XML FeedsI was browsing around last night on the internet and a person wanted to know what data type to use to store a course name. Here is an answer he got:
I would recommend nvarchar or varchar for the name. Use nvarchar if you anticipate there ever being a point in the future that you will need to support foreign languages that might contain unicode characters that are not supported by varchar. I would look at the longest name I anticipate needing, double it's length and then round up to the nearest 50. So if "Introduction to Partial Differential Equations" at 48 then I would make it varchar(100)
Okay so that part in bold is completely wrong, Nvarchar uses 2 bytes per character for storage but you do not specify that. So if you want to store the character 文 you need nchar(1) not nchar(2).
Run this and you will see the character 文 in the output
declare @n nchar(1) set @n = N'文' select @n
And no, there is no mistake, the reason I didn't use the codeblock that I use for the rest of this post is that the 文 character gets changed to 文 so the SQL would be incorrect
Also be aware that you need to have the N in front of the string, this tells SQL Server that it has to treat it as unicode. If you do this '文' instead of N'文' you will get a question mark in the output.
What if you wanted to store over 4000 characters in a nvarchar? Varchar goes up to 8000 characters but nvarchar only goes up to 4000 characters, take a look and run the statement below.
- DECLARE @n NVARCHAR(4001)
Below is the error that you will get.
Msg 2717, Level 16, State 2, Line 1
The size (4001) given to the parameter '@n' exceeds the maximum allowed (4000).
But there is hope, you can use NVARCHAR(max) to store up to 2GB of data, so about a billion characters or so
- DECLARE @n NVARCHAR(max)
One more thing to be aware of when using unicode is that LEN will give you the number of characters but DATALENGTH will give you the storage required to store the character(s)
Run this
- SELECT LEN(N'1'),DATALENGTH(N'1')
LEN returns 1 and DATALENGTH returns 2. To learn more about LEN and DATALENGTH, take a look at the The differences between LEN and DATALENGTH in SQL Server post I wrote a while back.
That is all for this short weekend post.
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Unicode goes up to U+10FFFF. Special consideration needs to be taken when characters above U+FFFF are used. In these cases, LEN and DATALENGTH will return 2 and 4 respectively, for a single character. 
Nice article. Well explained.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.