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. written by DO3AA , August 03, 2008 HOW CAN i GET THE DATA TYPE OF SOME COLUMN BY A QUERY? written by sonali , August 04, 2008 Can u plz tell me how to delcare '$200' while creating table. written by karthikpulla , September 02, 2008 is there any data type that stores true OR false written by abc , October 24, 2008 bit is basically used to represent True or False. using 1 or 0. SELECT CONVERT(bit, 'true') SELECT CONVERT(bit, 'false') SELECT CASE CONVERT(bit, 'false') WHEN 0 THEN 'false' WHEN 1 THEN 'true' ELSE 'unknown' END SELECT CASE CONVERT(bit, 'true') WHEN 0 THEN 'false' WHEN 1 THEN 'true' ELSE 'unknown' END written by abc , October 24, 2008 Forgot to put the null case SELECT CASE CONVERT(bit, NULL) WHEN 0 THEN 'false' WHEN 1 THEN 'true' ELSE 'unknown' END written by CharlesNguyen , October 27, 2008 How to retreive large value data types from sql server 2005 using select value? Please help. written by MANJEET , December 03, 2008 HOW TO KNOW DATATYPE OF ANY VALUE I TSQL written by Desta , December 25, 2008 I need to parse a Date of my interest as "dd/mm/yyyy" format using Java. I try in such a way: date = simpleDateFormat.parse(String); But could n't parse correctly to the format i need it. So could you help me please? written by Kal , January 24, 2009 how do you set a date column in a table to store/display as DD/MM/YYYY format? I've tried using the 'Date' data type for a column called Date of Birth but it displays with a time after it - (which i don't want). Any help appreciated.. written by SubbarayuduC , January 31, 2009 I want some more help on this topics. I want exactly,i want add files.in that matter i want to store in MSSQL2005 database.I have read file in Bytes.for that what is the Data type in MSSQL2005 written by bala , February 02, 2009 Is there Boolean type exist in SQL SERVER 2005? Thanks in Advance written by Blader , February 11, 2009 SELECT OBJECT_NAME(c.OBJECT_ID) TableName ,c.name AS ColumnName ,SCHEMA_NAME(t.schema_id) AS SchemaName ,t.name AS TypeName ,t.is_user_defined ,t.is_assembly_type ,c.max_length ,c.PRECISION ,c.scale FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id ORDER BY c.OBJECT_ID; written by johnson , March 02, 2009 How can i declare an autonumber field in SQLServer 2005?I neet such a field to be a primary-key for a cCustimer's Table. written by Shail , March 08, 2009 How can I make a column in SQLServer 2005 to store only date and not datetime. written by avinashg1 , March 12, 2009 how much data can be stored in sql server2005 in terms of rows written by sanu , March 18, 2009 how to retrive the image content in sql which is greater than the max value written by Mohd Ejaz , March 22, 2009 how to save the Image DATA Type in sql server i am using Vb.net2008 & Backend Sql Server2005 pls try to send the Respond Thanking You; Regards Ejaz written by dgfhghken , April 30, 2009 how about you answer all peoples question! there are so many unanswered questions. i bet the owners needed an answer, or at least were hoping you would. written by Samitha , May 18, 2009 You can define that particular column as BINARY, depend on the size you want to store you can use varbinary(n) up to 8000bytes or varbinary(max) can hold up to 2GB size image. CREATE TABLE StoreImage(imgFile varbinary(max)) INSERT INTO StoreImage(imgFile) SELECT * FROM OPENROWSET(BULK N'C:Image1.jpg', SINGLE_BLOB) written by asddddddd , June 26, 2009 DO YOU NEED MORE HELP? ASK NOW! written by shafina , July 02, 2009 How can i declare an autonumber field in SQLServer 2005? written by ggg , July 08, 2009 rtrtr written by bitsman , August 12, 2009 I need to use boolean data type in a table as the app accessing it has already been designed with true and false, is there a way out ... I don't see the boolean data type ... is there any thing else we can use apart from bit written by ToeToeAung , September 07, 2009 i want to add a button in KryptonDataGridView. can you explain me? i want to use this button's event. with regard, ToeToeAung written by pawan kumar , September 14, 2009 how to use date formate in a textbox, How to use nvarchar datatype as primarykey using as autonumber written by Paul Williams , October 05, 2009 Repeating a question asked but not answered previously ... How can one determine the datatype of a value in T-SQL? written by Rupa , October 28, 2009 i want to store video file in database plz help me written by Abat , November 06, 2009 I would like to appreciate you for your work.I get a lot from you,and thanks so much more.I want to get more about SQL Server 2005,please. written by Priyanka , November 06, 2009 I am planning to do MCTS certification for sql 2005. Can anyone get me some study materials for that? written by ephrem kinfe , November 23, 2009 how to create and how access a SQLserver 2005 written by dasdasdas , January 02, 2010 Thanks for everything Do you need more help? Ask now! Comment Name Email Website   Add Comment Trackback(0) Comments (41)

Last Updated ( Thursday, 06 September 2007 )