restriccion - sql server add column unique

Clave única frente a índice único en SQL Server 2008

Question

Tengo una tabla llamada countries y defino que la columna country_name sea ​​única creando un "Index / Key" de tipo "Unique Key" en SQL Server 2008 R2.

Pero tengo las siguientes preguntas:

1. ¿creará "Índice / Clave" de tipo "Clave única" automáticamente creará un índice no agrupado en esta columna?
2. si cambio el tipo de "Clave única" a "Índice" y el valor de IsUnique es "Sí", ¿habrá alguna diferencia?
3. Entonces, ¿por qué hay dos opciones, "clave única" e "índice", creo que los dos son iguales?

Answer 1

Aparte de las excelentes respuestas anteriores, agregaría mis 2 centavos aquí.

La clave única es una restricción y utiliza un índice único para imponerse. Así como la clave principal generalmente se aplica mediante un índice único agrupado. Lógicamente hablando, una restricción y un índice son dos cosas diferentes. Pero en RDBMS, una restricción se puede implementar físicamente a través de un índice.

Si se crea una tabla con una restricción única en el servidor sql, verá tanto un objeto de restricción como un índice único

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;

obtendremos lo siguiente (una restricción y un índice)

Sin embargo, si no creamos una restricción sino solo un índice único como el siguiente

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

Verá que NO hay ningún objeto de restricción creado (solo se creó un índice).

Desde la perspectiva "teórica", en SQL Server, una restricción es un objeto con valor object_id y está enlazado al esquema, mientras que un índice no es un objeto y no tiene ningún valor object_id ni ningún esquema relacionado.

Answer 2

Si está utilizando SqlMetal.exe para dar salida a las entidades DBML o LinqToSql:

• Si una clave externa utiliza una clave única, obtendrá una asociación como se esperaba.
• Si una clave externa usa un índice único, no se mostrará.

La razón está en la implementación de SqlMetal. Consulta el esquema de información de la base de datos, específicamente el uso de columnas clave. Las claves únicas están representadas allí, pero los índices únicos no lo están.

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

Answer 3

Una cosa adicional a mencionar es que si crea un índice, puede especificar las columnas incluidas, esto puede ayudar a que su código sql funcione más rápido si hay alguna búsqueda por country_name.

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''

El servidor SQL almacenará "foo_other_column" en el índice mismo. En caso de restricción única, primero encontrará el índice de ''prueba'', luego buscará la fila en la tabla foo y solo allí tomará "foo_other_column".

Answer 4

Una restricción única se implementa detrás de escena como un índice único, por lo que realmente no importa cómo lo especifique. Tiendo a implementarlo simplemente como:

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

Algunas personas crean un índice único en su lugar, por ejemplo

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

La diferencia está en la intención: si está creando la restricción para imponer reglas de exclusividad / negocio, crea una restricción, si lo hace para ayudar al rendimiento de la consulta, podría ser más lógico crear un índice único. Nuevamente, bajo las sábanas, es la misma implementación, pero el camino que tome para llegar puede ayudar a documentar su intención.

Creo que hay múltiples opciones para cumplir tanto con la funcionalidad anterior de Sybase como con el estándar ANSI (aunque las restricciones únicas no se adhieren al estándar 100%, ya que solo permiten un valor NULL, un índice único, en por otro lado, puede evitar esto agregando una cláusula WHERE col IS NOT NULL ( WHERE col IS NOT NULL ) en SQL Server 2008 y posteriores).