Logical vs Physical - Part 2 ' The Physical'

Now that we understand better about Primary Keys, Foreign Keys and declarative referential integrity (DRI). We can look at how this is physically implemented by the database engine. Relational database vendors can enforce or implement these structures in a number of different ways.

SQL Server implements the PK by creating a unique b-tree index, not allowing NULLs.

By default this is a clustered index, although you can specify a non clustered index.

The Foreign Key is just created as a constraint, with no indexes.

This is where you need to look at the physical database design to see if your FK's need to be indexed for better performance.

This leads us to a final discussion about heap tables.

Heap tables are tables with an index but no clustered index or a table with no indexes at all.

My rule of thumb is, for tables using more than one extent (64kb). Or you could say tables with greater than 100 rows.

Tables with an existing index, but no clustered index should be changed to have a clustered index.

Tables with no indexes at all, should have a clustered index added.