SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to add indexes in all tables in a database


how to add indexes in all tables in a database

Author
Message
achtro
achtro
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 480
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
bala.a
bala.a
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 65
You need to provide more details on the table details for creating index.
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70520 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Steve Hendricks
Steve Hendricks
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 199
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?
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70520 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70520 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search