Fwd: 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.

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.