nvarchar / char / text
-
I recently switched from Access to SQL.
Can anyone confirm that nvarchar is about the same as a text or memo field in Access? When would you use varchar and when nvarchar in SQL?
Is it right that the difference between char and varchar is that (n)char will add spaces (value1[empty space],value2 etc) to the data in the field and (n)varchar not? Why would anyone use (n)char? Those empty spaces mess everything up here.
Thanks in advance!!
-
I have usually steered clear of Access, but I believe that a text/memo field in Access can grow quite large - hence it is more like a text field in SQL.
Here is a quick summary.
CHAR(x) - fixed length string in SQL. Good for times where most of your strings are around the same length - eg postcodes. If you have things like book titles, etc, then you are better off with a VARCHAR. SQL stores X characters in the database and will return X characters always
VARCHAR(x) - variable length string with an upper limit. SQL doesn't store all X characters, it instead stores the length. This can make space more efficient when you have strings that range between say 0 and 3000 characters! However, it is "slightly" (ie, not noticeable i've been told) less efficient. I read in a book somewhere that the break-even point is about 8 characters.
the NCHAR and NVARCHAR varieties store unicode characters.
The maximum length of a character column is 8000 bytes - hence for the Nxxx varieties, this is 4000 2-byte characters.
I think I have used varchar for everything in my DBs - mainly because they are convenient and I don't have to remember about the spaces!
If you need > 8000 characters, then a TEXT or NTEXT column is your best bet. You should read up in Books Online about these as you often have to access them slightly differently.
Finally, SQL has a limit on the number of bytes available per row - around 8160 bytes or something. This means that if you try to create a table with 3 x Char(4000) columns, you will get an error. If you create the same table with 3 x Varchar(4000) columns you will get a warning - so long as the total length of the three cols remains beneath the upper limit of the row size, you will be fine, but you are warned that you could have failed inserts if you insert 3 x 3000 character strings for example.
Hope this helps
-
Ian has explained almost everything there is to explain... just a few details: The "n" varieties are useful only if you plan to use some special chinese or japanese character sets and such - no use for european languages, even if your language has lots of special characters. CHAR can be useful e.g. if you have a column that will always have 2 characters (mostly various "type" columns, like OrderType, CompanyType... where a two-character code is stored), but even here you can use VARCHAR if you wish. For columns that store data of variable length you should always use VARCHAR.
-
Only use "n" for double byte languages - most of these are asean based charcters (Japanese, Chinese, Korean etc) and only use then in places that you need to store a double byte character.
-
Thank you guys! 🙂
This forum is great!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply