• Computer Using
• Windows
• Browser
• How-To
• Microsoft Office
• MS Word
• MS Excel
• MS Outlook
• MS PowerPoint
• Graphic / Design
• Photoshop
• Illustrator
• Server & Network
• Computer Networking
• Internet
• Windows Server
• Web Development
• HTML
• Javascript
• CSS
• ASP.NET
• PHP
• Animation
• Flash
• Silverlight
• Database
• Database System
• SQL Server
• T-SQL
• XML
• XML

Data Type in SQL Server 2005

Exact Number

bigint

bigint : is a type of data type number. It store value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. If your data is not in this range you shouldn’t use this datatype because it needs 8bytes to store your value.

int

int : is a type of datatype number. This datatype store value in range -2,147,483,648 to 2,147,483,647. And it needs 8bytes to store value.

smallint

smallint : is a type of datatype number. It store integer number in range -32,768 to 32,767. It’s storage 2bytes.

tinyint

tinyint : is a type of datatype number. If u want store value from 0 to 255 you should use tinyint datatype because it need 1bytes for storage to store your value. This datatype it can not store value negative or values more than 255.

bit

bit : is a datatype of Boolean. It store only True(1) , False(0) or Null. It need 1 byte to store value.

decimal

decimal : is a numeric data type. It store value precision and scale. It’s storage

• if you user 1-9 it need 5 bytes to store value
• if you user 10-19 it need 9 bytes to store value
• if you user 10-28 it need 13 bytes to store value
• if you user 29-38 it need 17 bytes to store value

Example

If you use decimal(1,0) you can enter value in range -9 to 9. If you use decimal(1,1) you can enter value in range -0.9 to 0.9. Syntax : decimal(p,s)

• p: is the digit of number that u want to fix. If you enter p=2 your value must be smaller than 1000 or biger than -100.
• s: it have value 0 or 1. If you enter s=0 your value will be don’t have point (ex. When u enter 0.2 it will change your number to 0)

If you enter s=1 your value must be in range -0.n to 0.n (n is your number).

numeric

numeric is functionally equivalent to decimal. It using and storage the same decimal.

money

money : is the data type use to store currency value. Money data type can be store value in range -922,337,203,685,477.5808 to 922,337,203,685,477.5807. it need 8 bytes to store your value.

smallmoney

smallmoney : it use the same money data type. But it store currency in range -214,748.3648 to 214,748.3647. and it need 4bytes to store value it small than money data type.

Approximate Numerics

float

float : is the numeric data type. It can store value in range - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

real

real : is the - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. It need 4 bytes to store value

Date Time

datetime

datetime : are use to store value date and time of day. It can store in date time in range January 1, 1753, through December 31, 9999. It need 8bytes to store value. 4 bytes to date and 4 bytes store time.

smalldatetime

smalldatetime : are use to store value date and time of day. It can store in date time in range January 1, 1900, through June 6, 2079. It’s store 2bytes for date and 2bytes for time.

Character Strings

char(n)

char(n) : use to store characters. This data type doesn’t support Unicode. It’s store up to n if n = 1 it need 1 byte. range of n>1 and n < 8001. Char data type it store Fixed-length
Example: If you use char(3) but you enter 1 character so it have 2spaces.

varchar(n/max)

Varchar(n/max) : use to store characters. This data type doesn’t support Unicode.It’s store up to n if n = 1 it need 1 byte. range of n>1 and n < 8001. Varchar data type it store variable-length.
Example: If you use varchar(3) but you enter 1 character so in your value have only 1 character don’t have 2spaces. When you varchar(max) it need 2^31-1 bytes to store data.

text

text: use to store any statement that varchar(max) can not store. Text data type can not support Unicode.

Example: If you have paragraph 1 page or more than one pages. You can use text data type to store it.

Unicode Character Strings

nchar(n)

nchar(n) : use to store characters. This data type is support Unicode. It’s store up to n if n = 1 it need 1 byte. range of n between 1 to 4000. Char data type it store Fixed-length
Example: If you use nchar(3) but you enter 1 character so it have 2spaces.

nvarchar(n/max)

nvarchar(n/max) : use to store characters. This data type is support Unicode. It’s storage up to n if n = 1 it need 1 byte. range of n between 1 to 4000. Varchar data type it store variable-length.
Example: If you use nvarchar(3) but you enter 1 character so in your value have only 1 character don’t have 2spaces.
When you nvarchar(max) it need 2^31-1 bytes to store data.

ntext

ntext : use to store any statement that nvarchar(max) can not store. ntext data type is support Unicode.

Binary Strings

binary(n)

binary(n) : use to store binary data. This data type is fixed length. n must be in range 1 to 8000. Storage size up to n.

varbinary(n/max)

Varbinary(n/max) : use to store binary data. It store data variable-length. It’s storage up to n if n = 1 it need 1 byte. When you nvarchar(max) it need 2^31-1 bytes to store data.

image

Image : this data type use to store picture.

cursor, sql_variant, table, uniqueidentifier and xml Data Type

cursor

cursor : is the data type that can store result set. And Any variables created with the cursor data type are nullable.
Example:

DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor

sql_variant

sql_variant : this data type can be used in columns, parameters, variables, and the return values of user-defined functions. sql_variant enables these database objects to support values of other data types. Column of data type sql_variant can store rows data in different data type. Like int, binary and char values. But it not support with data type: varchar(max), nvarchar(max) , text , image, sql_variant, varbinary, xml, ntext, timestamp and user defined taypes. It have a maximum length of 8016 bytes. And the maximum length of the actual base type value is 8,000 bytes.

table

table : Is a special data type that can be used to store a result set for processing later. table is primarily used is for temporary storage of a set of rows returned as the result set of a table-valued function.

uniqueidentifier

uniqueidentifier: this data type use to store data in hexadecimal digits. In range 0-9 or a-f. it format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx which is x hexadecimal digit.
Example: Uniqueidentifier store value like as 6F9619FF-8B86-D011-B42D-00C04FC964FF.

xml

xml : this data type use to store XML documents and fragments in a SQL Server database. xml data type instances cannot exceed 2 gigabytes (GB) in size.