神刀安全网

SQL Server differences of char, nchar, varchar and nvarchar data types

Problem

The differences of SQL Server char, nchar, varchar and nvarchar are frequently discussed not just during interviews, but also by developers during discussions on database design. In this tip I would like to share not only the basic differences, but also what we need to know and be aware of when using each data type.

Solution

Char, nchar, varchar and nvarchar are all used to store text or string data in SQL Server databases.

  • char – is the SQL-92 synonym for character. Data is padded with blanks/spaces to fill the field size. Fixed length data type.
  • nchar – is the SQL-92 synonym for national char and national character. Fixed length data type.
  • varchar – is the SQL-92 synonym for character varying. Variable length data type.
  • nvarchar – is the SQL-92 synonym for national char varying and national character varying. Variable length data type.

You might wonder what the N stands for? N stands for National Language Character Set and is used to specify a Unicode string. When using Unicode data types, a column can store any character defined by the Unicode Standard, which includes all of the characters defined in the various character sets. Note that Unicode data types take twice as much storage space as non-Unicode data types.

Unicode is typically used in database applications which are designed to facilitate code pages which extend beyond the English and Western Europe code pages. It is designed so that extended character sets can still "fit" into database columns. What this means is that Unicode character data types are limited to half the space, because each byte actually takes two bytes to store the data (Unicode is sometimes referred to as "double-wide"). SQL Server has supported Unicode since SQL Server 7.0 by providing nchar/nvarchar/ntext data types. SQL Server doesn’t support UTF-8 encoding for Unicode data, but it does support UTF-16 encoding.

I made a table below that will serve as a quick reference.

Table of Differences

char

nchar

varchar

nvarchar

Character Data Type Non-Unicode fixed-length Unicode fixed-length can store both non-Unicode and Unicode characters (i.e. Japanese, Korean etc.) Non-Unicode variable length Unicode variable length can store both non-Unicode and Unicode characters (i.e. Japanese, Korean etc.)
Maximum Length up to 8,000 characters up to 4,000 characters up to 8,000 characters up to 4,000 characters
Character Size takes up 1 byte per character takes up 2 bytes per Unicode/Non-Unicode character takes up 1 byte per character takes up 2 bytes per Unicode/Non-Unicode character
Storage Size n bytes 2 times n bytes Actual Length (in bytes) 2 times Actual Length (in bytes)
Usage use when data length is constant or fixed length columns use only if you need Unicode support such as the Japanese Kanji or Korean Hangul characters due to storage overhead used when data length is variable or variable length columns and if actual data is always way less than capacity use only if you need Unicode support such as the Japanese Kanji or Korean Hangul characters due to storage overhead
query that uses a varchar parameter does an index seek due to column collation sets query that uses a nvarchar parameter does an index scan due to column collation sets

Advantages and Disadvantages

Data Types

Advantages

Disadvantages

char

Query performance is better since no need to move the column while updating.

No need to store the length of string in last two bytes.

If not properly used, it can take more space than varchar since it is fixed length and we don’t know the length of string to be stored.

It is not good for compression since it embeds space characters at the end.

varchar

Since it is variable length it takes less memory spaces.

Decreases the performance of some SQL queries.

nchar/nvarchar

Supports many client computers that are running different locales.

If not properly used it may use up a lot of extra storage space.

With the growth and innovation of web applications, it is even more important to support client computers that are running different locales. The easiest way to manage character data in international databases is to always use the Unicode nchar, nvarchar, and ntext data types, instead of their non-Unicode equivalents, char, varchar, and text.

Unicode is a standard for mapping code points to characters. Because it is designed to cover all the characters of all the languages of the world, there is no need for different code pages to handle different sets of characters. SQL Server supports the Unicode Standard, Version 3.2. If all the applications that work with international databases also use Unicode variables instead of non-Unicode variables, character translations do not have to be performed anywhere in the system. Clients will see the same characters in the data as all other clients.

SQL Server stores all textual system catalog data in columns having Unicode data types. The names of database objects, such as tables, views, and stored procedures, are stored in Unicode columns. This enables applications to be developed by using only Unicode, and helps avoid issues with code page conversions.

Remember when developing new applications to consider if it will be used globally because this will help you determine whether to use nchar and nvarchar to support different languages.

Next Steps

Learn more by reading and exploring the following:

Last Update:

SQL Server differences of char, nchar, varchar and nvarchar data types

About the author

SQL Server differences of char, nchar, varchar and nvarchar data types Sherlee Dizon is an IT consultant and web application developer with over 14 years of experience as a software and web developer.

View all my tips

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » SQL Server differences of char, nchar, varchar and nvarchar data types

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址