Wednesday, 30 November 2016

BIT, TINYINT, SMALLINT, INT and BIGINT

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

No comments:

Post a Comment