Data Type In Sql Server

Data Types in SQL SERVER:

Data Type means the type of data which users provided to a specific column

In SQL Server 2 types of data types available which includes

            1. System Defined Data Types

            2. User Defined Data Types.

System Defined Data Types:

              SQL Server already contains some data types called System Defined data types or Predefined Data types or Built-in Data types. System Defined Data Types again categorized into 4 types

a.      Numeric Data types

b.      String Data types

c.       Date Time Data types

d.      Special  Data types

Numeric Data types:

            These Data types are used to provide numeric information for the columns, these includes

                        Data Type                              Size

                        BIT                                          0 or 1

                        TINYINT                                1 BYTE

                        SMALLINT                            2 BYTES                   

                   *   INT                                         4BYTES

                        BIGINT                                  8BYTES

                        REAL                                     4BYTES

                        FLOAT                                   8BYTES                                

                        DECIMAL (P, S)                   5-17 BYTES

TINYINT, SMALLINT, INT, BIGINT are called Exact Numerics where as REAL, FLOAT are called Approximate Numerics.

  

String Data types:

            These Data types are used to provide character information for the columns, these includes

                        Data Type                               Size

                          CHAR [(N)]                          1BYTE

                     *   VARCHAR [(N)]                 1BYTE

                          TEXT                                    16BYTES

                        VARCHAR(MAX)                16 GB

  CHAR [(N)]: It is a fixed length data type, which occupies by default 1 byte memory. When we N value then it occupies N bytes of memory where N is an integer. It follows Static memory allocation process.

VARCHAR [(N)]: It is a variable length data type, which occupies by default 1 byte memory. When we N value then it occupies N bytes of memory where N is an integer. It follows dynamic memory allocation process.

Note : The Maximum limit for N is 8000,if it is more than 8000 characters we will use TEXT or VARCHAR(MAX)

Date Time Data Type:

            These data types are used to provide date oriented information to the columns, these includes

Data Type                               Size                 Range

                        SMALLDATETIME              2 BYTES        1900-01-01 TO 2079-06-06

                   *   DATETIME                            4BYTES         1753-01-01 TO 9999-12-31

Special Data types:

These data types are used to provide miscellaneous information to the columns, these includes

Data Type                               Size

                        SMALLMONEY                   4 BYTE

            *          MONEY                                 8 BYTES       

IMAGE                                   16 BYTES

                        VARBINARY (MAX)          Unlimited

1.      SQL_VARIANT

1. Binary Data types: These stores binary values of a given string in ordered to hide the original string values.

                                       Data Type                            Size

                                      BINARY [(N)]                     1BYTE

                           *   VARBINARY [(N)]                  1BYTE

2. Unicode Data types: These Data types are used to store Unicode information, these includes

                                       Data Type                            Size

                                      NCHAR [(N)]                       2BYTE

                           *   NVARCHAR [(N)]                    2BYTE

                                          NTEXT                       16BYTES

II. User Defined Data Types:

When user create a data type then that data type is called user defined data type

Syntax:

CREATE TYPE USER_DEFINED_DATATYPE FROM SYS_DEFINED_DATATYPE

Ex:

CREATE TYPE MYINT FROM INT

CREATE TYPE MYFLOAT FROM FLOAT

CREATE TYPE CASH FROM MONEY