Chapter 6 -- Tables

Chapter 6

In this chapter, I'll look at some in-depth implementation examples. But let's start with a basic introduction to tables. Simply put, a table is a collection of data about a specific entity (person, place, or thing) that has a discrete number of named attributes (for example, quantity or type). Tables are at the heart of Microsoft SQL Server and the relational model in general. Tables are easy to understand—they're just like the everyday lists that you make for yourself. In SQL Server, a table is often referred to as a base table to emphasize where data is stored. Calling it a base table also distinguishes the table from a view, a virtual table that's an internal query referencing one or more base tables.

Attributes of a table's data (such as color, size, quantity, order date, and supplier's name) take the form of named columns in the table. Each instance of data in a table is represented as a single entry, or row (formally called a tuple). In a true relational database, every row in a table is unique, and each row has a unique identifier called the primary key. (SQL Server, in accordance with the ANSI SQL standard, doesn't require you to make a row unique or declare a primary key. However, because both of these concepts are central to the relational model, you should always implement them.)

Most tables will have some relationship to other tables. For example, in an order-entry system, the orders table likely has a customer_number column in which it keeps track of the customer number for an order; customer_number also appears in the customer table. Assuming that customer_number is a unique identifier, or primary key, of the customer table, a foreign key relationship is established by which the orders and customer tables can subsequently be joined.

So much for the 30-second database design primer. You can find plenty of books that discuss logical database and table design, but this isn't one of them. I'll assume that you understand basic database theory and design and that you generally know what your tables will look like. The rest of this chapter discusses the internals of tables in SQL Server and implementation considerations.