CAST VS CONVERT

Data Type CAST and CONVERT (Transact-SQL)

It is used to convert an expression of one data type to another. 

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. 

Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. 

CAST	CONVERT
It is  ANSII Standard.	It is Specific to SQL SERVER.
It can’t be used for Formatting Purposes.  For example  PRINT CAST('12/01/2016' AS DATE) OUTPUT:  2016-12-01 Here, By default it produces result   ISO8601 standard.	It can be used for Formatting Purposes by passing the appropriate style as parameter. For example  PRINT CONVERT(VARCHAR(20), GETDATE(), 101) OUTPUT:  12/01/2016 Here, We are able to pass style 101 for producing the result as US standard.

The following chart shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

[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

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