Choosing the right data type is an important part of table design
A future T-SQL for Starters column will address using the T-SQL syntax to build tables. But before I start building tables, I need to talk about data types. Every column in a table must have a defined data type, such as integer or character, that controls the values you can enter into that column. In this article, I look at the system-defined data types and explain how you can add your own data types. You need to add your own data types before you start building the tables that might reference them. User-defined data types can add a level of consistency and control to projects with several developers working on the same database.
Columns Need Data Types
When you create a table, you must list a data type for every column. Any data entered into a column must match the data type for that column. In some cases, you must also specify the length of the column. The SQL statement in Listing 1 uses only system-supplied data types to create the Authors table in the Pubs database.
Most of the columns in this table are text fields. The phone number, state, and ZIP are always the same length, so they use the char data type. The author's name, address, and city can vary in length and can use the varchar, or variable-length character, data type. The contract column is a yes/no column that tells whether the author has a contract; here a bit data type, with a value of 0 or 1, will suffice. Let's take a closer look at the system-defined data types.
System-Defined Data Types
SQL Server 6.x comes with about 19 predefined data types; SQL Server 7.0 added four more to handle Unicode data and to support data-warehousing applications. Each data type has a range of values it can contain, and knowing these values can help you choose among the various options.
Character data. Character, or char, is one of the most common data types. This type holds character data such as names and addresses. SQL Server 6.5 limits the size of any character column to 255 characters. If your data is longer—for example, if you want to add free-form notes about a contact—you must use the text data type. SQL Server 7.0 eliminates this limitation, and a character field can now occupy up to 8000 bytes. No single record can span pages, so the page size determines the upper limit. When a column has the char data type, you must surround the data with single quotes, as you see in this example:
UPDATE authors
SET au_lname = 'Hemingway'
WHERE au_id = '213-46-8915'
As I mentioned in "Working with Character Data" (May 1999), you can choose a fixed-length character data type, char, or a variable-length data type, varchar. The fixed length works better when the data is of the same or similar length, such as the author ID (which looks like the author's Social Security number, a bad choice for an identifier). You won't save much processing overhead in most cases by using a variable-length data type. But authors' last names might be long, so using a variable-length character data type might be worthwhile. For most names, a fixed-length field means a lot of wasted space, so a variable-length data type is better. You have a trade-off between using the extra space and incurring the processing overhead to handle variable-length fields.
SQL Server 7.0 supports the Unicode character set, so you need a data type that takes advantage of the extended capability Unicode offers. If you want to use a Unicode character data type, you denote it as Nchar, or if it's of variable length, Nvarchar. When you input Unicode data, you again put the data in single quotes, with N preceding the opening quotation mark. If your author's name is defined as a Unicode data type, you write the previous UPDATE statement as
UPDATE authors
SET au_lname= N'Hemingway'
WHERE au_id = '213-46-8915'
The Unicode data type limit is 4000 characters. Each Unicode character takes two bytes of storage, so you can fit only half as many Unicode characters on an 8K page as you can regular character data.
Integers. The basic integer data type can handle data values ranging from -2,147,483,638 through 2,147,483,647. Smallint covers -32,768 to 32,767 and is often a better choice when you know that your range of data will be limited. If I were to number my CD collection, I might use a smallint column for the primary key, assuming that my collection never exceeds 32,767 CDs. I could opt for tinyint, at one byte, but it handles only values of 0 through 255—which might work if I were tracking my certificates of deposit rather than my compact discs.