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.
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!