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.

6. What's the
difference between NUMERIC and DECIMAL data types and when do I use them?

There is no difference between NUMERIC and DECIMAL data
types. They are synonymous to each other and either one can be
used. DECIMAL/NUMERIC data types are numeric data types
with fixed precision and scale.

DECIMAL (p [, s ])
NUMERIC (p [, s ])

In declaring a DECIMAL or NUMERIC data type, p, which is
the precision, specifies the maximum total number of decimal digits that can be
stored, both to the left and to the right of the decimal point. The
precision must be a value from 1 through the maximum precision of 38. The s
is the scale and it specifies the maximum number of decimal digits that can be
stored to the right of the decimal point. Scale, which defaults to 0 if
not specified, must be a value from 0 to the precision value.

The following table specifies the storage size required based on the precision
specified for the NUMERIC or DECIMAL data type:

Precision

Storage Size

1 - 9

5 bytes

10- 19

9 bytes

20-28

13 bytes

29-38

17 bytes

7. What's the
difference between FLOAT and REAL data types and when do I use them?

FLOAT and REAL data types are both approximate number data types
for use with floating point numeric data. Floating point data is
approximate; not all values in the data type range can be precisely
represented. The differences between these 2 data types are in the
minimum and maximum values each can hold as well as the storage size required,
as specified in the following table:

Data Type

n

Minimum Value

Maximum Value

Precision

Storage Size

float [(n)]

1-24

-1.79E + 308

1.79E + 308

7 digits

4 bytes

25-53

-1.79E + 308

1.79E + 308

15 digits

8 bytes

real

-3.40E + 38

3.40E + 38

7 digits

4 bytes

For FLOAT data type, the n is the number of bits used to
store the mantissa in scientific notation and thus dictates the precision and
storage size and it must be a value from 1 through 53. If not specified,
this defaults to 53. In SQL Server, the synonym for REAL data type
is FLOAT(24). If your data requires only a maximum of 7 digits
precision, you can either use the REAL data type or FLOAT data
type with 24 as the parameter (FLOAT(24)).

8. What's the
difference between SMALLDATETIME and DATETIME data types and when do I use
them?

A datetime data type is date and time data from January 1, 1753 through
December 31, 9999, to an accuracy of one three-hundredth of a second
(equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded
to increments of .000, .003, or .007 seconds.

On the other hand, a smalldatetime data type is a date and time data from
January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime
values with 29.998 seconds or lower are rounded down to the nearest minute;
values with 29.999 seconds or higher are rounded up to the nearest minute.

Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers. The first 4 bytes store the number of days
before or after the base date, January 1, 1900. The base date is
the system reference date. Values for datetime earlier than
January 1, 1753, are not permitted. The other 4 bytes store the time of day
represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less
precision than datetime. SQL Server stores smalldatetime values
as two 2-byte integers. The first 2 bytes store the number of days after
January 1, 1900. The other 2 bytes store the number of minutes since
midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy
to the minute.

Data Type

Minimum Value

Maximum Value

Time Accuracy

Storage Size

smalldatetime

January 1, 1900

June 6, 2079

up to a minute

4 bytes

datetime

January 1, 1753

December 31, 9999

one three-hundredth of a second

8 bytes

smalldatetime is usually used when you don't need to store the time of
the day such as in cases of effectivity dates and expiration dates. datetime
is used if the time of the day is needed and up to the second accuracy is
required.

9. What's the
difference between SMALLMONEY and MONEY data types and when do I use them?

MONEY and SMALLMONEY are both monetary data types for representing
monetary or currency values. The differences between these 2 data types
are in the minimum and maximum values each can hold as well as in the storage
size required by each data type, as shown in the following table:

Data Type

Minimum Value

Maximum Value

Storage Size

smallmoney

-214,748.3648

214,748.3647

4 bytes

money

-2^63 (-922,337,203,685,477.5808)

2^63 - 1 (+922,337,203,685,477.5807)

8 bytes

Both SMALLMONEY and MONEY data types has an accuracy to a
ten-thousandths of a monetary unit. The rule of thumb is to always use
the data type that will require the least storage size. If the monetary
value that you will store is less than 214,748.3647 then you should use SMALLMONEY;
otherwise use the MONEY data type.

10. How do I
store a boolean value in SQL Server?

In SQL Server, there's no boolean data type. The nearest data type that
can be used in place of boolean data is the BIT data type, which is an
integer data type that can accept a value of 1, 0 or NULL value only.