SQL Server – People Forgetting to Add Indexes to Foreign Keys
I had an interesting conversation with a colleague today who insisted that when you create a foreign key in Microsoft SQL Server, it automatically adds an index on the foreign key. Guess what — it doesn’t!! I think this is one of the most common mistakes made in SQL Server. I’ve seen wonderfully normalized databases where the developer did not create any indexes on the foreign keys. With today’s fast hardware speeds, this problem is often masked because when you’re working with a new database, everything usually runs smoothly anyway. It is after time that the problem manifests itself, as that database grinds down under the pressure of doing table scans in order to achieve joins.
So I did some searching online and found a great script on Paul Nielson’s blog that will generate missing foreign key indexes for you. Thanks Paul!
Now my recommendation would be to run this script, but hold off on actually executing it until you review it, make sure you haven’t already created indexes, double-check everything, etc. Remember that you can DESCREASE performance sometimes with too many indexes. But usually, this will improve your performance
Here is Paul’s script:
/* This script creates a (composite) nonclustered index for every Foreign Key without a complete matching (composite) index FK Index script Paul Nielsen www.sqlserverbible.com ---------------------------------- version 1.00 - Feb 8, 2007 */ CREATE -- alter Function FKCol (@Object_ID INT) RETURNS VARCHAR(8000) as Begin DECLARE @SQL VARCHAR(max) SET @SQL = '' select @SQL = @SQL + pc.name + ',' from sys.foreign_key_columns fk join sys.columns pc on fk.parent_object_id = pc.object_id and fk.parent_column_id = pc.column_id where fk.constraint_object_id = @Object_ID; set @SQL = left(@SQL, len(@SQL)-1); Return @SQL End -- Dynamic SQL DECLARE @SQL VARCHAR(max); SET @SQL = '' SELECT @SQL = @SQL + ' CREATE INDEX Ix' + FK_Name + ' ON ' + FK_Table + '(' + FK_Columns + ');' FROM ( -- FK w/o complete (composite) index select distinct fko.name as FK_Name, fks.name + '.' + fkt.name as FK_table, dbo.FKCol(fk.constraint_object_id) as FK_Columns from sys.foreign_key_columns fk join sys.objects fkt on fk.parent_object_id = fkt.object_id join sys.schemas as fks on fks.schema_id = fkt.schema_id join sys.objects fko on fk.constraint_object_id = fko.object_id left join sys.index_columns ic on ic.object_id = fk.parent_object_id -- same table and ic.column_id = fk.parent_column_id -- same column and ic.index_column_id = fk.constraint_column_id -- column position in the index and FK where ic.object_id IS NULL) sq SELECT @SQL EXEC (@SQL) -- check for updates at www.SQLServerBible.com
Follow comments to this post through RSS 2.0.
Both comments and pings are currently closed.
Comments are closed.