Database design matters, seriously

All those rules and best practices about database design exist for a reason. Today I want to show you something very simple for you to see how a good design might improve the performance of certain queries. This might sound pretty obvious, but really, database design matters.

And all those rules and best practices about database design exist for a reason. Today I want to show you something very simple for you to see how a good design might improve the performance of certain queries.

The idea for this post is originally taken out of my coworker Shane’s (b|t) latest post Primary Foreign Key.

DBA’s sometimes have to deal with funny requests like don’t create that foreign key because developers will handle that in the code… 🙂

By definition, Foreign key constraints will enforce the existence of a value in a table prior to be inserted into another table which references it.

That can be done programmatically in TSQL or any other language, but it is important to understand that for the SQL engine, knowing that information in the form of a foreign key constraint can help it to take decisions beforehand and avoid some extra work.

But, let’s stop the talking and go to the point.

USE AdventureWorks2014
GO

SELECT * INTO Person.Address_noFK FROM Person.Address
GO

I’ve just created a copy of [Person].[Address] with no Foreign Keys as opposed to the original which contains one, [StateProvinceID] which references the table [Person].[StateProvince]

If we run a simple query against both, the original and the one without Foreign Key, we can see how they behave completely different.

SELECT * 
FROM Person.Address
WHERE StateProvinceID IN (SELECT [StateProvinceID] FROM [Person].[StateProvince])
GO

SELECT * 
FROM Person.Address_noFK
WHERE StateProvinceID IN (SELECT [StateProvinceID] FROM [Person].[StateProvince])
GO 

So when SQL Server knows (thanks to the Foreign Key) that all values from [Person].[Address].[StateProvinceID] must exist in [Person].[StateProvince], there is no need to do the extra work like in the second query.

In my opinion DBA’s should always enforce Foreign key relationships at the lower level using constraints for all described above and more.

And that’s all for this post, hope you enjoyed reading and do not trust anyone who asks you not to create that FK!