Data Types:

Char and Varchar:

1) Char data type is fixed size. a) Database engine will allocate memory based on the size of char data type. 2) Varchar data type is dynamic. a) Database engine will allocate memory based on the actual value which is being stored into varchar type variable.

Syntax:

declare @ variablename datatype [(size)]; variable syntax(optional)
set @variablename = data; // data should match the datatype.

nchar and nvarchar:

a) nchar data type is fixed size data type. b) By using nchar, we can store Unicode characters or globalized characters including English characters, special characters and numbers. a) nvarchar is varying size data type. Note: 1. In t-sql unsigned data types are not supported. 2. In place of string data type, we got 6 data types in t-sql
char, varchar, varchar(max) nchar, nvarchar,nvarchar(max) variable string
3. We must use single quotation " ' " for storing the data into char, varchar, varchar(max), nchar, nvarchar, nvarchar(max)

Data Types in SQL Server 2008:

bigintbinarybitcharcursor
datetimedecimalfloatimageint
moneyncharvarbinary(max)numericnvarchar
real(old data type~=float)smalldatetimesmallintsmallmoneysql variant
tablevarchar(max)timestamptiny intvarbinary
smalldatetimedatetimedate*datetime2*time*
varcharuniqueidentifierxml
Note: By using char and nchar data types we can store upto 8000 characters, with varchar(max) we can store data upto 2^31 characters.

Sizes of Data Types:

TypeSize
int or integer4 bytes
Bigint8 bytes
Smallint2 bytes
Tinyint1byte
Decimalcan store 38 digits all 38 digits can Be after decimal point
Charcan store max 8000 characters
Varcharcan store max 8000 characters
varchar(max)can store upto 2^31 characters
Bit1 bit (it can store either 1 or 0) usually used for storing true/false.
Money8 bytes
Small money4 bytes
Note: i. We must use bit data type in place of Boolean data type, since t-sql will not support Boolean data type. ii. We are not using money and small money data type, which is same as float, decimal iii. $10000 - we must store this in varchar datatype.

Range of date-time Data Types:

DatatypeRangeAccuracy
DatetimeJanuary 1, 1753, through December 31, 99993.33 milliseconds
Smalldate timeJanuary 1, 1900, through June 6, 20791 minute
Datetime 2January 1, 0001 through December 31, 9999100 nano seconds

Large Value Data Types:

1. Microsoft SQL Server 2005 introduces the max specifier. 2. This specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. 3. Varchar(max), nvarchar(max), and varbinary(max) are collectively called large value data types.(2^31-1 bytes of data)