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

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF

DECIMAL/NUMERIC, FLOAT and REAL

Differences among DECIMAL/NUMERIC, FLOAT and  REAL data types.

	DECIMAL/ NUMERIC	FLOAT	REAL
Bytes	5 bytes  - 17 bytes Maximum storage sizes vary, based on the precision Precision Storage bytes 1 - 9 5 10-19 9 20-28 13 29-38 17	4 bytes to 8 bytes depends on nvvalue nvalue Precision Storage size 1-24 7 digits 4 bytes 25-53 15 digits 8 bytes	8 bytes
Minimum Value	- 10^38 +1	- 1.79E+308 to -2.23E-308, 0	- 3.40E + 38 to -1.18E - 38, 0
Maximum Value	10^38 - 1	2.23E-308 to 1.79E+308	1.18E - 38 to 3.40E + 38
Max Length	38 digits The default precision is 18.
Format	DECIMAL(p) DECIMAL(p,s) p- precision s- scale	FLOAT(s)	REAL
Example	DECLARE @Decimal DECIMAL(38) = 92233720368547758079223372036854775807 SELECT @Decimal DECLARE @Decimal DECIMAL(38,2) = 122337203685477580792233720368547758.07 SELECT @Decimal DECLARE @Decimal DECIMAL(38,3) = 12233720368547758079223372036854775.807 SELECT @Decimal DECLARE @Numeric NUMERIC(38) = 92233720368547758079223372036854775807 SELECT @Numeric DECLARE @Numeric NUMERIC(38,2) = 122337203685477580792233720368547758.07 SELECT @Numeric DECLARE @Numeric NUMERIC(38,3) = 12233720368547758079223372036854775.807 SELECT @Numeric 54775 – Unit . – Decimal point 807 - Mantissa	DECLARE @Float FLOAT(53) = 92233720368547758079223372036854775807 SELECT @Float DECLARE @Float FLOAT(1) = 922337.47 PRINT @Float OUTPUT: 922338 DECLARE @Float FLOAT(1) = 922337.46 PRINT @Float OUTPUT: 922337	DECLARE @Real REAL = 92233720368547758079223372036854775807 SELECT @Real DECLARE @Real REAL = 922337.47 PRINT @Real OUTPUT: 922338 DECLARE @Real REAL = 922337.46 PRINT @Real OUTPUT: 922338

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF

SMALLMONEY VS MONEY

What are the differences between SMALLMONEY and MONEY [SMALLMONEY VS MONEY]?

Which data type should use for defining a column in a table [SMALLMONEY VS MONEY]?

	SMALLMONEY	MONEY
Bytes	4 bytes	8 bytes
Minimum Value	- 214,748.3648	-922,337,203,685,477.5808
Maximum Value	214,748.3647	922,337,203,685,477.5807
Max Length
Format	SMALLMONEY - Round of 2 digit	MONEY – Round of 2 digit
Example	DECLARE @smallmoney SMALLMONEY = 210000.36125 PRINT @smallmoney OUTPUT: 210000.36 DECLARE @smallmoney SMALLMONEY = $210000.36125 PRINT @smallmoney OUTPUT: 210000.36 210000 – Unit . – Decimal point 36 - Mantissa	DECLARE @money MONEY = 2102333434000.365251232 PRINT @money OUTPUT: 2102333434000.37 DECLARE @money MONEY = $2102333434000.365251232 PRINT @money OUTPUT: 2102333434000.37

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF