Tuesday , June 19 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

Find all rows count and columns count in single database in sql server

In General for any database it contains lot of tables, if we want to find …

%d bloggers like this: