Data types in Microsoft SQL Server 2005

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! Comment Name Email Website   Add Comment Trackback(0) Comments (10)

Last Updated ( Thursday, 06 September 2007 )