SQL Server Data Types(TSQL)

It is a very critical step to correctly determine SQL Server Data Types when designing tables to prevent unnecessary growth of the database. The space that data types allocate on the disk is different.

For example, suppose you have a column in your table that will contain numbers. If the value of this column will never exceed 32768, you can define it as smallint. Because the maximum value of smallint is 32768 and smallint allocates 2 bytes of space on the disk. If you define the same column as an integer, your application will work, but your database will grow unnecessarily because the integer will allocate 4 bytes of space on the disk.

I will give an important example and share the table detailing all types of data.

For example, you want to store a text column in your table. And the length of the values ​​on this column will vary between 1 and 100. You can use CHAR (100) to assign data type, you can also use VARCHAR (100).

Your application will work with two data types. But if you use CHAR, the space that allocated on the disk will be as much as the value you defined.

For example, you have defined a column with the CHAR (100) type, and you have inserted a 1 character data into this column. This data will be 100 bytes on the disk. If you define the column type as varchar (100) and insert 1 character data into that column, the space on the disk will be 1 + 2 = 3 bytes.

Data Type Value Range Allocated space on disk
bigint Minimum:   -2^63 (-9,223,372,036,854,775,808)

Maximum:  2^63-1 (9,223,372,036,854,775,807)

8 Byte
int Minimum:   -2^31 (-2,147,483,648)

Maksimum:  2^31-1 (2,147,483,647)

4 Byte
smallint Minimum:   -2^15 (-32,768)

Maximum:  2^15-1 (32,767)

2 Byte
tinyint Minimum:        0

Maximum:  255

1 Byte
bit It takes 0 or 1. If there are 8 or less bit columns in the table, it allocates 1 byte, and if there are more than 8 bit columns, it allocates 2 bytes.
decimal/ numeric Minimum:   – 10^38 +1

Maximum:   10^38 – 1.

According to its precision , the space allocated in the disk changes.

For precision from 1 to 9:                   5 byte

For precision from 10 to 19:               9 byte

For precision from 20 to 28:               13 byte

For precision from 29 to 38:               17 byte

money Minimum:   -922,337,203,685,477.5808 Maximum:  922,337,203,685,477.5807 8 Byte
smallmoney Minimum:   – 214,748.3648

Maximum:   214,748.3647

4 Byte
float -1.79308 ile –2.23308, 0

2.23308 ile    1.79308

4 bytes up to 7 digits

8 bytes up to 15 digits

Real -3.438  ile -1.1838, 0

1.1838 ile 3.438

4 Byte
date Minimum:     0001-01-01

Maximum:   9999-12-31

3 Byte
smalldate Minimum:     1900-01-01

Maximum:  2079-06-06

4 Byte
datetime Minimum:     1753-01-01 00:00:00.000

Maximum:  9999-12-31 23:59:59.997

8 Byte
datetime2 Minimum:     0001-01-01 00:00:00.0000000

Maximum:  9999-12-31 23:59:59.9999999

Sensitivity For 1-2 = 6 Bytes

Sensitivity For 3-4 = 7 Byte

Sensitivity For 5-7 = 8 Byte

datetimeoffset Minimum:     0001-01-01 00:00:00.0000000

Maximum:   9999-12-31 23:59:59.9999999

Time zone offset range: -14:00 / +14:00

Sensitivity For 1-2 = 8 Byte

Sensitivity For 3-4 = 9 Byte

Sensitivity For 5-7 = 10 Byte

time Minimum:     00:00:00.0000000

Maximum:   23:59:59.9999999

5 Byte(If used as default)
char Between 0 and 8000 Allocates the byte as much as the value it is defined.

Char(10) -> 10 Byte

varchar Between 0 and 8000 Inserted Value + 2 Byte

Varchar(100)

insert into table values(‘123’)-> 3+2=5 byte

insert into table values(‘123456’)-> 6+2=8 byte

varchar(MAX) Between  0 and 2 147 483 647 Maximum value:   2^31-1 (2,147,483,647) Byte
text Between  0 and 2,147,483,647 Maximum value :  2^31-1 (2,147,483,647) Byte
ntext Between  0 and 1,073,741,823 Maximum value:   2^30-1 (1,073,741,823) Byte
image Maximum value:   2^31-1 (2,147,483,647) Byte
binary Between 0 and 8000 Allocates the byte as much as the value it is defined.

Binary(10) -> 10 Byte

varbinary Between 0 and 8000 Inserted Value + 2 Byte
varbinary(MAX) Between 0 and 2 147 483 647 Inserted Value + 2 Byte

Maximum Value:  2^31-1 (2,147,483,647) Byte

sql_variant It is used to store the values of some data types. Except the following:

varchar(max),varbinary(max),

nvarchar(max),xml,text,ntext,image,rowversion (timestamp), sql_variant,geography,hierarchyid,

geometry,User-defined types,datetimeoffset

cursor For use in SQL Server cursor. You may want to read my “Cursor Usage In SQL Server” article about cursor.
timestamp/

rowversion

There is an automatic incremental counter when an insert or modification occurs in a table in the database.

This counter is the rowversion of the database. One column can be defined as a rowversion column.

Timestamp is the same as rowversion and will not be used in later versions.

Xml Used for XML data.
Table Used to store a result set for later use.
uniqueidentifier This data type stores the GUID data (guaranteeing the singularity globally).

When you run the select NEWID() script, it generates a GUID of data as follows.

A4C5DB26-7F18-4B4F-A898-E7DE26A8446A

Sometimes it is used to provide singularity in databases. But when used for this purpose, it generally lowers performance.

hierarchyid It is used in hierarchical structures to represent positions in the hierarchy. It takes into account the slopes of the world.
geography Stores the coordinate system in the world.
geometry This data type stores the coordinate system with the Euclidean (flat) system. Calculated only on 2 planes. It does not take into account the slopes of the world.