Thursday , October 18 2018
Home / DataBase / SQL SERVER / Data Types in SQL SERVER

Data Types 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.

      1.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

 

  1. Numeric Data types
  2. String Data types
  3. Date Time Data types
  4. Special Data types

 

Numeric Data types:

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

Datatype                                  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

2.String Data types:

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

Datatype                                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

Datatype                              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:

Datatype                             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.

Datatype                           Size

BINARY [(N)]                     1BYTE

*   VARBINARY [(N)]                  1BYTE

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

       Datatype                          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

 

 

Print Friendly, PDF & Email

Give us your valuable feedback

comments

About Samba

Check Also

sql server backup script

Backup all databases in SQL Instance with one script

Backup: A copy of SQL Server data that can be used to restore and recover the …