¿Qué son los índices columnares? Básicamente son un tipo de índice del motor relacional con la particularidad que se almacenan en columnas y nos aportan una mayor capacidad para resolver consultas complejas de una manera mucho más rápida. Los índices columnares están pensados para ser utilizados en entornos OLAP principalmente.
Características principales
- Modo de almacenamiento en columnas
- Vertipaq. Vamos a tener una disminución de la actividad de E/S y una mejora en el uso de la memoria
- Modo de procesamiento Batch
El almacenamiento columnar se realiza de la siguiente forma:
- Primero tenemos el almacenamiento tradicional por filas.Tenemos paginas de datos y los datos se almacenan en filas.
- En el almacenamiento por columna separamos los valores de cada columna, en segmentos, los segmentos contienen los datos de las columnas comprimidas con Vertipaq.
Crear un índice columnar
Para crear un índice columnar lo podemos crear mediante T-SQL:
CREATE NONCLUSTERED COLUMNSTORE INDEX nombre_indice ON NombreTabla
( columna1, columna2, columna n,…);
O bien, mediante el asistente, haciendo click derecho sobre la tabla que queremos crear el índice, en la carpeta Indexes
¿Qué ventajas nos ofrecen los índices columnares?
- Resolución de consultas de grandes volúmenes de datos de forma rápida
- Almacenamiento comprimido de los datos en memoria con el algoritmo Vertipaq
- Sólo carga en memoria los segmentos de las columnas de la consulta realizada
- Modo Batch. SQL lo utiliza para consultas con join, condición en el where, filtro o agregación.
¿Qué limitaciones tenemos?
- Tipos de datos no soportados:
- Binary, varbinary
- Ntext, text, image
- varchar (max) y nvarchar(max)
- uniqueidentifier
- rowversion, timestamp
- sql_variant
- decimal y numeric (con precisión superior a 18 digitos)
- Datetimeoffset (con escala mayor que 2)
- Tipos CLR (hierarchyid y tipos especiales)
- XML
- El índice columnar no permite más de 1024 columnas
- Sólo permite los índices no-clustered. Lo normal es que creemos un índice columnar por tabla con todas las columnas, el optimizador ya usará los segmentos de las columnas que necesite.
- No admite compresión nativa de SQL, ni PAGE ni ROW.
- No se aplica a vistas indexadas
- Las tablas con índice columnas se convierten a tablas de solo lectura. Por tanto no permiten INSERT, UPDATE, DELETE Y MERGE.
Alternativas para realizar cargas
Como hemos mencionado, una de las mayores limitaciones es que no nos permite escribir en la tabla que tenga un índice columnar creado. Para solventar esta limitación contamos con algunas alternativas:
1. Deshabilitar el índice. Si queremos realizar una carga, podemos deshabilitar el índice, realizar la carga y hacer rebuild del índice. Esta práctica tiene la desventaja que hacer el rebuild del índice consume mucho tiempo.
2. Otra práctica es crear una tabla en Stage que contenga:
- Una tabla auxiliar sin índice columnar: donde realizaremos las cargas de los datos.
- Una tabla histórico con índice columnar: tendrá todos los datos
- Y una vista sobre las dos tablas anteriores que tendrá un Union all de ambas tablas. De este modo todas las consultas de lectura las realzaremos sobre la vista, permitiendo carga de datos y utilizando la potencia de índice columnar.
3. Creación de particiones: El índice columnar va a existir a nivel de tabla base. No es necesario realizar ningún cambio en el índice columnar cuando creemos una partición, ya que este índice debe estar alineado con la partición de la tabla base. Por lo tanto sólo se puede crear un índice columnar en una tabla con particiones si la columna de partición es una columna del índice columnar.
Como hemos dicho, el uso de índices columnares está orientado en los DataWarehouse, porque son bases de datos orientadas al análisis, con muchas operaciones de lectura, carga de datos incrementales y manejan histórico de datos con grandes cantidades de datos. Es una buena práctica utilizar los índices columnares sabiendo sus limitaciones, restricciones, y en qué escenarios nos saldrá rentable.
