September 28, 2012 at 10:32 am
hi 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 advance
September 28, 2012 at 11:05 am
You need to provide more details on the table details for creating index.
September 28, 2012 at 11:26 am
blindly 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:
http://www.sqlservercentral.com/Forums/Topic11946-8-1.aspx, 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.
Lowell
September 28, 2012 at 4:56 pm
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?
September 29, 2012 at 6:14 am
Steve Hendricks (9/28/2012)
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?
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 WHERE STATEID= 12 , 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 WHERE COMPANYID = 4, and there was only two contacts out of hundreds, that index might be used to find and lock just the rows affected.
Lowell
October 1, 2012 at 7:49 am
here'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.
--select * from sys.index_columns
WITH 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 PotentialIndex
FROM
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
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply