SQL Server 2012 :: Explicit Casting Varchar And Nvarchar Columns?

Feb 26, 2014

I know that if I have an nvarchar column I can use an equality like = N'supersqlstring' so it doesn't implicit cast as a varchar, like if I were to do ='supersqlstring'. And then I'll be a big SQL hero and all my stored procedures will run before a millisecond can whisper.

But if I'm comparing an nvarchar column to a varchar column, is it better to cast the varchar 'up' to an nvarchar or cast the nvarchar 'down' to a varchar?

For instance:

cast(a.varchar as nvarchar(100)) = an.nvarchar

or

cast(an.nvarchar as varchar(100)) = a.varchar

Leaving aside non-matching, like (at least I don't think) that SQL considers the varchar n to be equal to the nvarchar ń, what's the best way to handle this?

Pretend for a moment that each column contains a mixed letter and number ID with no accented or wiggly-squiggly Unicode characters; it's just designs clashing.

Is there a performance hitch doing it one way or another? Should I use COLLATE? Should one of the columns be altered?

View 8 Replies


ADVERTISEMENT

SQL Server 2012 :: Case Statement On Nvarchar With Literal String Comparison To Varchar?

Apr 14, 2015

how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.

Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise

in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.

View 3 Replies View Related

Method For Compressing Varchar/nvarchar Columns?

Jul 20, 2005

I have an application with highly compressable strings (gzip encodingusually does somewhere between 20-50X reduction.) My base 350MBdatabase is mostly made up of these slowly (or even static) strings. Iwould like to compress these so that my disk I/O and memory footprintis greatly reduced.Some databases have the ability to provide a compressedtable, compressed column, or provide a user defined function tocompress an indvidual Field with a user defined function[ala. COMPRESS() and DECOMPRESS() ].I could right a UDF with an extended prodcedure if I need to but I'mwondering if there are any other known methods to do this in MS SQLServer 2000 today?--Frederick Staatsfrederick dot w dot staats at intel dot com (I hate junk mail :-)

View 6 Replies View Related

SQL 2012 :: Explicit Columns Or NULL Foreign Keys?

Mar 19, 2014

I have a table called Appointment, for storing (you guessed it) appointments at a medical practice. If an appointment is cancelled, I want to collect a cancellation reason, so let's say that I create a second table called Cancellation which has a foreign key relating to the Appointment table's primary key, AppointmentID, and another column, Reason. Now, in order to indicate that an appointment was cancelled, I could include a Cancelled column in the Appointment table with a bit datatype, or instead I could infer that an appointment must be cancelled if it has a corresponding record in the Cancellation table.

It may be that it'd be better to store the cancellation reason in the Appointment table - But regardless, let's say I stick with the two-table solution described above, and I subsequently want to write a query to list all appointments which have been cancelled. If I had the Cancelled column in the Appointment table, I could simply query for all records in that table where that column's value was FALSE. If I went the other way and DIDN'T have a Cancelled column, I could instead write a query joining the Appointment and Cancellation tables to return all records in Appointment with a corresponding record in Cancellation.

That latter method, whilst slightly more complicated because it involves joining two tables, seems to me to be the most normalised. Instead of storing the fact that an appointment is cancelled in two different tables, that fact is only stored in the Cancellation table. Would there be a performance hit in using the two-table, 'inferred cancellation' method rather than just having a bit column in the Appointment table? Would that performance hit be enough to persuade you to use a Cancellation column in the Appointment table instead? And what about if I were to apply that method to other things associated with each appointment, e.g. Is it completed? Is it chargeable to the client or an insurance company? Is the client and in-patient or out-patient?

View 6 Replies View Related

How To Reclaim Space In Columns Changed From Nvarchar To Varchar

Jul 23, 2005

Hi,This is probably an easy question for someone so any help would beappreciated.I have changed the columns in a table that where nvarchar to the samesize of type varchar so halve the space needed for them.I have done this a) becuase this is never going to be an internationalapplication, b) we are running out of space and c) there are 100million rows.I have done this with the alter table statement which seems to work butthe space used in the database hasn't altered.I'm presuming that the way the records are structured within the tablethere is just now more space free inbetween each page???Is there a way or re-shrinking just an individual table and free upsome of the space in there or am i missing the point somewhere?Thanks in advance,Ian

View 4 Replies View Related

Type Casting Varchar As Time

Apr 16, 2007

Hi.



Current development setup: Visual Studio 2005 (C# language), SQL Server 2005 Express Edition.



I am at this point changing some of my application. It stores a lot of information for times of trains. I decode a file which is text based and my times are stored in varchar(4) columns with the format "HHmm".



I am now at a stage where I need to add values to these times and perform a search based on two time values (a minimum time and maximum time).



I really need to be able to add another varchar(4) value to the times stored in my database (also varchar(4) - may look at this again in future). I have absolutely no interest in storing seconds, modifying the database to include the time colon, nor am I interested in the date portion of a time string, only the 4 digits representing the hours and minutes.



Can this be done easily in SQL server? For example:



varchar(4) of 0445 + varchar(4) of 0027 should result in the time 0512.



Similarly a varchar(4) of 2358 + varchar(4) of 0015 should result in the time 0013.



This has to be very quick to execute.



Thanks for any help you can offer!



Sean



View 1 Replies View Related

SQL Server 2012 :: Premature Casting On 0 Rows Insert

Feb 28, 2015

I have encountered some weird behaviour. Code that has been working for "eternities" suddenly started to fail. I couldn't recreate it on any other machines.

I have written a sample script to illustrate the issue:

CREATE TABLE #t_parcels (parcel_id INT, current_pos NUMERIC(28, 0), end_pos NUMERIC(28, 0))
CREATE TABLE #t_orders (outorder INT, parcel_id INT)
CREATE TABLE #t_missing_parcels (parcel_id INT, diff INT)

INSERT INTO #t_parcels (parcel_id, current_pos, end_pos)
SELECT1, 100000000000.0, 900000000000.0

[Code] ..

The last insert crashed with :"Arithmetic overflow error converting expression to data type int", even though there are no rows that satisfies the condition!

This is due to "diff" column having wrong datatype, BUT, the insert had no hits in the database. So how can inserting 0 rows crash with incorrect datatype?

I even copied the select so it was ran before the insert, in in that case, the SELECT completed successfully.

When i changed datatype in the table, the error went away, but I'm still curious what led to the error.

View 9 Replies View Related

Changing Date Format And Casting As Varchar?

Jul 21, 2014

My current Query takes the DATE value stored in P.CreatedDate and makes it VARCHAR, then stores it in the table. I need to format this to return YYYYMMDD. How would I go about this?

Current Code:

Code:
CAST(P.CreatedDate AS VARCHAR) AS DateEntered,

View 3 Replies View Related

SQL 2012 :: Query Returns 13864 On A Varchar Columns

Mar 24, 2015

We have a customer that is running SQL2012 and we are seeing a weird result on a query when we run it on their db. It is based off of a table that has about 30 columns but in this case we only care about 2 of them.

[Number] [varchar](15) NOT NULL
[Person_ID] [varchar](12) NULL

Here is the query we are doing:
Select Number,Person_ID From TableName where LP='ABC123'

The result I get back is the following:
Number:1
Person_ID:13864

The Person_ID should be a result of another table that created that Person_ID but it doesn't exist in that table. So we do not know where that 13864 is coming from. When we open that record through our application it shows Nothing for the Person_ID in that field.

When we do this query on our copy we get back
Number:1
Person_ID:

Which is exactly what we should see as the result.

Could there be a sql server setting that is set on their server that could possibly be given us back 13864 for a NULL value?

View 2 Replies View Related

SQL Server 2012 :: How To Stop Select For XML Explicit From Escaping Inner

Jun 3, 2014

I'm working on a script to produce XML. There are two parts to the XML: an envelope (called "message") and a payload ("payload). Here's an example of what the result should look like:

<?xml version="1.0" encoding="utf-8" ?>
<message guid="cb9f7927-a4c7-44f8-9e55-bd5dd3e85894">
<client name="BNS CASL Client" guid="3dc500f3-dffb-455f-a071-12c4fa37a1eb" endPoint="http://localhost:51873/CASLWS.aspx" />
<payload>
<![CDATA[

[code]....

(Note that I had to add a space between the '&' and 'lt' and 'gt' to get it to post in the forum without the forum converting them back to < and >!)You can see that SQL escaped the < and >. Also the CDATA escape sequence is missing.I want to see < instead of & lt and > instead of & gt. Also, I need the CDATA escape sequence.

View 5 Replies View Related

Problem With Varchar And Nvarchar Datatype In Linked Server

Mar 14, 2006

Hi,

I am updating a remote table using linked server in sql server 2005.

but in case of varchar and nvarchar i am getting an error :
"OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor."



thanks in advance.

Thanks & Regards

Pintu





View 2 Replies View Related

Transact SQL :: Unexpected Rounding With MONEY When Casting To VARCHAR Or Printing

Dec 18, 2008

if I 'print' a MONEY value, or cast a MONEY variable to VARCHAR, it automatically rounds it to two decimal places. Maybe that's a built-in convenience, but I'd like to make it not do that.My workaround right now is to first cast my MONEY variabled to DECIMAL(30,4), and then cast the result to VARCHAR, but I'd like to avoid that step if possible.Consider the following

query:DECLARE @UnitCost MONEY  SET @UnitCost = .0167  SELECT      @UnitCost,      
CAST(@UnitCost AS VARCHAR(30)),     
CAST(CAST(@UnitCost AS DECIMAL(30, 4)) AS VARCHAR(30))  -
- Results in: 0.0167, 0.02, 0.0167 

View 5 Replies View Related

SQL Server 2008 :: Identifying ASCII Characters In NVARCHAR Columns

May 25, 2010

I have an issue where I am storing various international characters in nvarchar columns, but need to branch the data at one point of processing so that ASCII characters are run through an additional cleansing process and all non-ASCII characters are set aside.

Is there a way to identify which nvarchar values are within the ASCII range and can be converted to varchar without corruption? Also, the strings may contain a mix of english and international character sets, so the entire string must be checked and not just the first character.

i.e.
Pass:
'Hello', 'abc123'

Fail:
'太平市', 'abc太123'

View 5 Replies View Related

Nvarchar To Varchar

Aug 28, 2007

i have used nvarchar as my datatype in sql server 2000 now
i have decided to change to varchar as i can increase the character length from 4000 to 8000
Do I Lose data if i change the datatype.

View 7 Replies View Related

Nvarchar To Varchar

Jul 10, 2003

I have a table using nvarchar(for what ever reason which beyond me why its a nvarchar...) that I would like to change to a varchar. There is no unicode in the fields so I don't have to worry about but I don't want to lose any text data. Will coverting the data type lose data?

Thanks

View 9 Replies View Related

VARCHAR Vs NVARCHAR

May 16, 2006

So I have an existing table that looks like:


ID BIGINT
VAL VARCHAR(128)


I am converting this table to something that will be multi language compliant. My question is, I know that NVARCHAR's take double the space of a VARCHAR. Do I actually need to double the length of the VAL field to store the same amount of data or does the DB handle that?

Basically I want to store a 128 character NVARCHAR.. do I need to set my table up like this:


ID BIGINT
VAL NVARCHAR(256)

or


ID BIGINT
VAL NVARCHAR(128)

View 3 Replies View Related

Nvarchar && Varchar

Feb 26, 2007

Hi,I am new to MS SQL. When I create a column in a table, when shall Iuse nvarchar or varchar? Please help.Thanks,Mike

View 5 Replies View Related

Varchar &&amp; Nvarchar

Dec 18, 2006

from the definition, i know that "n" means uni-code. but what is the exact advantage of having nchar or nvarchar over char or varchar?

View 1 Replies View Related

Nvarchar And Varchar Sizes....

Jun 19, 2004

Hello again everyone....

I have another question for everyone....

I am currently cleaning up my database to get its total size down and am not sure how nvarchar and varchar work exactly.

When defining the length of a varchar or nvarchar in enterprise manager, will that effect the size of the entry (as far as data size) no matter what the length of the entry? In other words, will there be a difference in Data Size for an entry with the length of 4 characters with a definition of varchar(4) versus an entry with the length of 4 characters with a definition of varchar(50).

****If there is no difference, is there any reason in trying to best guess the size to give nvarchar or varchar columns? It would seem easier to just define the lengths of columns which need variable lengths to 200 or 400 just to save time in not trying to best guess what the size might be...*****

Thanks ahead for any help...

-Alec

View 3 Replies View Related

Conversion From Varchar To Nvarchar

Aug 4, 2006

Hi,

Can someone please explain to me how the datapages in Microsoft SQL Server 2000 works. The pages are supposed to be 8K, that is 8192 bytes of which only 8060 are accessible for data storage (due to overhead).
Now, I currently have a table containing 8 fields. Two of these fields are varchar and should be converted to nvarchar. One of the varchar fields is limited to 255 characters and the other to 4000 characters. When I convert the 255 characters field to nvarchar it works just fine, but when I want to convert the 4000 characters field I get an error from MS SQL saying that it gets to big. Is the error only for the 4000 characters field (which growths to 8000 bytes when using nvarchar instead of varchar) or must the whole table fit into one datapage?
Could a blob maybe solve my problem, or will I face new problems when storing unicode characters in a blob?

Thanks in advance

View 3 Replies View Related

To Varchar Or NVarchar, That Is The Question

Jan 14, 2006

I have a table with a Varchar field that will contain encrypted data. Since each byte can have a value from 0 through 255, can I use Varchar or should I change the field to NVarchar? The reason I ask is that during testing, the Varchar field sometimes is truncated, supposed to be 16 bytes but ends up as 5 or 6 or something less than 16.

View 2 Replies View Related

Varchar/nvarchar Length

Mar 9, 2007

Hi,I have a pretty straightforward question to do with variable length fields I hope someone can help me with:When using varchar (or nvarchar), is there any point in specifying a smaller length than the maximum? Does it save space or improve performance at all?ThanksRedit: I suppose the max rowsize is an issue. any others?

View 2 Replies View Related

Difference Between Varchar And Nvarchar

Feb 17, 2006

What is the difference between the nvarchar and varchar datatypes? Which should be used?

View 2 Replies View Related

Differents Between Varchar An Nvarchar

Apr 10, 2006

What is the diferent between varchar an nvarchar?

View 4 Replies View Related

Diff Between Varchar And Nvarchar

May 22, 2006

hi

could any one help me in differentiating between varchar and nvarchar

Thanks in advance

View 4 Replies View Related

Nvarchar Versus Varchar

Oct 10, 2007

I have table with a field defined as nvarchar. I want to change it to varchar. I have a stored procedure which defines the parameter @strCall_desc as nvarchar(4000). Are there going to be ay problems with running this sp if I just change the field type as described.

TIA

View 6 Replies View Related

Varchar And Nvarchar Issue

Jul 20, 2005

HiThe maximum length of a nvarchar could be 4000 characters while that ofvarchar could be 8000.We are trying to use unicode which would require that the datatype forone our fields be converted from varchar to nvarchar. But looks likethis would result in loss of existing data.Is there a way to do this without loss of data?Many thanks.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Nvarchar Or Varchar In Function?

Mar 14, 2006

I am using SQL Express

I have a very simple function to retreive the maximum value (MemberID) from a member table.  The memberID column is in "nvarchar(8)" type.

The following shows the function:

ALTER FUNCTION dbo.GetLastMemberID()
RETURNS nvarchar(8)
AS
BEGIN
 RETURN (SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo)
END

When i run the function, only the first 4 character (say 'IM00') is returned.

If I change the "RETURNS nvarchar(8)" to either "RETURNS nvarchar(16)" or "RETURNS varchar(8)", whole column (8 characters) is returned.

My question: should I use nvarchar(16) or varchar(8) in the function in this case?

Thanks

 


 

View 7 Replies View Related

SSIS: Varchar(max) Or Nvarchar(max) Help

Apr 11, 2006

Please I know this is fustrating but I really need help with this issue:



I am getting data conversion error when I tried to load data from one SQl table to another SQL table using SSIS.

The source table has a column with data type nvarchar(max). Also the destination table has the same data type nvarchar(max) but I keep getting conversion error when I use SCD transformation.

Error: " Input column "des" (116) has a long object data type of DT_TEXT, DT_NTEXT or DT_IMAGE which is not supported"

I am fine when I use OLEDB destination but I want to do an incremental load.

Is there a quick fix for this?



Thanks

Omon

View 5 Replies View Related

Difference Between Nvarchar And Varchar

Jun 26, 2007

what is the difference between nvarchar and varchar

View 3 Replies View Related

SQL Server 2012 :: Converting Nvarchar To Datetime

Jul 24, 2015

I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this

"04/24/2013'
"05/01/2014"...etc...

My requirement is to convert this column into Datetime ?

I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..

View 9 Replies View Related

Simple Question Regarding Nvarchar And Varchar

Jun 20, 2007

I have looked at several explinations and I understand the difference between unicode and non-unicode.
I get that the basic idea around storage is "double", 2 bytes instead of 1.
My question is, does the 2 byte instead of 1 byte rule apply even if I am storing a char that doesn't need the full to bytes.
for arguments sake I have a table called "UnicodeTable" and one column called "Letter".
If I store the letter "A" on the first row of the "UnicodeTable" does the size of my database increase by 2 bytes?
 

View 1 Replies View Related

Internationalizing: Varchar To Nvarchar (automated?)

May 4, 2004

I have an existing application that relies on a SQL Server database.

I want to switch all varchar fields to nvarchar so it can handle multiple languages.

The database has ~25 tables, many of which have varchar fields. I want to convert them all to nvarchar.

The database has ~150 stored procedures, many of which have varchar fields. I want to convert them all to nvarchar.

Are there any tools out there that would let me convert the tables of my choosing, and the stored procedures of my choosing, so that any 'varchar' mentions are changed to 'nvarchar' ? I've only used SQL Query Analyzer to write queries and use MS Access (and some SQL Enterprise Manager) to make the tables and relationships.

Thanks,
-Bret

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved