GilaMonster (11/27/2012)
PiMané (11/27/2012)
Hi,If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?
Yes.
But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!
Is it worth having both indexes?
Maybe.
Hi,
After reading your blog about indexes http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/ I made a query to get "duplicate" indexes considering that the first two columns are important and the other can be "joined", for example an index col1, col2, col3 and col1, col2, col5 can be "joined" in a col1, col2, col3, col5... I'm creating wider indexes and removing small ones.
I'm still not considering the include columns but joining two "duplicate" indexes include columns is the next step..
DECLARE @FirstColumnsKeys INT = 2
;WITH IndexInfo AS (
SELECT
SCHEMA_NAME(t.schema_id) SchemaName,
t.name TableName,
i.name IndexName,
(SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 0 AND ic.index_column_id <= @FirstColumnsKeys ORDER BY ic.index_column_id FOR XML PATH('')) FirstKeyColumns,
(SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 0 AND ic.index_column_id > @FirstColumnsKeys ORDER BY ic.column_id FOR XML PATH('')) OtherKeyColumns,
(SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 1 ORDER BY ic.column_id FOR XML PATH('')) IncludeColumns
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id AND t.is_ms_shipped = 0
WHERE i.type != 0)
SELECT i1.* FROM
IndexInfo i1 INNER JOIN IndexInfo i2 ON i1.SchemaName = i2.SchemaName AND i1.TableName = i2.TableName AND i1.IndexName <> i2.IndexName AND i1.FirstKeyColumns = i2.FirstKeyColumns
ORDER BY i1.SchemaName, i1.TableName, i1.FirstKeyColumns, i1.OtherKeyColumns, i1.IndexName
Still working on the query but this is as it is so far...
Is this assumption, that the first two columns are what really matters and the other can be joined, valid for foreign keys without indexes? If I have a FK on col2, col3 and col4 can an index with col2, col3, col5, col4 be considered "good" for FK validation?
Thanks,
Pedro