In Microsoft SQL Server 2005, the structures of tables and indexes are just as important as the database itself, especially when it comes to performance. Tables are collections of data about a specific entity, such as a customer or an order. To describe the attributes of these entities, you use named columns. For example, to describe the attributes of a customer, you could use these columns: cust_name, cust_address, and cust_phone.

Each instance of data in a table is represented as a single data entry or row. Typically, rows are unique and have unique identifiers called primary keys associated with them. However, a primary key is not mandatory in ANSI SQL, and it is not required in SQL Server. The job of the primary key is to set a unique identifier for each row in the table and to allow SQL Server to create a unique index on this key. Indexes are user-defined data structures that provide fast access to data when you search on an indexed column. Indexes are separate from tables, and you can configure them automatically with the Database Tuning Advisor.

Most tables are related to other tables. For example, a Customers table may have a cust_account column that contains a customer’s account number. The cust_account column may also appear in tables named Orders and Receivables. If the cust_account column is the primary key of the Customers table, a foreign key relationship can be established between Customers and Orders as well as between Customers and Receivables. The foreign key creates a link between the tables that you can use to preserve referential integrity in the database.

After you have established the link, you will not be able to delete a row in the Customers table if the cust_account identifier is referenced in the Orders or Receivables tables. This feature prevents you from invalidating references to information used in other tables. You would first need to delete or change the related references in the Orders or Receivables tables, or both, before deleting a primary key row in the Customers table. Foreign key relationships allow you to combine data from related tables in queries by matching the foreign key constraint of one table with the primary or unique key in another table. Combining tables in this manner is called a table join, and the keys allow SQL Server to optimize the query and quickly find related data.