Thursday, 1 December 2016

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.



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

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


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