Table Column Attributes  «Prev  Next»

Lesson 2Datatypes
ObjectiveDescribe the different datatypes available for columns in a table.

Datatypes Available for Table Columns

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.

Understanding Unicode Data Types in Microsoft SQL Server

In Microsoft SQL Server, the handling of international character sets is imperative, especially given the global nature of modern databases. Recognizing this need, Microsoft SQL Server has long incorporated Unicode data types to accommodate diverse character sets.
  1. Unicode Data Types in SQL Server: Microsoft SQL Server offers two primary Unicode data types:
    • `NCHAR`: This is a fixed-length Unicode character data type. Each `NCHAR` character takes two bytes of storage, making it suitable for storing characters in multiple languages concurrently.
    • `NVARCHAR`: This data type is a variable-length version of the Unicode character data type. Similar to `NCHAR`, each `NVARCHAR` character occupies two bytes. The key distinction is in its variable storage length, accommodating text strings of varying lengths up to a specified maximum.
    Both `NCHAR` and `NVARCHAR` data types utilize the UCS-2 character encoding standard, which covers most frequently-used characters in major languages.
  2. Storage Implications: The dual-byte storage model used by the Unicode data types in SQL Server ensures a comprehensive representation of a wide range of characters. However, this also means that Unicode data types may consume more storage space than their non-Unicode counterparts (`CHAR` and `VARCHAR`). Database administrators should be aware of this trade-off when designing their databases, weighing the requirements for multilingual support against storage optimization.
  3. Usage Note:
    When defining Unicode string literals in SQL Server, they must be prefixed with an upper-case 'N'. For instance: 
    INSERT INTO TableName (UnicodeColumnName) VALUES (N'UnicodeText');

    This 'N' prefix distinguishes Unicode literals from non-Unicode text.
  4. Additional Unicode Data Type: `NTEXT`: In earlier versions of SQL Server, there was an `NTEXT` data type designed for holding longer Unicode strings. However, it's essential to note that `NTEXT` is deprecated in recent versions of SQL Server. Microsoft recommends using `NVARCHAR(MAX)` instead, given its flexibility and broader application.
Microsoft SQL Server unequivocally supports Unicode data types, recognizing the need for databases to handle diverse character sets in a globalized world. Utilizing `NCHAR` and `NVARCHAR`, database professionals can ensure their systems are equipped to store and process multilingual data with precision and reliability.

Ad Azure SQL