InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 04:47:13 GMT20http://www.sqlservercentral.com/Forums/Topic1366007-392-1.aspxhere's the script I mentioned that blindly builds the SQL statements to create indexes on any foreign keys that do not have indexes on them yet;any peer review would be appreciated.[code]--select * from sys.index_columnsWITH MyIndexes AS (SELECT DISTINCT idxz.name AS IndexName, idxz.object_id, OBJECT_NAME(idxz.object_id) AS TableName, ixcolnamez.name AS IndexColumnName FROM sys.indexes idxz INNER JOIN sys.index_columns idxcolz ON idxz.index_id = idxcolz.index_id INNER JOIN sys.columns ixcolnamez ON idxcolz.object_id = ixcolnamez.object_id AND idxz.object_id = ixcolnamez.object_id AND idxcolz.column_id = ixcolnamez.column_id WHERE index_column_id = 1 --because only the lead column will help for the join )SELECT SCHEMA_NAME(chldz.schema_id) AS SchemaName, objz.name AS FKName, parenz.name AS ParentTable, pcolz.name AS ParentColumn, chldz.name AS ChildTable, ccolz.name AS ChildColumn, MyIndexes.*, CASE WHEN MyIndexes.object_id IS NULL THEN 'CREATE INDEX [IX_FK_' + chldz.name + '_' + ccolz.name + '] ON ' + QUOTENAME(SCHEMA_NAME(chldz.schema_id)) + '.' + QUOTENAME(chldz.name) + '(' + QUOTENAME(ccolz.name) + ');' ELSE '' END AS PotentialIndexFROM sys.sysforeignkeys fkeyz INNER JOIN sys.objects objz ON fkeyz.constid = objz.object_id INNER JOIN sys.objects parenz ON fkeyz.rkeyid = parenz.object_id INNER JOIN sys.columns pcolz ON parenz.object_id = pcolz.object_id AND fkeyz.rkey = pcolz.column_id INNER JOIN sys.objects chldz ON fkeyz.fkeyid = chldz.object_id INNER JOIN sys.columns ccolz ON chldz.object_id = ccolz.object_id AND fkeyz.fkey = ccolz.column_id LEFT OUTER JOIN MyIndexes ON chldz.object_id = MyIndexes.object_id AND ccolz.name = MyIndexes.IndexColumnName [/code]Mon, 01 Oct 2012 07:49:05 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1366007-392-1.aspx[quote][b]Steve Hendricks (9/28/2012)[/b][hr]In re: "it's the selectivity of the data that is what the SQL engine uses to determine whether to use an index or not."For SELECTs that would be true but wouldn't the index on the foreign key be used when validating data consistency during INSERTs and UPDATEs?[/quote]stating the obvious, a foreign key is used to validate data consistency regardless of an index on it or not.an index, if selective enough, might be used by the engine to find the data that needs to be selected/inserted/updated/deleted; if it's not selective enough, or doesn't cover enough columns the engine needs to search on, it might be ignored and a different index or a table scan used instead.so if you were updating a table that has a FK, for example [b]WHERE STATEID= 12 [/b], and there were hundreds of rows with that value, the engine will evaluate the statistics and selectivity to determine which index might be beneficial to the UPDATE statement; lots of rows to be affected , in this example, and you'd probably see a table scan;in a different query, where you were updating, say the contacts related to a company, the query might be very selective on the FK:ie [b]WHERE COMPANYID = 4[/b], and there was only two contacts out of hundreds, that index might be used to find and lock just the rows affected.Sat, 29 Sep 2012 06:14:12 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1366007-392-1.aspxIn re: "it's the selectivity of the data that is what the SQL engine uses to determine whether to use an index or not."For SELECTs that would be true but wouldn't the index on the foreign key be used when validating data consistency during INSERTs and UPDATEs?Fri, 28 Sep 2012 16:56:37 GMTSteve Hendrickshttp://www.sqlservercentral.com/Forums/Topic1366007-392-1.aspxblindly adding an index on all the columns that foreign keys are involved with might not help much;it's the selectivity of the data that is what the SQL engine uses to determine whether to use an index or not.so for example, if you have a column in a table that is foreign keyed to toa STATUS table that has three values(ie, open, closed,cancelled) , the index is not very selective; your data would probably have 90% of the data in two of the values.indexes on other items might actually help.The second post I ever did on SSC asked that exact question:[b][url]http://www.sqlservercentral.com/Forums/Topic11946-8-1.aspx[/url][/b], and there's a bit of discussion on it.if you really insist you need a script that blindly builds the indexes on foreign keys if they do not exist yet, I'll post it as a follow up.Fri, 28 Sep 2012 11:26:18 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1366007-392-1.aspxYou need to provide more details on the table details for creating index.Fri, 28 Sep 2012 11:05:07 GMTbala.ahttp://www.sqlservercentral.com/Forums/Topic1366007-392-1.aspxhi i want to create foreign key indexes on all the base tables in a database ,i need to check in every table that if there is already a index created on a foreign key column i don't want to create index on that column, is there any sql script that anybody can provide me with .thanks in advanceFri, 28 Sep 2012 10:32:34 GMTachtro