Datasheet
Column Names
Keep the names simple and intuitive. For more information see Chapter 11.
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 only be used 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 com-
putations. 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 num-
bers (like my daughter’s) 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 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 2000 and 2005 are specified in the following table.
Data Type Storage Description
Bigint 8 bytes An 8-byte signed integer. Valid values are
-9223372036854775808 through
+9223372036854775807.
Int 4 bytes A 4-byte signed integer. Valid values are
-2,147,483,648 through +2,147,483,647.
SmallInt 2 bytes A double-byte signed integer. Valid values are
-32,768 through +32,767.
TinyInt 1 byte A single-byte unsigned integer. Valid values are
from 0 through 255.
Bit 1 bit Integer data with either a 1 or 0 value.
8
Chapter 1
04_57955x ch01.qxd 9/19/05 12:43 PM Page 8