sql-server - una - indice unico sql server

¿Cuáles son las diferencias entre un índice agrupado y un índice no agrupado? (10)

¿Cuáles son las diferencias entre un non-clustered index clustered y un non-clustered index ?

Índice agrupado

Solo puede haber un índice agrupado para una tabla.
Generalmente se hace en la clave primaria.
Los nodos de hoja de un índice agrupado contienen las páginas de datos.

Índice no agrupado

Solo puede haber 249 índices no agrupados para una tabla (hasta las versiones posteriores de sql versión 2005 admiten hasta 999 índices no agrupados).
Generalmente hecho en cualquier tecla.
El nodo hoja de un índice no agrupado no consta de las páginas de datos. En su lugar, los nodos de hoja contienen filas de índice.

Índice agrupado

Solo un índice agrupado puede estar allí en una tabla
Ordene los registros y almacénelos físicamente de acuerdo con el pedido.
La recuperación de datos es más rápida que los índices no agrupados
No necesita espacio extra para almacenar la estructura lógica.

Índice no agrupado

Puede haber cualquier número de índices no agrupados en una tabla
No afecta el orden físico. Cree un orden lógico para las filas de datos y use punteros para archivos de datos físicos
La inserción / actualización de datos es más rápida que el índice agrupado
Usa espacio extra para almacenar estructura lógica

Aparte de estas diferencias, debe saber que cuando la tabla no está agrupada (cuando la tabla no tiene un índice agrupado), los archivos de datos no están ordenados y utiliza la estructura de datos Heap como estructura de datos.

Índice agrupado

Solo uno por mesa
Más rápido de leer que no agrupado, ya que los datos se almacenan físicamente en orden de índice

Índice no agrupado

Se puede usar muchas veces por mesa.
Más rápido para las operaciones de inserción y actualización que un índice agrupado

Ambos tipos de índice mejorarán el rendimiento cuando se seleccionen datos con campos que usan el índice pero ralentizarán las operaciones de actualización e inserción.

Debido a que los índices agrupados de inserción y actualización más lentos se deben establecer en un campo que normalmente es incremental, es decir, Id o Timestamp.

Normalmente, SQL Server solo usará un índice si su selectividad es superior al 95%.

Básicamente, agrupado significa que los datos están en ese orden físico en la tabla. Es por esto que solo puedes tener uno por mesa.

Desagrupado significa que es "solo" un orden lógico.

Los índices agrupados ordenan físicamente los datos en el disco. Esto significa que no se necesitan datos adicionales para el índice, pero solo puede haber un índice agrupado (obviamente). Acceder a los datos utilizando un índice agrupado es más rápido.

Todos los demás índices deben ser no agrupados. Un índice no agrupado tiene un duplicado de los datos de las columnas indexadas mantenidas ordenadas junto con los punteros a las filas de datos reales (punteros al índice agrupado, si existe). Esto significa que el acceso a los datos a través de un índice no agrupado debe pasar por una capa adicional de direccionamiento indirecto. Sin embargo, si selecciona solo los datos que están disponibles en las columnas indexadas, puede recuperar los datos directamente de los datos del índice duplicado (por eso es una buena idea SELECCIONAR solo las columnas que necesita y no usar *)

Los índices agrupados se almacenan físicamente en la tabla. Esto significa que son los más rápidos y solo puede tener un índice agrupado por tabla.

Los índices no agrupados se almacenan por separado y puede tener tantos como desee.

La mejor opción es establecer su índice agrupado en la columna única más utilizada, generalmente la PK. Siempre debe tener un índice agrupado bien seleccionado en sus tablas, a menos que haya una razón muy convincente: no puede pensar en uno solo, pero bueno, puede estar ahí fuera, porque no hacerlo aparece.

Pros:

Los índices agrupados funcionan muy bien para rangos (por ejemplo, seleccione * de my_table donde my_key entre @min y @max)

En algunas condiciones, el DBMS no tendrá que hacer un trabajo para ordenar si utiliza una declaración orderby.

Contras:

Los índices agrupados pueden retardar las inserciones porque las distribuciones físicas de los registros deben modificarse a medida que se colocan los registros si las nuevas claves no están en orden secuencial.

Un índice agrupado en realidad describe el orden en que los registros se almacenan físicamente en el disco, por lo tanto, la razón por la que solo puede tener uno.

Un índice no agrupado define un orden lógico que no coincide con el orden físico en el disco.

Un índice agrupado es esencialmente una copia ordenada de los datos en las columnas indexadas.

La principal ventaja de un índice agrupado es que cuando su consulta (búsqueda) localiza los datos en el índice, no se necesita IO adicional para recuperar esos datos.

La sobrecarga de mantener un índice agrupado, especialmente en una tabla que se actualiza con frecuencia, puede llevar a un bajo rendimiento y, por esa razón, puede ser preferible crear un índice no agrupado.

Una base de datos indexada tiene dos partes: un conjunto de registros físicos, que se organizan en un orden arbitrario, y un conjunto de índices que identifican la secuencia en la que se deben leer los registros para obtener un resultado ordenado por algún criterio. Si no existe una correlación entre la disposición física y el índice, entonces leer todos los registros en orden puede requerir realizar muchas operaciones de lectura independientes de un solo registro. Debido a que una base de datos puede leer docenas de registros consecutivos en menos tiempo del que se tardaría en leer dos registros no consecutivos, el rendimiento puede mejorarse si los registros que son consecutivos en el índice también se almacenan consecutivamente en el disco. Especificar que un índice está agrupado hará que la base de datos haga un esfuerzo (las diferentes bases de datos difieren en cuanto a la cantidad) para organizar las cosas de modo que los grupos de registros que son consecutivos en el índice sean consecutivos en el disco.

Por ejemplo, si uno comenzara con una base de datos no agrupada vacía y agregara 10,000 registros en una secuencia aleatoria, los registros probablemente se agregarían al final en el orden en que se agregaron. Leer la base de datos en orden por el índice requeriría 10,000 lecturas de un registro. Sin embargo, si uno utilizara una base de datos agrupada, el sistema podría verificar al agregar cada registro si el registro anterior se almacenó solo; si encuentra que ese es el caso, podría escribir ese registro con el nuevo al final de la base de datos. Luego podría mirar el registro físico antes de las ranuras donde solían residir los registros movidos y ver si el registro que siguió se almacenó solo. Si descubriera que ese es el caso, podría mover ese registro a ese lugar. El uso de este tipo de enfoque haría que muchos registros se agruparan en pares, lo que potencialmente duplicaría la velocidad de lectura secuencial.

En realidad, las bases de datos agrupadas usan algoritmos más sofisticados que esto. Sin embargo, una cosa importante que se debe tener en cuenta es que existe un compromiso entre el tiempo necesario para actualizar la base de datos y el tiempo necesario para leerla de forma secuencial. Mantener una base de datos agrupada aumentará significativamente la cantidad de trabajo requerido para agregar, eliminar o actualizar registros de cualquier manera que afecte la secuencia de clasificación. Si la base de datos se leerá secuencialmente con mucha más frecuencia de la que se actualizará, el agrupamiento puede ser una gran ganancia. Si se actualizará con frecuencia pero rara vez se leerá en secuencia, la agrupación en clúster puede ser una gran pérdida de rendimiento, especialmente si la secuencia en la que se agregan los elementos a la base de datos es independiente de su orden de clasificación con respecto al índice agrupado.