Sunday, January 25, 2015

SQL SERVER - Data types

In relational database, we store data in tabular form where data is divided into columns. Each column has a name and a data type which shows what type of data is going to store in that column. Below are the data types which are mostly used in SQL server. 

CHARACTER(n)    Character string of fixed length n

VARCHAR(n)        Character string of variable length with maximum length n

VARCHAR(MAX)   Character string of variable length with maximum length                                can exceed 8000 byes

NCHAR(n)            Fixed-length Unicode string data where n defines the string                            length and must be a value from 1 through 4,000

NVARCHAR(n)     Variable length Unicode string data where n defines the                                  string length and must be a value from 1 through 4,000

NVARCHAR(MAX) Variable length Unicode string data where MAX indicates                                 that the maximum storage size is 2^31-1 bytes (2 GB)

BINARY(n)           Binary string of fixed length n

BOOLEAN             It stores TRUE or FALSE values

VARBINARY(n)    Binary string of variable length with maximum length n

NTEXT                  Variable-length Unicode data with a maximum string length                            of 2^30 - 1 (1,073,741,823) bytes

TEXT                    Variable-length non-Unicode data in the code page of the                               server and with a maximum string length of 2^31-1                                       (2,147,483,647)

IMAGE                 Variable-length binary data from 0 through 2^31-1                                          (2,147,483,647) bytes.

INTEGER Or Int   A 32-bit signed integer value having range of INTEGER is                                -2147483648 to 2147483647

SMALLINT           A 16-bit signed integer value having range of SMALLINT is                              -32768 to 32767.

BIGINT               A 64-bit signed integer value having range of BIGINT is                                   -9223372036854775808 to 9223372036854775807

DECIMAL(p,s)    Exact numerical, precision p, scale s. For Example,                                         Decimal(5,3) means 22.212

NUMERIC(p,s)   Exact numerical, precision p, scale s. (Same as DECIMAL)

REAL                  Approximate numerical, mantissa precision 7

FLOAT                Approximate numerical, mantissa precision 16

DOUBLE             Approximate numerical, mantissa precision 16

DATE                  It stores year/month/day values

TIME                  It stores hour/minute/second values

TIMESTAMP       It stores year/month/day/hour/minute/second values

XML                    It stores XML data

No comments:

Post a Comment