Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

varchar versus nvarchar Expand / Collapse
Author
Message
Posted Tuesday, December 2, 2008 9:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 1,577, Visits: 1,857
I just discovered a problem in the database design for an application that was developed years ago. There is an audit table with OldValue and NewValue columns defined as nvarchar(255). However, in the mean time, new tables have been defined with columns longer than nvarchar(255).
I want to propose that the audit table use a much larger value.
However, nvarchar takes up twice as much space as varchar, AND not too long ago, there was a forum post about trailing blanks in nvarchar, and how that can be a problem in comparisons, etc. unless you use RTRIM().

So I was wondering what the general consensus is on using varchar versus nvarchar, especially for longer strings. Is it considered best practice to always use nvarchar, or are there some other considerations?

Thanks.
Post #612145
Posted Tuesday, December 2, 2008 9:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
It all depends on whether you think that there is any chance that that column will need to store Unicode characters that are not covered by Varchar().

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #612163
Posted Tuesday, December 2, 2008 9:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
I tend to default to using NVARCHAR, unless i can absolutely be certain that there will not be any UniCode data in there. I find the increase in space is easier to deal with than potentialy needing to chnage the data types in the future.

Post #612189
Posted Tuesday, December 2, 2008 9:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 40,180, Visits: 36,583
Carla Wilson (12/2/2008)

So I was wondering what the general consensus is on using varchar versus nvarchar, especially for longer strings. Is it considered best practice to always use nvarchar, or are there some other considerations?


Best practice is to always use the appropriate data type. so if you're going to be storing unicode, make the column nvarchar. If you're not storing unicode (just ascii strings) then make the column varchar.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #612194
Posted Tuesday, December 2, 2008 10:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 1,577, Visits: 1,857
Practically speaking, I expect only "American-keyboard" ascii characters, but I suspect the original designer wanted to cover international users.
But I can think of a number of other columns in this database that would never need unicode.
Post #612232
Posted Tuesday, December 2, 2008 10:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 13,875, Visits: 28,272
Whether or not to use varchar or nvarchar really depends on the type of data you're storing. If you are not now or ever going to store different character sets for foreign language use, you can use varchar. However, if you need to store unicode, you need to use nvarchar.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #612235
Posted Tuesday, December 2, 2008 2:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:44 PM
Points: 449, Visits: 3,389
On the specific question of audit tables. If your requirement is to capture actual data then you should create audit table columns that match the types of the data being audited. If you have more than one type then use multiple columns. Casting every column to a single string type (whether VARCHAR or NVARCHAR) is poor practice in my opinion.

In SQL Server 2008 use Change Data Capture rather than roll-your-own audit.


David
Post #612417
Posted Wednesday, December 3, 2008 7:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 1,577, Visits: 1,857
RE: audit table - it's not like a standard audit table. I have several of those, and the columns do match the datatypes of the tables that they audit. I was going to put the word in double-quotes when I first posted; this table is used in an application to keep track of change requests from users, that may or may not be approved. The application has been around a LONG time, and I'm not even going to propose messing with it. However I do need to make sure it keeps working.

Thanks to everyone for your input. It was interesting to see that the use of nvarchar is not as common as I was expecting to see.
Post #612841
Posted Wednesday, December 3, 2008 7:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
Carla Wilson (12/3/2008)
RE: audit table - it's not like a standard audit table. I have several of those, and the columns do match the datatypes of the tables that they audit. I was going to put the word in double-quotes when I first posted; this table is used in an application to keep track of change requests from users, that may or may not be approved. The application has been around a LONG time, and I'm not even going to propose messing with it. However I do need to make sure it keeps working.

Thanks to everyone for your input. It was interesting to see that the use of nvarchar is not as common as I was expecting to see.


It is probably not used as much in America, but here in Europe with all the different languages etc.. , it is pretty much needed for any column that will hold user input string data.
Post #612848
Posted Thursday, December 4, 2008 10:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 1,186, Visits: 1,977
Note that even in America, extended ASCII is very common. Just think of all of peoples names that have accents in them. Or the word resumé. Let alone, there is this province in Canada that has French as the standard language.

So if you are designing a new system, use NCHAR and NVARCHAR. Trust me and you'll thank me later.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #613882
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse