My blog share the my c# knowledge to others and get response from others.

Monday, April 11, 2011

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?