Constraints - Pro SQL Server Internals - page 149

Listing 7-1. Orders table

create table dbo.Orders
(
OrderId int not null identity(1,1),
-- other columns
constraint PK_Orders
primary key clustered(OrderId)
)

The OrderLineItems table could have two key columns: OrderId , which references the row from the Orders table
and OrderLineItemId identity column. In most cases, we will work with OrderLineItems , which belong to a specific
Order , and natural candidates for the clustered index in this table would be (OrderId, OrderLineItemId) . It would
be logically incorrect, however, to define that clustered index as the primary key—the row can be uniquely identified
by the single OrderLineItemId identity column, and we do not need OrderId for this purpose.
The question of whether we want to define non-clustered primary key on OrderLineItemId depends on the other
factors. From the logical design standpoint, it would be the right thing to do, especially if the table is referenced by
the other tables with foreign key constraints, which we will discuss later in this chapter. This would introduce another
non-clustered index, however, which we need to store and maintain. The final implementation might be similar to the
code shown in Listing 7-2.

Listing 7-2. OrderLineItems table
create table dbo.OrderLineItems
(
OrderId int not null,
OrderLineItemId int not null identity(1,1),
-- other columns
constraint PK_OrderLineItems
primary key nonclustered(OrderLineItemId)
);

create unique clustered index IDX_OrderLineItems_OrderId_OrderLineItemId
on dbo.OrderLineItems(OrderId,OrderLineItemId)

While primary keys can be represented as the unique indexes from the physical implementation standpoint,
there is the minor difference between them. All primary key columns cannot be nullable. On the other hand, unique
indexes can be created on the nullable columns and would treat null as the regular value.
One very important thing to remember is that we cannot change the definition of the primary key or, in fact,
change the definition of any constraint without dropping and recreating it. As a result, if primary key constraint is
clustered, it will lead to two table rebuilds. Dropping the constraint would remove the clustered index and convert the
table to a heap table. Adding a clustered primary key creates a clustered index on the heap table. The table would be
locked for the duration of both operations regardless of the edition of SQL Server. Alternatively, the definition of the
clustered indexes could be changed online without locking the table in SQL Server Enterprise Edition.

■ Disable non-clustered indexes in case you need to drop and recreate a clustered primary key constraint. enable
(rebuild) them after both operations are done. this will speed up the process because non-clustered indexes would be
rebuilt only once after the operation is completed rather than during each step.

Tip