Data Types in Sqlserver 2005

Hi All in my second post on  i am going to describe Data Types available in sqlserver 2005

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.

INTEGER VALUES:
                                   tinyint    storage capacity 1 byte
                                   smallint  storage capacity 2 byte
                                   int          storage capacity 4 byte
                                   bigint     storage capacity 8 byte
FLOAT VALUES:
                                  Decimal(p,s)
where p is precision and s is scale.precision tells the otal number of digit in the value. storage value varies according to p value like
                                      if p is 1 - 9  then storage will be 5 byte
                                      if p is 10-19  then storage will be 9 byte
                                      if p is 20-28  then storage will be 13 byte
                                      if p is 29-38  then storage will be 17 byte
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 default precision is 18.

The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

CURRENCY VALUES:
                                           smallmoney   storage 4 byte
                                           money          storage  8 byte

these are used for storing currency values

BOOLEAN VALUES:    
                                         bit

                                        

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0. 

CHARACTER VALUES:

                                    Are character data types of either fixed length or variable length.

   char [ ( n ) ] 

 

Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.

 

    varchar [ ( n | max ) ] 

 

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.  nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.

nvarchar [ ( n | max ) ] 

 

Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying. Note:-  whenever you asked for differentiate char ,varchar or nvarchar or any other datatype like this you can say that first one is fixed length second is variable length and third one is for unicode support  

BINARY VALUES: To store image ,Audio files,vedio files we require binary value data types because they are internally store in binary formate.                                                    binary(n)                                                    varbinary(n)                                                    image( in sql server 2005  varbinary(max))                                                    DATE VALUES:                                smalldatetime   4 byte fixed                                                             datetime           8 byte default range of smalldatetime is  jan 1 1900 through jun 6 2079 default range of datetime is jan 1 1753 through 31 dec 9999 in sql server 2008 more datatime datatye have been added      datatime2,datetimeoffset