String Datatypes
The most commonly used datatypes are string datatypes. These store strings: for example, names, addresses, phone numbers, and zip codes. There are basically two types of string datatype that you can use—fixed-length strings and variable-length strings (see Table D.1).
Fixed length strings are datatypes that are defined to accept a fixed number of characters, and that number is specified when the table is created. For example, you might allow 30 characters in a first-name column or 11 characters in a social-security-number column (the exact number needed allowing for the two dashes). Fixed-length columns do not allow more than the specified number of characters. They also allocate storage space for as many characters as specified. So, if the string Ben is stored in a 30-character first-name field, a full 30 characters are stored (and the text is padded with spaces or nulls as needed).
Variable-length strings store text of any length (the maximum varies by datatype and DBMS). Some variable-length datatypes have a fixed-length minimum. Others are entirely variable. Either way, only the data specified is saved (and no extra data is stored).
If variable-length datatypes are so flexible, why would you ever want to used fixed-length datatypes? The answer is performance. DBMSs can sort and manipulate fixed-length columns far more quickly than they can sort variable-length columns. In addition, many DBMSs will not allow you to index variable-length columns. This also dramatically impacts performance. (See Lesson 20, "Using Advanced SQL Features," for more information on indexes.)
| Datatype | Description |
|---|---|
| CHAR | Fixed length string from 1 to 255 chars long. Its size must be specified at create time. |
| NCHAR | Special form of CHAR designed to support multibyte or Unicode characters. (The exact specifications vary dramatically from one implementation to the next.) |
| NVARCHAR | Special form of TEXT designed to support multibyte or Unicode characters. (Exact specifications vary dramatically from one implementation to the next.) |
| TEXT (also called LONG or MEMO or VARCHAR) | Variable-length text. |
Tip
Using Quotes Regardless of the form of string datatype being used, string values must always be surrounded by single quotes.
Caution
When Numeric Values Are Not Numeric Values You might think that phone numbers and zip codes should be stored in numeric fields (after all, they only store numeric data), but doing so would not be advisable. If you store the zip code 01234 in a numeric field, the number 1234 would be saved. You'd actually lose a digit.
The basic rule to follow is: If the number is a number used in calculations (sums, averages, and so on), it belongs in a numeric datatype column. If it is used as a literal string (that happens to contain only digits), it belongs in a string datatype column.