THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

www.SQLServerBible.com

CodeGen to Create Indexes for FKs

/* 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

 

Published Thursday, February 08, 2007 8:02 PM by Paul Nielsen

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Curious said:

How are you assuring, in function "FKCol", that the order of the concatenation is in the order of the postion of columns in the index definition?.

I do not see an "order by" clause in the statement.

March 28, 2007 3:15 PM
 

Curious said:

I should have asked if it really matters to create the index in the same order of the position of columns in the foreign key constraint definition?

Thanks in advance.

March 28, 2007 3:50 PM
 

Curious said:

The stimated execution plans from this script show that the position of the columns in the index (based on the order of the columns in the foreign key constraint) could produce different execution plans. SQL Server is using a "sort" operator for the first twn statements. when the index is created using the same order as the one in the foreign key constraint, then it stops sorting the output from the nonclustered index scan.

create table dbo.t1 (

c1 int not null,

c2 int not null,

constraint pk_t1 primary key clustered (c1, c2)

)

go

create table dbo.t2 (

pk int not null identity primary key,

c1 int not null,

c2 int not null,

c3 char(128) default (replicate('a', 128)),

constraint fk_t2_t1 foreign key (c1, c2) references dbo.t1(c1, c2)

)

go

set nocount on

declare @i int, @j int

set @i = 1

while @i < 100

begin

set @j = 1

while @j < 100

begin

insert into dbo.t1 values(@i, @j)

if @j % 10 = 0

insert into dbo.t2(c1, c2) values(@i, @j)

set @j = @j + 1

end

set @i = @i + 1

end

set nocount off

go

set showplan_text on

go

dbcc freeproccache

dbcc dropcleanbuffers

go

select

a.*

from

dbo.t1 as a

left join

dbo.t2 as b

on a.c1 = b.c1 and a.c2 = b.c2

where

b.c1 is null and b.c2 is null

go

set showplan_text off

go

create nonclustered index t2_nu_nc_c2_c1_ix

on dbo.t2(c2, c1)

go

set showplan_text on

go

dbcc freeproccache

dbcc dropcleanbuffers

go

select

a.*

from

dbo.t1 as a

left join

dbo.t2 as b

on a.c1 = b.c1 and a.c2 = b.c2

where

b.c1 is null and b.c2 is null

go

set showplan_text off

go

drop index dbo.t2.t2_nu_nc_c2_c1_ix

go

create nonclustered index t2_nu_nc_c1_c2_ix

on dbo.t2(c1, c2)

go

set showplan_text on

go

dbcc freeproccache

dbcc dropcleanbuffers

go

select

a.*

from

dbo.t1 as a

left join

dbo.t2 as b

on a.c1 = b.c1 and a.c2 = b.c2

where

b.c1 is null and b.c2 is null

go

set showplan_text off

go

drop table dbo.t2

go

drop table dbo.t1

go

March 28, 2007 4:07 PM
 

alex hatcher said:

Comment out the EXEC (@SQL)  in the code.

you're going to get someone that runs it and forgets to realize that it makes the change.

February 2, 2009 12:22 PM

Leave a Comment

(required) 
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement