Best practise of storing a column which is of string type

Hi,

Hi I am currently using SQL Server 2005.

Whats the difference between nvarchar & varchar datatypes in SQL server and which is opted best to store strings.

Thanks,

Uma Ramiya

nvarchar is used commonly used to store national characters / unicode characters. so if your front end application accepts unicode characters to be stored to your db then you should use nvarchar. if not use varchar instead
|||

I use ASp.Net 2.0 as front end. In that case what should I use

Thanks,

Uma Ramiya

|||its not dependent on asp.net. it depends on the localization of your application e.g. does your asp.net application can be viewed, transalated and/or accepts inputs with chinese , korean characters, etc. then you should use nvarchar. if you application only stores english characters then use varchar
|||

If you want to store english (latin based) string then you can use varchar.

If you want to store non-english string (ex. Japanese, Hindi, Tamil etc) then you have to use NVarchar.

Nvarchar uses Unicode (which supports all the alphabets/numbers)

Varchar Uses ASCII CODE (which supports only english alphabets/numbers)

Nvarchar each char occupies 2 Byte

Varchar each char occupies 1 Byte

Nvarchar max length in SQL Server 2000 = 4000

Varchar max length in SQL Server 2005 = 8000

If your app supports (or planned to support on future) Globilization/Localization then use the NVarchar

Otherwise Varchar is more enough..

Benefits/drawbacks with NVarChar(max)

Hi,

I wonder if there are any drawbacks with NVarChar(max) contra e.g. NVarChar(40) when it comes to performance and amount of storage used?

Are there other arguments to use e.g. NVarChar(40) than that you have more control of how long the strings are when you set the upper limit?

I'm using Sql Server 2005.

Tomsi

Hey Tomsi. Using any of the (max) datatypes will basically tell the server that the data in that column could possibly grow to 2gb if desired. To achieve this, the database engine will evaluate the size of the data being inserted/updated into the column and store it appropriately depending on the size. If the size of the data being stored will fit in-row with the rest of the data for that row (i.e. if it's less than 8k minus the size of the other columns in the row give or take some other considerations), then SQL Server will store the data in-row with the rest of the row data. If it is larger than that and can't be stored in-row with the rest of the row's data, then SQL Server will store a pointer in the row with the data that points to the location of the actual data elsewhere. During read/write operations for that row from now on, the engine will have to jump from the pointer to the data, get/update the data, then jump back to the pointer, so read/write time is slowed in this case.

You'll also see a slight additional overhead for determining if the data can fit in row or not, but that will be miniscule.

General recommendation would be that if you know the size of the data will never exceed 'x', and x is <= ~8000 bytes, then use nvarchar(x)...if the data will in some cases or always exceed that size, then use the (max) indicator...

You could see this article for a bit more info and go from there

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

HTH