Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


varchar versus nvarchar


varchar versus nvarchar

Author
Message
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1755 Visits: 1949
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.
RBarryYoung
RBarryYoung
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 9517
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."
steveb.
steveb.
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3238 Visits: 7195
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57491 Visits: 44707
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, MVP, M.Sc (Comp Sci)
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


Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1755 Visits: 1949
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.
Grant Fritchey
Grant Fritchey
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20435 Visits: 32375
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
sqlvogel
sqlvogel
Mr or Mrs. 500
Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)

Group: General Forum Members
Points: 548 Visits: 3706
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.
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1755 Visits: 1949
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.
steveb.
steveb.
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3238 Visits: 7195
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.
Mauve
Mauve
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1344 Visits: 2052
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search