You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
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
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.
The text was updated successfully, but these errors were encountered: