Data Type

.We need data type to defined Column, local variable, expression, and parameters
.An attribute that specifies the type of data(integer, characters, money and so on) that the object can hold.
.User defined data type is also there.

Characters encoding fall in below two types

Non-Unicode: Uses 1 byte(8 bits) to encode each characters.
It can hold 0 – 255 different characters.

Unicode : It comes into the picture when we are storing chineese , french and etc language data.
It uses 2 bytes to encode each characters.
It can represents 65,536 characters.

You can minimize the characters conversion issues by using Unicode data types throughout your system.

SQL server supports
-nchar
-nvarchar
-ntext
It requires wider range of characters. And more space is needed to store Unicode characters.

The maxium size for nchar and nvarchar is 4000 not 8000 like char and varchar (because it takes 2 bytes to store single char).

Unicode constants are specified with a leading N: N’A Unicode string.

Below are the different categories

.Exact Numeric
.Approximate Numeric
.Characters String
.Binary

Exact Numeric: Uses integer data
-bigint -storage Size 8 bytes : -2^63 – 2^63-1
-int : 4 bytes
-smallint : 2bytes
-tinyint : 1 byte :  0-255
-Bit : 0,1, null

-Decimal and numeric:
-Decimal /Numeric:
.Fixed precision and scale numeric  : -10^38 + 1 to 10^38-1
.Precision : maximum number of decimal digits cab be stored, both to the left and right of the decimal point
.Scale : maximum number of decimal digits to the right of the decimal point/
-Monetary Data type to represent monetary or currency value:
-money : -2^63 – 2^63-1 : 8 bytes
-smallmoney : 4 bytes

Approximate Numeric:
.Approximate number data types use with floating point numeric data, not all values in the data type range can be precisely represented.
-Float : -1.79E+308 – 1.79E+308
-Real : -3.40E+38 through 3.40E38

Datetime : January 1, 1753 – December 31,9999 accuracy of one three hundredth of a second.
Smalldatetime : January 1, 1900 through June 6, 2079 with accuracy to the minute

Character String:
Fixed length char and variable length char.

-char[n] max. size 8000 storage size n bytes
-varchar[n] : max. 8000 storage size depend on data stored.
-text : variable length non-Unicode data in the code page of the server and with a max length of 2^31-1 chars.

Binary String:  This can be either fixed or variables
-binary[n] : max size is 8000, storage size is n+4 bytes.
-varbinary[n] : max size is 8000, storage size is n+4 bytes.
-image : variable length 0 through 2^31 -1 bytes

Unicode :
nchar[n] : Fixed length : 1 – 4000 size.
nvarchar[n] : variable length : 1 – 4000 size.
ntext : variable length : 2^30 -1

 

Advertisements

#data-type