Latest tutorial: Making a Movieclip face another Movieclip or point on the stage | Ask Tutorial5!
 

Build Your Own Database Driven Website Using PHP & MySQL

SitePoints Best Seller book on scripting.

  • Learn how to install and administer PHP & MySQL on Windows, Linux or Mac

  • Build your very first live Database Driven Website using PHP & MySQL

  • Instantly apply working code examples from the book to your Website


Download the free chapters now!

Data types in Microsoft SQL Server 2005

(11 votes)
Written by MoN   

Every column in a Microsoft SQL Server 2005 table must hace a data type. MS SQL Server 2005 has a series of system data types readily available upon instalation. You can also add alias data types based on the system data types  (see Working with alias data types in SQL Server 2005 using T-SQL) and custom data types by using the CLR integration.

In the next table you have a list of the system data types available in MS SQL Server 2005.

  >Data type name >Number of bytes >Comments
Integer tinyint 1  
smallint 2  
int 4  
bigint 8  
Exact numeric decimal[p[,s]] 5-17  
numeric[p[,s]] 5-17  
Appropiate numeric float[(n)[ 8  
real 4  
Monetary smallmoney 4  
money 8  
Date and time smalldatetime 4  
datetime 8  
Characters char[(n)] 0-8000 non-Unicode
varchar[(n)] 0-8000 non-Unicode
varchar(max) 0-2 GB non-Unicode, 16 bytes pointer on row, ,preferred over text data type
text 0-2 GB non-Unicode, 16 bytes pointer or in row, obsolete, varchar(max) prefered
nchar[(n)] 0-8000 max 4000 unicode characters
nvarchar[(n)] 0-8000 max 4000 unicode characters
nvarchar(max) 0-2 GB 16 bytes pointer or in row, ,preferred over ntext data type
ntext 0-2 GB 16 bytes pointer, obsolete, nvarchar(max) prefered
Binary binary[(n)] 0-8000  
varbinary[(n)] 0-8000  
varbinary(max) 0-2 GB 16 bytes pointer or in row, ,preferred over image data type
Image image 0-2 GB 16 bytes pointer, obsolete, varbinary(max) prefered
Global identifier uniqueidentifier 16  
XML xml 0-2GB 16 bytes pointer
Special bit 1 1 byte for every 8 bit columns
cursor 0-8  
timestamp 8 one column per table
sysname 256  
table -  
sql_variant 0-8016  


You should take care of some facts when you choose a data type for a variable or for a column.


When using exact numeric data types you can specifie the scale and precision (digits on the left and on the right side of the decimal point. Data will always be returned exactly as entered. In the case of the approxiamete numeric data types the MS SQL Server will tyy to keep the data as accurately as possible, For example 2/3 which is 0.(6) cannot be stored without loss so an approximation is stoted in the database. You should take care when using approximate numeric data types in WHERE clausebecause of the way data is stored because 1/3 + 2/3 = 3/3 = 1 but 0.(3) + 0.(6) = 0.(9) <> 1.

When you store characters in a table, if the length of the text is not always the same you should use varchar on nvarchar. Even if it use some extra processing power, the benefits of not wasting space on the harddisk and on the database page that is holding the row is greater then the loss.

The smalldatetime data type can be used to store dates between January 1st, 1900 and June 6th, 2079, values been kept in approximation to the nearest minute. The datetime data type can store dates between January 1st, 1753 and December 31, 9999 with an accuracy of one three-hundredth of a second.

The timestamp data type is used to version-stamp table rows. You can hava only one timestamp column per table and it is not necessarly to specify a name for the timestamp column when you create a table.

Subscribe now via RSS feed and get all the new tutorials

written by Samaya , September 19, 2007

Can someone please let me know what datatype can be used to store any special characters?
written by Matthew , September 25, 2007

Samaya,
You can use the nvarchar datatype to store unicode characters.
written by User , February 11, 2008

Can someone explain me what exactly is sysname datatype means?

Thanks
written by rupa , February 26, 2008

how to change precision of money type
written by D"Silva Az , March 15, 2008

i want to store photographs of students in the database.im using sql server2005 & vb.net.How would i make use of image data type in sql server.
written by benpod , April 01, 2008

how do you retrieve images stored in an sql server 2005 db using classic asp?
written by Noel , June 13, 2008

D"Silva Az,

You should use varbinary(max) instead of image.
Image is going to be deprecated in SQL 2008 and/or future versions of SQL Server
written by prabhakar , June 27, 2008

create table emp(fullname varcchar(20),dob datetime,city varchar(20)
insert into emp values(prabhakar,26/07/1983,chennai)
select * from emp


whenever execute the program in the date error will be coming like jan 1 1966 12:00am...i gave one date ..the result came diffrent

can some one plz help me
written by anvesh , July 02, 2008

can u please tell me ,what is the difference between ntext and text?
written by mantuiala , July 03, 2008

in the case of ntext the data is kept using the Unicode format and each char will occupy 2 bytes. In the case of text the data is kept using only 1 byte per char so no Unicode data.

Do you need more help? Ask now!
 

busy
Last Updated ( Thursday, 06 September 2007 )