Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 22, 2002

SQL Server 2000 Administration in 15 Minutes a Week: Table Basics (Part 1) - Page 2

By Michael Aubert


Every column in your table must have a "data type," which is simply a property that defines what type of data is stored in that column. In addition, a data type will reject data that is not of the correct type (i.e. attempting to store a letter in a data type designed for numbers). SQL Server has over 25 different data types -- some with more options than others. Let's look at the different data types and the options for each:

 



Data Type Size Description
Integers    
bigint 8 bytes Holds integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).
int 4 bytes Holds integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
smallint 2 bytes Holds integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).
tinyint 1 byte Holds integer data from 0 through 255.
bit 1 byte for up to 8 bit columns Holds integer data with either a 1 or 0 value. Each set of up to 8 bit columns requires 1 byte. So if there are anywhere from 1 to 8 bit columns in a table, the storage space you will need is 1 byte. If there are anywhere from 9 to 16 bit columns in a table, the storage space you will need is 2 bytes. And so on...
Decimal    
decimal Anywhere from 5 to 17 bytes depending on the precision Holds fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The Precision specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through 38. The Scale specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through Precision. Examples:
if precision is set to 10 and scale is set to 3 the smallest (other than 0)/ largest number we could store would be 0.001 / 9999999.999
if precision is set to 8 and scale is set to 6 the smallest (other than 0)/ largest number we could store would be 0.000001 / 99.999999 
numeric - Same as decimal data type
Money    
money 8 bytes Holds monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (922,337,203,685,477.5807), with accuracy to one ten-thousandth of a monetary unit
smallmoney 4 bytes Holds monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
Approximate    
float(n) Anywhere from 4 to 8 bytes depending on the precision Holds floating precision number data from -1.79E + 308 through 1.79E + 308. The value n is the number of bits used to store the mantissa of the float number and can range from 1 to 53
real 4 bytes Holds floating precision number data from -3.40E + 38 through 3.40E + 38. Real is the same as float(24).
Date and Time    
datetime 8 bytes Holds date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
smalldatetime 4 bytes Date and time data from January 1, 1900 through June 6, 2079, with accuracy to the minute.
Strings - non-Unicode  
char(n) n bytes Holds fixed-length non-Unicode character data with length of n characters, where n is a value from 1 through 8000. If less than n number of characters are entered n bytes are still required because unused character spaces get padded to the end to make them n bytes long.
varchar(n) Depends on the length of data entered - 1 byte per character Holds variable-length non-Unicode character data with a length of n characters, where n is a value from 1 through 8000. The storage size is the actual length in bytes of the data entered, not n bytes.
text 16 bytes for the pointer Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters. A text column entry can hold up to 2^31 - 1 characters. It is a pointer to the location of the data value, the data is stored separately from the table data.
Strings - Unicode  
nchar(n) 2 bytes * n Holds fixed-length Unicode character data of n characters, where n is a value from 1 through 4000. Unicode characters use 2 bytes per character and can support all international characters. If less than n number of characters are entered n bytes are still required because unused character spaces get padded to the end to make them n bytes long.
nvarcher(n) Depends on the length of data entered - 2 byte per character Holds variable-length Unicode data of n characters, where n is a value from 1 through 4000. Unicode characters use 2 bytes per character and can support all international characters. The storage size is the actual length in bytes * 2 of the data entered, not n bytes.
ntext 16 bytes for the pointer Holds variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. The column entry for ntext is a pointer to the location of the data. The data is stored separately from the table data
Binary    
binary(n) n + 4 bytes Holds fixed-length binary data of n bytes, where n is a value from 1 through 8000. Use binary when column data entries are consistent in size.
varbinary(n) Depends on the length of data entered + 4 bytes Holds variable-length binary data of n bytes, where n is a value from 1 through 8000. Use varbinary when column data entries are inconsistent in size.
image 16 bytes for the pointer Used for variable-length binary data longer than 8000 bytes, with a maximum of 2^31 - 1 bytes. An image column entry is a pointer to the location of the image data value. The data is stored separately from the table data
Other    
sql_variant size varies A column of type sql_variant may contain rows of different data types. For example, a column defined as sql_variant can store int, binary, and char values. The only types of values that cannot be stored using sql_variant are text, ntext, image, timestamp, and sql_variant.
timestamp 8 bytes Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. Each table can have only one timestamp column.
uniqueidentifier 16 bytes Stores a 16-byte binary value that is a globally unique identifier (GUID).


Page 3: Data Types (Continued)


 » See All Articles by Columnist Michael Aubert



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date