{"id":704,"date":"2018-06-24T18:00:05","date_gmt":"2018-06-24T11:00:05","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=704"},"modified":"2020-04-11T20:13:20","modified_gmt":"2020-04-11T13:13:20","slug":"sql-server-int","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-int\/","title":{"rendered":"SQL Server INT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how about the integer data types and how to use them effectively to store integer values in the database.<\/p>\n\n\n\n

SQL Server support standard SQL integer types including BIGINT<\/code>, INT<\/code>, SMALLINT<\/code>, and TINYINT<\/code>. The following table illustrates the range and storage of each integer type:<\/p>\n\n\n\n

Data type<\/th>Range<\/th>Storage<\/th><\/tr><\/thead>
BIGINT<\/strong><\/td>-263<\/sup> (-9,223,372,036,854,775,808) to 263<\/sup>-1 (9,223,372,036,854,775,807)<\/td>8 Bytes<\/td><\/tr>
INT<\/strong><\/td>-231<\/sup> (-2,147,483,648) to 231<\/sup>-1 (2,147,483,647)<\/td>4 Bytes<\/td><\/tr>
SMALLINT<\/strong><\/td>-215<\/sup> (-32,768) to 215<\/sup>-1 (32,767)<\/td>2 Bytes<\/td><\/tr>
TINYINT<\/strong><\/td>0 to 255<\/td>1 Byte<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

It is a good practice to use the smallest integer data type that can reliably contain all possible values. For example, to store the number of children in a family, TINYINT<\/code> is sufficient because nowadays no one could have more than 255 children. However, TINYINT<\/code> is would not be sufficient for storing the stories of a building because a building can have more than 255 stories.<\/p>\n\n\n\n

SQL Server Integers example #<\/a><\/h2>\n\n\n\n

The following statement creates a new table<\/a> that consists of four integer columns:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> test.sql_server_integers (\n\tbigint_col bigint<\/span>,\n\tint_col INT<\/span>,\n\tsmallint_col SMALLINT<\/span>,\n\ttinyint_col tinyint<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> test.sql_server_integers (\n\tbigint_col,\n\tint_col,\n\tsmallint_col,\n\ttinyint_col\n)\nVALUES<\/span>\n\t(\n\t\t9223372036854775807<\/span>,\n\t\t2147483647<\/span>,\n\t\t32767<\/span>,\n\t\t255<\/span>\n\t);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n\tbigint_col,\n\tint_col,\n\tsmallint_col,\n\ttinyint_col\nFROM<\/span>\n\ttest.sql_server_integers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Converting integer data #<\/a><\/h2>\n\n\n\n
SQL Server converts the integer constant greater than 2,147,483,647 to DECIMAL<\/code> data type, not BIGINT<\/code> data type as shown in the following example:<\/p>\n\n\n
SELECT<\/span> 2147483647<\/span> \/ 3<\/span> AS<\/span> r1, \n\t   2147483649<\/span> \/ 3<\/span> AS<\/span> r2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned various SQL Server integer data types and how to use them to store integers in the database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
The query example showed when the threshold value was exceeded, the data type of the result changed from INT<\/code> to a DECIMAL<\/a><\/code>.<\/p>\n\n\n\n
To show the values stored in the test.sql_server_integers<\/code> table, you use the following SELECT<\/a><\/code> statement:<\/p>\n\n\n
The following INSERT<\/a><\/code> statement adds the maximum integers of BIGINT<\/code>, INT<\/code>, SMALLINT<\/code>, and TINYINT<\/code> to the corresponding columns of the table:<\/p>\n\n\n