Datasheet

6
Chapter 1: Introducing T-SQL and Data Management Systems
The data in the table would look something like that shown in Figure 1 - 2 .
Figure 1-1
Figure 1-2
Primary Keys
To manage the data in your table efficiently, you need to be able to uniquely identify each individual row
in the table. It is much more difficult to retrieve, update, or delete a single row if there is not a single
attribute that identifies each row individually. In many cases, this identifier is not a descriptive attribute
of the entity. For example, the logical choice to uniquely identify your employee is the Social Security
number attribute. However, there are a couple of reasons why you would not want to use the Social
Security number as the primary mechanism for identifying each instance of an employee, both boiling
down to two different areas: security and efficiency.
When it comes to security, what you want to avoid is the necessity of securing the employee ’ s Social
Security number in multiple tables. Because you will most likely be using the key column in multiple
tables to form your relationships (more on that in a moment), it makes sense to substitute a non -
descriptive key. In this way you avoid the issue of duplicating private or sensitive data in multiple
locations to provide the mechanism to form relationships between tables.
As far as efficiency is concerned, you can often substitute a non - data key that has a more efficient or
smaller data type associated with it. For example, in your design you might have created the Social
Security number with either a character data type or an integer. If you have fewer than 32,767
employees, you can use a double - byte integer instead of a 4 - byte integer or 10 - byte character type;
besides, integers process faster than characters.
So, instead of using the Social Security number, you will assign a non - descriptive key to each row. The
key value used to uniquely identify individual rows in a table is called a primary key . (You will still
want to ensure that every Social Security number in your table is unique and not null, but you will use a
different method to guarantee this behavior without making it a primary key.)
A non - descriptive key doesn ’ t represent anything else with the exception of being a value that uniquely
identifies each row or individual instance of the entity in a table. This will simplify the joining of this
table to other tables and provide the basis for a “ relation. ” In this example you will simply alter the table
by adding an EmployeeKey column that will uniquely identify every row in the table, as shown in
Figure 1 - 3 .
CH001.indd 6CH001.indd 6 3/26/10 11:35:35 AM3/26/10 11:35:35 AM