Jun
16
2016

Database design matters, seriously

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 

01_Query_Plan

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!

 

5 comments
  1. Ken says:

    SELECT * FROM Person.Address WHERE StateProvinceID is not null
    would work just as well, because if it isn’t null it must be in the other table and if it is null than it can’t be connected to the other table. That’s assuming the field isn’t defined as not null. In that case, you don’t need the where clause at all. It is more important that the continuity between tables that is needed is automatically maintained than the efficiency of query plans.

    • Raul says:

      Thanks for your comment!, As you well point, referential integrity is the key point to create a foreign key, but doing things the right way can also bring us some nice additions like in the examples.
      Cheers

  2. Kevin Boles says:

    Now why don’t you show the opposite side of the coin, where every DML affecting those fields will be forced to do extra work to ensure the referential integrity is maintained. On many systems I have worked on that cost is much larger than the savings that can come from having FKs in place.

    Now, I note that effort does not say anything about keeping bad data out of the system. That is something that has completely different calculus associated with it. 😀

Leave a Reply

Your email address will not be published. Required fields are marked *