SQL Tutorials: BIT, TINYINT, SMALLINT, INT and BIGINT

Wednesday 30 November 2016

Differences among BIT, TINYINT, SMALLINT, INT and BIGINT data types.

	BIT	TINYINT	SMALLINT	INT	BIGINT
Bytes	1 byte Optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on	1 byte	2 bytes	4 bytes	8 bytes
Min Value	FALSE (it can be converted to 0)	0	-32768 - 2^15	-2147483648 - 2^31	-9,223,372,036,854,775,808 - 2^63
Max Value	TRUE(it can be converted to 1)	255	32767 2^15-1	2147483647 2^31-1	9,223,372,036,854,775,808 2^63-1
Max Length	Depends on its value	3 digits	5 digits	10 digits	19 digits
Type		unsigned integer (allows only 0 and positive value)	signed integer (allows 0, (–) and (+) numbers)	signed integer (allows 0, (–) and (+) numbers)	signed integer (allows 0, (–) and (+) numbers)
Example	DECLARE @Bit BIT = 'True' PRINT @Bit OUTPUT:1 DECLARE @Bit BIT = 0 PRINT @Bit OUTPUT:1	DECLARE @TinyInt TINYINT = 255 PRINT @TinyInt OUTPUT:255 DECLARE @TinyInt TINYINT = -1 PRINT @TinyInt OUTPUT: Error	DECLARE @SmallInt SMALLINT = 32767 PRINT @SmallInt OUTPUT: 32767	DECLARE @Int INT = 2147483647 PRINT @Int OUTPUT: 2147483647	DECLARE @BigInt BIGINT = 9223372036854775807 PRINT @BigInt OUTPUT: 9223372036854775807

SQL Tutorials