Уникальный ключ против уникального индекса на SQL Server 2008

У меня есть таблица под названием countries, и я определяю столбец country_name, чтобы быть уникальным, создав "Index/Key" типа "Unique Key" на SQL Server 2008 R2.

Но у меня есть следующие вопросы:

создаст "Индекс/ключ" типа "Уникальный ключ" , автоматически создаст некластеризованный индекс в этом столбце?
Если я изменил тип на "Уникальный ключ" на "Индекс", и я сохраняю значение IsUnique равным "Да", тогда будут ли какие-либо различия?
так почему есть два варианта: "Уникальный ключ" и "Индекс". Я думаю, что два одинаковые?

Ответ 1

Уникальное ограничение реализуется за кулисами как уникальный индекс, поэтому на самом деле не имеет значения, как вы его определяете. Я стараюсь реализовать его просто так:

ALTER TABLE dbo.foo ADD CONSTRAINT UQ_bar UNIQUE(bar);

Некоторые люди создают уникальный индекс, например,

CREATE UNIQUE INDEX IX_UQ_Bar ON dbo.foo(bar);

Разница заключается в намерении - если вы создаете ограничение для обеспечения уникальности/бизнес-правил, вы создаете ограничение, если вы это делаете, чтобы помочь повысить производительность запроса, было бы логичнее создать уникальный индекс. Опять же, под обложками это такая же реализация, но дорога, которую вы берете, чтобы попасть туда, может помочь документировать ваши намерения.

Я думаю, что есть несколько вариантов придерживаться как предыдущей функциональности Sybase, так и придерживаться стандарта ANSI (хотя уникальные ограничения не соответствуют стандартным 100%, поскольку они допускают только одно значение NULL - уникальное index, с другой стороны, может обойти это, добавив предложение WHERE (WHERE col IS NOT NULL) на SQL Server 2008 и выше).

Ответ 2

Еще одна вещь, которая стоит упомянуть, заключается в том, что если вы создаете индекс, вы можете указать включенные столбцы, это может помочь вашему SQL-коду работать быстрее, если есть какой-то поиск по имени страны.

CREATE UNIQUE NONCLUSTERED INDEX IX_UQ_Bar
ON dbo.foo (
    bar
)
INCLUDE (foo_other_column)
GO

SELECT foo_other_column FROM Foo WHERE bar = 'test'

SQL-сервер будет хранить "foo_other_column" в самом индексе. В случае уникального ограничения он сначала найдет индекс "test", затем будет искать строку в таблице foo, и только там он примет "foo_other_column".

Ответ 3

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

Ответ 4

Если вы используете SqlMetal.exe для вывода сущностей DBML или LinqToSql:

Если внешний ключ использует уникальный ключ, то вы получите связь, как и ожидалось.
Если внешний ключ использует уникальный индекс, он не будет отображаться.

Причина в реализации SqlMetal. Он запрашивает информационную схему базы данных, в частности, использование ключевого столбца. Здесь представлены уникальные ключи, но нет уникальных индексов.

SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

Ответ 5

Помимо превосходных ответов выше, я бы добавил сюда свои 2 цента.

Уникальный ключ является ограничением, и он использует уникальный индекс для принудительного применения. Так же, как первичный ключ обычно обеспечивается кластеризованным уникальным индексом. Логически говоря, ограничение и индекс две разные вещи. Но в СУБД ограничение может быть физически реализовано через индекс.

Если таблица создана с уникальным ограничением на сервере SQL, вы увидите и объект ограничения, и уникальный индекс

create table dbo.t (id  int constraint my_unique_constraint unique (id));

select [Constraint]=name from sys.key_constraints 
where parent_object_id = object_id('dbo.t');

select name, index_id, type_desc from sys.indexes
where object_id = object_id('dbo.t')
and index_id > 0;

мы получим следующее (ограничение и индекс)

Тем не менее, если мы не создаем ограничение, а просто уникальный индекс, как показано ниже

create table dbo.t2 (id int );
create unique index my_unique_constraint on dbo.t2 (id);

select [Constraint]=name from sys.key_constraints 
where parent_object_id = object_id('dbo.t2');

select name, index_id, type_desc from sys.indexes
where object_id = object_id('dbo.t2')
and index_id > 0

Вы увидите, что объект ограничения НЕТ не создан (создан только индекс).

С "теоретической" точки зрения в SQL Server ограничение - это объект со значением object_id и привязанный к схеме, в то время как индекс не является объектом и не имеет значения object_id и не связан со схемой.

Ответ 6

Третьим вариантом для обеспечения уникальности является использование отфильтрованного уникального индекса, чтобы разрешить обнуляемый уникальный индекс.
Это не будет работать с Unique-Constraints.
Например, скажем, у вас есть столбец, в котором вы хотите разрешить только уникальные значения,
но по-прежнему хотят поддерживать несколько значений NULL когда они не существуют.
Будет работать только фильтрованный уникальный индекс:

CREATE UNIQUE NONCLUSTERED INDEX [UF_Employee_UserID] ON [dbo].[Employee]
(
    [UserID] ASC--Not all Employees have a UserID to log into the System.
)
WHERE ([UserID] IS NOT NULL)--Enforce Uniqueness when not null.

Прямо сейчас вы все еще не можете создать Filtered-Index в SSMS при редактировании таблицы с использованием графического интерфейса.
Однако вы можете закрыть все открытые конструкторы таблиц, а затем открыть свойства самого индекса в обозревателе объектов, если вы хотите пройти через графический интерфейс вместо создания индекса вручную (как описано выше).

Ответ 7

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