SQLConfiguration SQLConfiguration


Table Column Attributes  «Prev  Next»
Lesson 2Datatypes
ObjectiveDescribe the different datatypes available for columns in a table.

Choosing storage space

Every column in every table in MS SQL Server must be declared as corresponding to a specific type of data, known as a datatype .
Each datatype stores a specific type of data, such as a numeric or text, but requires a certain amount of disk space to store. Therefore, for each datatype, you want to choose the minimum storage space, while providing enough room to hold all of your data.
The table below shows the range and description of values that can be stored, and the amount of disk space required for each of the datatypes available in MS SQL Server 2000. An (n) in the table indicates that the number of characters to store is specified by you.
Precision and Scale are discussed later in this module.

Unicode

You will notice the term Unicode in the table. Unicode is a standard by which more characters are available in a character set. This makes it possible to store data for other languages, such as Chinese. In fact, a standard character set allows for 256 distinct letters, numbers, and symbols, while a Unicode character set allows for 65, 536. Storing Unicode text takes twice as much disk space as a standard character.

Datatype Acceptable Value Range/Description Storage Space
Binary Fixed length binary data Up to 8,000 bytes
Bit Integer that stores only a 0 or 1 1 byte for each 8 bits
Char(n) Fixed length text data N is the number of bytes
Datetime Date and time data from 1/1/1753 to 12/31/9999 with an accuracy of 1/300 of a second 8 bytes
Decimal(p[,s]) Fixed precision (p) and scale (s) numeric data from –10E +38 to 10E +38 If p is 1 to 9, 5 bytes
If p is 10 to 19, 9 bytes
If p is 20 to 28, 13 bytes
If p is 29 to 38, 17 bytes
Float(n) Floating point numeric data from –1.79E + 308 to 1.79E +308. N indicates the precision for storage from 1 to 53. If n is 1 to 24, 4 bytes If n is 25 to 53, 8 bytes
Image Variable length binary data Up to 2,147,483,647 bytes
Int Integer from -2,147,483,648 to 2,147,483,647 4 bytes
Money Monetary data from –922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
Nchar Fixed length unicode text data Up to 4,000 bytes
Ntext Variable length unicode text data 2 times the number of characters stored, up to 1,073,741,823
Numeric Same as decimal Same as decimal
Nvarchar Variable length unicode text data Up to 4,000 bytes
Real Floating point numeric data from –3.40E + 38 to 3.40E +38. N indicates the precision for storage from 1 to 7. 4 bytes
Smalldatetime Date and time data from 1/1/1900 to 6/6/2079 with an accuracy to the minute 4 bytes
Smallint Integer from –32,768 to 32,767 2 bytes
Smallmoney Monetary data from –214,748.3648 to 214,748.3647 4 bytes
Text Variable length text data Up to 2,147,483,647 bytes
Timestamp Automatically updates changed table rows with current date and time 8 bytes
Tinyint Integer from 0 to 255 1 byte
Uniqueidentifier Stores unique values, called GUIDs that cannot be duplicated by any other computer in the world. These GUID values are generated by using the NEWID() SQL Server function 16 bytes
Varbinary Variable length binary data Up to 8,000 bytes
Varchar(n) Variable length text data 1 byte for every character stored, not the number declared in N.

Before you can create a table, you must know the type of data that each column will store. This will be specified in each column’s datatype when you create your tables.
In the next lesson, you will learn how to specify length, precision, and scale for table columns.