Qual è il rischio in cui si può incorrere quando un progetto viene sviluppato da più persone, ovvero quando un database viene modellato da più mani?
Creare un indice che esiste già! 🙁
SQL Server non fa nulla per venirci in aiuto o, almeno, non fa nulla in automatico.
Con questo script è possibile ottenere una lista degli indici identici / duplicati.
;WITH tIndex AS
(
SELECT
object_id AS id ,
index_id AS indid ,
type,
name ,
(
SELECT
colid as colPosition
FROM sys.sysindexkeys
WHERE id = I.object_id AND indid = I.index_id
AND keyno > 0
FOR XML PATH
) AS cols ,
(
SELECT includedCol FROM
(
SELECT
CASE keyno WHEN 0 THEN colid ELSE NULL END AS includedCol
FROM sys.sysindexkeys
WHERE id = I.object_id AND indid = I.index_id
) T0
order by includedCol
FOR XML PATH
) AS inc
FROM sys.indexes AS I
)
SELECT
object_schema_name ( T1.id ) + ‘.’ + object_name ( T1.id ) as tableName,
T1.name AS indexName,
T2.name AS duplicateIndex,
S.used_page_count * 8 indexSizeKB
FROM tIndex AS T1
JOIN tIndex AS T2 ON
T1.type = T2.type AND
T1.id = T2.id AND
T1.indid < T2.indid AND
T1.cols = T2.cols AND
T1.inc = T2.inc
JOIN sys.dm_db_partition_stats AS S ON
S.[object_id] = T2.id AND S.index_id = T2.indId
ORDER BY object_schema_name ( T1.id ) + ‘.’ + object_name ( T1.id ), T1.name
go