Tuesday , March 2 2021

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 TypeValue RangeAllocated space on disk
bigintMinimum:   -2^63 (-9,223,372,036,854,775,808)

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

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

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

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

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

2 Byte
tinyintMinimum:        0

Maximum:  255

1 Byte
bitIt 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/ numericMinimum:   – 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

moneyMinimum:   -922,337,203,685,477.5808 Maximum:  922,337,203,685,477.58078 Byte
smallmoneyMinimum:   – 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
dateMinimum:     0001-01-01

Maximum:   9999-12-31

3 Byte
smalldateMinimum:     1900-01-01

Maximum:  2079-06-06

4 Byte
datetimeMinimum:     1753-01-01 00:00:00.000

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

8 Byte
datetime2Minimum:     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

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

timeMinimum:     00:00:00.0000000

Maximum:   23:59:59.9999999

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

Char(10) -> 10 Byte

varcharBetween 0 and 8000Inserted Value + 2 Byte


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 647Maximum value:   2^31-1 (2,147,483,647) Byte
textBetween  0 and 2,147,483,647Maximum value :  2^31-1 (2,147,483,647) Byte
ntextBetween  0 and 1,073,741,823Maximum value:   2^30-1 (1,073,741,823) Byte
imageMaximum value:   2^31-1 (2,147,483,647) Byte
binaryBetween 0 and 8000Allocates the byte as much as the value it is defined.

Binary(10) -> 10 Byte

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

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

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


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

geometry,User-defined types,datetimeoffset

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


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.

XmlUsed for XML data.
TableUsed to store a result set for later use.
uniqueidentifierThis 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.


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

hierarchyidIt is used in hierarchical structures to represent positions in the hierarchy. It takes into account the slopes of the world.
geographyStores the coordinate system in the world.
geometryThis 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.

Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *