Geeks With Blogs

News My Blog has been MOVED to https://mfreidge.wordpress.com
Michael Freidgeim's OLD Blog My Blog has been MOVED to https://mfreidge.wordpress.com

http://stackoverflow.com/questions/278982/are-foreign-keys-indexed-automatically-in-sql-server

I’ve used SQL script similar to paul_nielsen’s to Create Indexes for Foreign Keys and added “if not exists” condition
DECLARE @SQL VARCHAR(max); SET @SQL = ''

SELECT @SQL = @SQL +
'if not exists (select * from sys.indexes
 where id=object_id(''' + TableName +''') and name=''Ix' + ForeignKeyName+''')
 CREATE INDEX Ix' + ForeignKeyName
    + ' ON ' + TableName + '(' + ColumnName + ');
    '
 FROM
…....


--SELECT @SQL
print @SQL
GO


At the end I would recommend to print @SQL to show all new lines correctl in messages ta of SSMS,
rather than SELECT @SQL or execute SQL

 

Another script can be found here(login is required): http://www.sqlservercentral.com/scripts/Index+Management/62069/

From http://msdn.microsoft.com/en-us/library/ms175464.aspx
Indexing FOREIGN KEY Constrains

Creating an index on a foreign key is often useful for the following reasons:
  • Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.
  • Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the FOREIGN KEY constraint of one table with the primary or unique key column or columns in the other table. An index enables the Database Engine to quickly find related data in the foreign key table.
Posted on Sunday, June 5, 2011 8:42 AM SQL Server | Back to top


Comments on this post: SQL Script to create indexes for Foreign keys

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Michael Freidgeim | Powered by: GeeksWithBlogs.net