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