Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Missing Foreign Key Indexes missing multicolumn indexes #24

Open
CharlMeyers opened this issue Feb 10, 2023 · 0 comments
Open

Missing Foreign Key Indexes missing multicolumn indexes #24

CharlMeyers opened this issue Feb 10, 2023 · 0 comments

Comments

@CharlMeyers
Copy link

I have some foreign keys that I put together in a multicolumn index to ensure that all foreign keys are indexed. Running the following query shows me I don't have any missing foreign key indexes: source for the query

CREATE TABLE #TempForeignKeys (TableName varchar(100), ForeignKeyName varchar(100) , ObjectID int)
INSERT INTO #TempForeignKeys 
SELECT OBJ.NAME, ForKey.NAME, ForKey .[object_id] 
FROM sys.foreign_keys ForKey
INNER JOIN sys.objects OBJ
ON OBJ.[object_id] = ForKey.[parent_object_id]
WHERE OBJ.is_ms_shipped = 0
 
CREATE TABLE #TempIndexedFK (ObjectID int)
INSERT INTO #TempIndexedFK  
SELECT ObjectID      
FROM sys.foreign_key_columns ForKeyCol
JOIN sys.index_columns IDXCol
ON ForKeyCol.parent_object_id = IDXCol.[object_id]
JOIN #TempForeignKeys FK
ON  ForKeyCol.constraint_object_id = FK.ObjectID
WHERE ForKeyCol.parent_column_id = IDXCol.column_id 
 
SELECT * FROM #TempForeignKeys WHERE ObjectID NOT IN (SELECT ObjectID FROM #TempIndexedFK)
 
 
DROP TABLE #TempForeignKeys
DROP TABLE #TempIndexedFK

I tend to keep columns in a multicolumn index that I know will always be used together when joining on to my table. The rest of the foreign keys I suspect will be used separately I give them their own index.

Is it by design that the Missing Foreign Key Indexes test ignores multicolumn indexes, and if so, what is the reason behind it?

To reproduce, create a table with 2 foreign keys and add those foreign keys to one multicolumn index.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant