Table of Differences
|Character Data Type|| |
|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
|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.|