Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Поиск дублирующихся индексов  [new]
zenk
Member

Откуда: Москва
Сообщений: 115
Добрый день!

Разработчики, не зная технической реализации ограничения уникальности, и, в частности, первичных ключей,
часто создают индексы по уникальным полям и первичным ключам, когда думают, что считают,
что по ним будет осуществляться поиск или связывание таблиц.

Написал простой скрипт для поиска таких индексов.
SELECT Object_name(a.object_id), a.name, b.name
FROM sys.indexes a, sys.indexes b 
WHERE a.object_id = b.object_id
  AND a.type = b.type
  AND a.type <> 0
  AND b.type <> 0
  AND a.type_desc = b.type_desc
  AND a.is_unique = b.is_unique
  AND a.data_space_id = b.data_space_id
  AND a.ignore_dup_key = b.ignore_dup_key
  -- поля is_primary_key и is_unique_constraint не анализируются
  -- так как ищем индексы независимо от того, были ли они созданы явно или неявно
  AND a.fill_factor = b.fill_factor
  AND a.is_padded = b.is_padded
  AND a.is_disabled = b.is_disabled
  AND a.is_hypothetical = b.is_hypothetical
  AND a.allow_row_locks = b.allow_row_locks
  AND a.allow_page_locks = b.allow_page_locks
  AND a.index_id <> b.index_id
  AND NOT EXISTS (SELECT object_id, index_column_id, column_id, key_ordinal, partition_ordinal, is_descending_key, is_included_column
                  FROM sys.index_columns
                  WHERE sys.index_columns.object_id = a.object_id
                    AND sys.index_columns.index_id = a.index_id
            EXCEPT
                  SELECT object_id, index_column_id, column_id, key_ordinal, partition_ordinal, is_descending_key, is_included_column
                  FROM sys.index_columns
                  WHERE sys.index_columns.object_id = b.object_id
                    AND sys.index_columns.index_id = b.index_id)
  AND NOT EXISTS (SELECT object_id, index_column_id, column_id, key_ordinal, partition_ordinal, is_descending_key, is_included_column
                  FROM sys.index_columns
                  WHERE sys.index_columns.object_id = b.object_id
                    AND sys.index_columns.index_id = b.index_id
            EXCEPT
                  SELECT object_id, index_column_id, column_id, key_ordinal, partition_ordinal, is_descending_key, is_included_column
                  FROM sys.index_columns
                  WHERE sys.index_columns.object_id = a.object_id
                    AND sys.index_columns.index_id = a.index_id)


Всё ли я учёл? Нельзя ли как-нибудь отказаться от зеркальной конструкции из двух NOT EXISTS?
28 май 12, 17:41    [12627518]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублирующихся индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
zenk
Всё ли я учёл?
Фильтрованные индексы. Они могут быть по одинаковым полям но с разными условиями фильтрации. Данный скрипт такие индексы считает одинаковыми.
28 май 12, 18:00    [12627673]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублирующихся индексов  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
zenk
Нельзя ли как-нибудь отказаться от зеркальной конструкции из двух NOT EXISTS?


вместо них можно написать один
...
AND NOT EXISTS (
  SELECT * FROM (
    SELECT <поля> FROM sys.index_columns colA WHERE ...
    UNION ALL
    SELECT <поля> FROM sys.index_columns colB WHERE ...
  ) t3
  GROUP BY <поля>
  HAVING COUNT(*) = 1)
28 май 12, 19:24    [12628063]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублирующихся индексов  [new]
zenk
Member

Откуда: Москва
Сообщений: 115
Гавриленко Сергей Алексеевич
zenk
Всё ли я учёл?
Фильтрованные индексы. Они могут быть по одинаковым полям но с разными условиями фильтрации. Данный скрипт такие индексы считает одинаковыми.


Спасибо! Есть такой момент... Хотя наши разработчики о них не знают и не используют.
29 май 12, 10:11    [12629809]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублирующихся индексов  [new]
zenk
Member

Откуда: Москва
Сообщений: 115
Shakill
zenk
Нельзя ли как-нибудь отказаться от зеркальной конструкции из двух NOT EXISTS?


вместо них можно написать один
...
AND NOT EXISTS (
  SELECT * FROM (
    SELECT <поля> FROM sys.index_columns colA WHERE ...
    UNION ALL
    SELECT <поля> FROM sys.index_columns colB WHERE ...
  ) t3
  GROUP BY <поля>
  HAVING COUNT(*) = 1)


Спасибо!
29 май 12, 11:07    [12630202]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублирующихся индексов  [new]
zenk
Member

Откуда: Москва
Сообщений: 115
Интересно. а в последовательности index_column_id могут быть пропуски?
В BOL гарантируется только уникальность.

У меня этот запрос везде возвращает пустой набор:
SELECT object_id, index_id, Max(index_column_id), Count(*)
FROM sys.index_columns
GROUP BY object_id, index_id
HAVING Max(index_column_id) <> Count(*)
29 май 12, 11:26    [12630339]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить