Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to add indexes in all tables in a database Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 10:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 7:26 AM
Points: 16, 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
Post #1366007
Posted Friday, September 28, 2012 11:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 5, 2013 6:15 AM
Points: 15, Visits: 65
You need to provide more details on the table details for creating index.

Post #1366026
Posted Friday, September 28, 2012 11:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1366038
Posted Friday, September 28, 2012 4:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:51 PM
Points: 446, Visits: 97
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?

Post #1366135
Posted Saturday, September 29, 2012 6:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1366181
Posted Monday, October 1, 2012 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1366569
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse