Datasheet

8
Chapter 1: Introducing T-SQL and Data Management Systems
Table Columns
As previously described, a table is a set of rows and columns used to represent an entity. Each
row represents an instance of the entity. Each column in the row will contain at most one value that
represents an attribute, or property, of the entity. For example, consider the employee table; each
row represents a single instance of the employee entity. Each employee can have one and only one first
name, last name, SSN, extension, or hire date, according to your design specifications. In addition to
deciding which attributes you want to maintain, you must also decide how to store those attributes.
When you define columns for your tables, you must, at a minimum, define three things:
The name of the column
The data type of the column
Whether the column can support null
Column Names
Keep the names simple and intuitive (such as LastName or EmployeeID) instead of more cumbersome
names (such as EmployeeLastName and EmployeeIdentificationNumber). For more information,
see Chapter 8 .
Data Types
The general rule on data types is to use the smallest one you can. This conserves memory usage and disk
space. Also keep in mind that SQL Server processes numbers much more efficiently than characters, so
use numbers whenever practical. I have heard the argument that numbers should be used only if you
plan on performing mathematical operations on the columns that contain them, but that just doesn ’ t
wash. Numbers are preferred over string data for sorting and comparison as well as mathematical
computations. The exception to this rule is if the string of numbers you want to use starts with a zero.
Take the Social Security number, for example. Other than the unfortunate fact that some Social Security
numbers begin with a zero, the Social Security number would be a perfect candidate for using an integer
instead of a character string. However, if you tried to store the integer 012345678, you would end up
with 12345678. These two values may be numeric equivalents, but the government doesn ’ t see it that
way. They are strings of numerical characters and therefore must be stored as characters rather than as
numbers.
When designing tables and choosing a data type for each column, try to be conservative and use the
smallest, most efficient type possible. But at the same time, carefully consider the exception, however
rare, and make sure that the chosen type will always meet these requirements.
The data types available for columns in SQL Server 2005 and 2008 are specified in the following table.
Those that are unique to SQL Server 2008 are prefixed with an asterisk (*).
❑
❑
❑
CH001.indd 8CH001.indd 8 3/26/10 11:35:36 AM3/26/10 11:35:36 AM