Frequently Asked Questions - SQL Server Data Types

1. What's the difference between CHAR and VARCHAR data types and when do I use them?

CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).

You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.
2. What's the difference between NCHAR and NVARCHAR data types and when do I use them?

NCHAR and NVARCHAR data types are both Unicode character data types with a maximum length of 4,000 characters. The main difference between these 2 data types is that an NCHAR data type is fixed-length while an NVARCHAR is variable-length. If the number of characters entered in an NCHAR data type column is less than the specified column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for NCHAR is two times n bytes while for NVARCHAR is two times the number of characters entered (in bytes).

You should use NCHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use NVARCHAR when the data values in a column are expected to vary considerably in size.
3. What's the difference between CHAR and NCHAR data types and when do I use them?

CHAR and NCHAR data types are both character data types that are fixed-length. Below is the summary of the differences between these 2 data types:
CHAR(n) NCHAR(n)
Character Data Type Non-Unicode Data Unicode Data
Maximum Length 8,000 4,000
Character Size 1 byte 2 bytes
Storage Size n bytes 2 times n bytes

You would use NCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.
4. What's the difference between VARCHAR and NVARCHAR data types and when do I use them?

VARCHAR and NVARCHAR data types are both character data types that are variable-length. Below is the summary of the differences between these 2 data types:
VARCHAR(n) NVARCHAR(n)
Character Data Type Non-Unicode Data Unicode Data
Maximum Length 8,000 4,000
Character Size 1 byte 2 bytes
Storage Size Actual Length (in bytes) 2 times Actual Length (in bytes)

You would use NVARCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.
5. What's the difference between TINYINT, SMALLINT, INT and BIGINT data types and when do I use them?

TINYINT, SMALLINT, INT and BIGINT are all the same in the sense that they are all exact number data types that use integer data. The difference between these data types are in the minimum and maximum values that each can contain as well as the storage size required by each data type, as shown in the following table:
Data Type Minimum Value Maximum Value Storage Size
tinyint 0 255 1 byte
smallint -2^15 (-32,768) 2^15 - 1 (32,767) 2 bytes
int -2^31 (-2,147,483,648) 2^31 - 1 (2,147,483,647) 4 bytes
bigint -2^63 (-9,223,372,036,854,775,808) 2^63 - 1 (9,223,372,036,854,775,807) 8 bytes

Choosing which of these data types to use depends on the value you want to store for the column or variable. The rule of thumb is to always use the data type that will require the least storage size. Don't always use INT as your data type for whole numbers if you don't need to. If you simply need to store a value between 0 and 255 then you should define your column as TINYINT.