Los índices se utilizan para acelerar el proceso de consulta en SQL Server, lo que resulta en un alto rendimiento. Son similares a los índices de libros de texto. En los libros de texto, si necesita ir a un capítulo en particular, vaya al índice, busque el número de página del capítulo y vaya directamente a esa página. Sin índices, el proceso de encontrar el capítulo deseado habría sido muy lento.

Lo mismo se aplica a los índices en las bases de datos. Sin índices, un DBMS tiene que pasar por todos los registros de la tabla para recuperar los resultados deseados. Este proceso se llama escaneo de tablas y es extremadamente lento. Por otro lado, si crea índices, la base de datos va primero a ese índice y luego recupera los registros de la tabla correspondiente directamente.

Hay dos tipos de índices en SQL Server:

1. Índice agrupado
2. Índice no agrupado

Índice agrupado

Un índice agrupado define el orden en el que los datos se almacenan físicamente en una tabla. Los datos de la tabla se pueden ordenar de una sola manera, por lo tanto, solo puede haber un índice agrupado por tabla. En SQL Server, la restricción de clave principal crea automáticamente un índice agrupado en esa columna en particular.

Echemos un vistazo. Primero, cree una tabla de «estudiantes» dentro de «schooldb» ejecutando el siguiente script, o asegúrese de que su base de datos esté completamente respaldada si está usando sus datos en vivo:

Aviso aquí, en la tabla «estudiante», hemos establecido prima restricción de clave ry en la columna «id». Esto crea automáticamente un índice agrupado en la columna «id». Para ver todos los índices en una tabla en particular, ejecute el procedimiento almacenado «sp_helpindex». Este procedimiento almacenado acepta el nombre de la tabla como parámetro y recupera todos los índices de la tabla. La siguiente consulta recupera los índices creados en la tabla de estudiantes.

La consulta anterior devolverá este resultado:

En la salida puede ver el único índice. Este es el índice que se creó automáticamente debido a la restricción de clave principal en la columna «id».

Este índice agrupado almacena el registro en la tabla de estudiantes en orden ascendente de «id». Por lo tanto, si el registro insertado tiene el id de 5, el registro se insertará en la quinta fila de la tabla en lugar de en la primera fila. De manera similar, si el cuarto registro tiene una identificación de 3, se insertará en la tercera fila en lugar de la cuarta fila. Esto se debe a que el índice agrupado debe mantener el orden físico de los registros almacenados de acuerdo con la columna indexada, es decir, id. Para ver este orden en acción, ejecute el siguiente script:

El script anterior inserta diez registros en la tabla de estudiantes. Observe que aquí los registros se insertan en orden aleatorio de los valores en la columna «id». Pero debido al índice agrupado predeterminado en la columna id, los registros se almacenan físicamente en el orden ascendente de los valores en la columna «id». Ejecute la siguiente instrucción SELECT para recuperar los registros de la tabla de estudiantes.

Los registros se recuperarán en el siguiente orden:

Creación de un índice agrupado personalizado

Puede crear su propio índice personalizado así como el índice agrupado predeterminado. Para crear un nuevo índice agrupado en una tabla, primero debe eliminar el índice anterior.

Para eliminar un índice, vaya a «Object Explorer- > Bases de datos- > Database_Name- > Tablas- > Table_Name – > Índices”. Haga clic con el botón derecho en el índice que desea eliminar y seleccione BORRAR . Vea la captura de pantalla a continuación.

Ahora, para crear un nuevo índice agrupado, ejecute la siguiente secuencia de comandos:

El proceso de cre El índice agrupado es similar a un índice normal con una excepción. Con el índice agrupado, debe utilizar la palabra clave «CLÚSTER» antes de «ÍNDICE».

El script anterior crea un índice agrupado llamado «IX_tblStudent_Gender_Score» en la tabla de estudiantes. Este índice se crea en las columnas «gender» y «total_score». Un índice que se crea en más de una columna se llama «índice compuesto».

El índice anterior ordena primero todos los registros en orden ascendente de género. Si el género es el mismo para dos o más registros, los registros se ordenan en orden descendente de los valores en su columna «total_score». También puede crear un índice agrupado en una sola columna. Ahora, si selecciona todos los registros de la tabla de alumnos, se recuperarán en el siguiente orden:

Índices no agrupados

Un índice no agrupado no ordena los datos físicos dentro de la tabla. De hecho, un El índice no agrupado se almacena en un lugar y los datos de la tabla se almacenan en otro lugar. Esto es similar a un libro de texto donde el contenido del libro se encuentra en un lugar y el índice se encuentra en otro. Esto permite más de un no agrupado índice por tabla.

Es importante mencionar aquí que dentro de la tabla los datos serán ordenados por un índice agrupado. Sin embargo, dentro del índice no agrupado los datos se almacenan en el orden especificado. El índice contiene valores de columna en los que se crea el índice y la dirección del registro al que pertenece el valor de columna.

Cuando se emite una consulta contra una columna en la que se crea el índice, la base de datos primero irá al índice y buscará la dirección de la fila correspondiente en la tabla. Luego irá a esa dirección de fila y buscará otros valores de columna. Debido a este paso adicional, los índices no agrupados son más lentos que los índices agrupados.

Creación de un índice no agrupado

La sintaxis para crear un índice no agrupado es similar a el del índice agrupado. Sin embargo, en el caso de un índice no agrupado, se utiliza la palabra clave «NONCLUS EXCLUSATED» en lugar de «CLUSjected». Eche un vistazo al siguiente guión.

La secuencia de comandos anterior crea un índice no agrupado en la columna «nombre» de la tabla de estudiantes. El índice ordena por nombre en orden ascendente. Como dijimos anteriormente, los datos de la tabla y el índice se almacenarán en diferentes lugares. Los registros de la tabla se ordenarán por un índice agrupado si lo hay. El índice se ordenará según su definición y se almacenará por separado de la mesa.

Datos de la tabla de estudiantes:

Datos del índice IX_tblStudent_Name

Aviso , aquí en el índice cada fila tiene una columna que almacena la dirección de la fila a la que pertenece el nombre. Entonces, si se emite una consulta para recuperar el género y la fecha de nacimiento del estudiante llamado «Jon», la base de datos primero buscará el nombre «Jon» dentro del índice. Luego leerá la dirección de la fila de «Jon» e irá directamente a esa fila en la tabla «estudiante» para buscar el género y la fecha de nacimiento de Jon.

Conclusión

De la discusión encontramos las siguientes diferencias entre índices agrupados y no agrupados.

1. Solo puede haber un índice agrupado por tabla. Sin embargo, puede crear varios índices no agrupados en una sola tabla.
2. Los índices agrupados solo ordenan tablas. Por lo tanto, no consumen almacenamiento adicional. Los índices no agrupados se almacenan en un lugar separado de la tabla real y reclaman más espacio de almacenamiento.
3. Los índices agrupados son más rápidos que los índices no agrupados, ya que no implican ningún paso de búsqueda adicional.

Otros excelentes artículos de Ben

Diferencia entre la secuencia de identidad & en SQL Server

¿Cuál es la diferencia entre índices agrupados y no agrupados en SQL Server?

• Autor
• Publicaciones recientes

Ben Richardson dirige Acuity Training, un proveedor líder de formación SQL en el Reino Unido. Ofrece una gama completa de formación sobre SQL, desde cursos introductorios hasta formación avanzada en administración y almacenamiento de datos; consulte aquí para obtener más detalles. Acuity tiene oficinas en Londres y Guildford, Surrey. También escribe ocasionalmente en el blog de Acuity
Ver todas las publicaciones de Ben Richardson

Últimas publicaciones de Ben Richardson (ver todas)

• Power BI: gráficos en cascada y efectos visuales combinados – 19 de enero de 2021
• Power BI : Formato condicional y colores de datos en acción – 14 de enero de 2021
• Power BI: Importación de datos desde SQL Server y MySQL – 12 de enero de 2021

573.279 Vistas