Performance issues with nvarchar

[sqlturbo]

I want to store Unicode data of varying length in a column. The length could vary from anywhere from 100 bytes to 2000 bytes maybe more. Would I suffer a performance hit if I set the column lenght to maximum, that is 4000? Or, would be better to limit it to little over 2000 bytes? What are the other issues invloved in such large column length data? Also Is Unicode data retrival faster then non-Unicode data in SQL Server 2000?

 

[SQLSister]

UNicode should only be used if you have a need for foreign characters in your field. Unicode takes twice the space to store as regular varchar.

One issue with the long length is that you can easily run up against the byte limit for a record depending on how many other fields you have in the table. It will let you create the structure but if a particular record will have too many bytes it will not allow it to be saved. If you havea lot of fields it might be better to use a text datatype or put you varchar or navarchar field in a related table with a one to one relationship.

I think varchar is supposed to be more efficent, but I couldn;t swear to it.

Questions about posting. See faq183-874

 

[vongrunt]

Also Is Unicode data retrival faster then non-Unicode data in SQL Server 2000?

Unicode may eventually eliminate collation conversions, for a price of double storage length (2 bytes per character). Slower.

 

[sqlturbo]

Good point SQLSister. I did not think of that.

vongrunt, On a NT machine a Unicode app runs faster then a character string app. Do you think one could extend the same logic to Unicode in SQL Server?

 

[vongrunt]

SQL2k is multi-user server software. Any bit of resources count in a long run. When cca 95% of even bytes are set to 00H, this is helluva lot of resources. Disk space, I/O, network bandwidth, used buffer cache, everything goes up by default except maybe CPU in some lab cases.

http://www.sql-server-performance.com/datatypes.asp