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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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











×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.