<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Database Design
»
Relational Theory
»
varchar versus nvarchar
17 posts, Page 1 of 2
1
2
»»
varchar versus nvarchar
Rate Topic
Display Mode
Topic Options
Author
Message
Carla Wilson-484785
Carla Wilson-484785
Posted Tuesday, December 02, 2008 9:21 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 9:22 AM
Points: 1,359,
Visits: 1,748
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
RBarryYoung
RBarryYoung
Posted Tuesday, December 02, 2008 9:38 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855,
Visits: 9,376
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
steveb.
steveb.
Posted Tuesday, December 02, 2008 9:54 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, June 05, 2013 8:56 AM
Points: 2,802,
Visits: 7,110
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
GilaMonster
GilaMonster
Posted Tuesday, December 02, 2008 9:58 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 38,074,
Visits: 30,368
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
Carla Wilson-484785
Carla Wilson-484785
Posted Tuesday, December 02, 2008 10:33 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 9:22 AM
Points: 1,359,
Visits: 1,748
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
Grant Fritchey
Grant Fritchey
Posted Tuesday, December 02, 2008 10:35 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436,
Visits: 25,281
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #612235
sqlvogel
sqlvogel
Posted Tuesday, December 02, 2008 2:51 PM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 12:11 AM
Points: 407,
Visits: 2,866
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
Carla Wilson-484785
Carla Wilson-484785
Posted Wednesday, December 03, 2008 7:38 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 9:22 AM
Points: 1,359,
Visits: 1,748
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
steveb.
steveb.
Posted Wednesday, December 03, 2008 7:41 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, June 05, 2013 8:56 AM
Points: 2,802,
Visits: 7,110
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
Mauve
Mauve
Posted Thursday, December 04, 2008 10:00 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 9:16 AM
Points: 1,150,
Visits: 1,859
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 »
17 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
