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.
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.