• Adam Thurgar

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.


3 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t