DATA TYPES

The choice of data type is also the most fundamental performance decision you will ever make for a database. You need to select a data type that can store the data required by the business, but your data type should not consume a single byte of storage more than necessary.

 Numeric Data Types

Nine numeric data types ship with SQL Server 2008, and they are used to store integer,monetary, and decimal-based numbers.

 

Decimal Data Types

 Decimal data types have two parameter.

Precision 

The precision indicates the total number of digits that can be stored both to the left and to the right of the decimal.

Scale. 

The scale indicates the maximum number of digits to the right of the decimal point.

     For example, assigning a column the DECIMAL(8,3) data type allows SQL Server to store a total of eight digits in the column, with three of the digits to the right of the decimal point or values

 between -99999.999 and 99999.999.

 Decimal and Numeric Data Type Storage

 PRECISION                                      STORAGE SPACE

  1 to 9                                                    5 bytes

 10 to 19                                               9 bytes

 20 to 28                                               13 bytes

 29 to 38                                              17 bytes

  Character Data Types

SQL Server 2008 has four data types for storing character data, with the choice of which one to use depending upon whether you have fixed- or variable-length values and whether you

want to store Unicode or non-Unicode data....

DATA TYPE                     STORAGE SPACE

 CHAR(n)                          Non-Unicode, 1 byte per character
                                         defined by n, up to a maximum of

8,000 bytes.

 VARCHAR(n)                    Non-Unicode, 1 byte per character                                                      stored up to a maximum of

8,000 bytes

 NCHAR(n)                       Unicode, 2 bytes per character defi ned                                              by n, up to a maximum of

                                           4,000 bytes

 NVARCHAR(n)                Unicode, 2 bytes per character stored                                                 up to a maximum of 4,000 bytes

 Date and Time Data

 One of the biggest recent advances in SQL Server greatly expands the data types to store dates and times.

Small date time and datetime  data types store a date and a time together as a single value and have existed for several versions of SQL Server. The range of values stored for a DATETIME data type was rather limited for historical applications, so SQL Server 2008 introduced a DATETIME2 data type that provides better precision than either SMALLDATETIME or DATETIME, along with a much larger range of values.

 The DATETIMEOFFSET allows you to store a time zone for applications that need to localize dates and times.