Column Store Index es una nueva característica de SQL Server 2012 que mejora el rendimiento del almacenamiento de datos. A diferencia de los índices normales o pilas que almacenan los datos en una estructura de árbol B, el Column Store Index almacena los datos en columnas y usa una compresión para reducir los accesos a disco E/S necesarios para atender las solicitudes con un modo de procesamiento por lotes.
Este índice ha sido diseñado para acelerar significativamente consultas comunes d ealmacenamiento de datos que requieren análisis, agregación y filtrado de grandes cantidades de datos o unión de múltiples tablas con un esquema en estrella. En este artículo crearemos dos tablas con la única diferencia que una trabajará con un índice regular y la otra con una Column Store Index.
La sintáxis para la creación de una Column Store Index sería:
CREATE NONCLUSTERED COLUMNSTORE INDEX <IndexName> ON <TableName> (<Column1>, <Column2>, <Column3>, ... <Column N) ON <FilegroupName>
A continuación mostraré un ejemplo de creación de este tipo de índice. Primero crearé dos tablas:
CREATE TABLE TablaIndiceRegular(
[ID] [int] NOT NULL,
[DetalleID] [int] IDENTITY(1,1) NOT NULL,
[ProductoID] [int] NOT NULL,
[Total] [money],
CONSTRAINT [PK_TablaConIndiceRegular_Index]
PRIMARY KEY CLUSTERED
(
[ID] ASC,
[DetalleID] ASC
)) ON [PRIMARY]
GO
CREATE TABLE TablaColumnStoreIndex(
[ID] [int] NOT NULL,
[DetalleID] [int] IDENTITY(1,1) NOT NULL,
[ProductoID] [int] NOT NULL,
[Total] [money],
CONSTRAINT [PK_TablaColumnStoreIndex_Index]
PRIMARY KEY CLUSTERED
(
[ID] ASC,
[DetalleID] ASC
)) ON [PRIMARY]
GO
Y por último crearé un índice regular (campos ProductoID y Total) en la primera tabla y un índice del tipo Column Store Index (campos ProductoID y Total) en la segunda tabla.
CREATE_NONCLUSTERED_INDEX[XI_TablaConIndiceRegular_Index_ProductoID_Total]ON SalesOrderDetailWithRegularIndex(ProductID, LineTotal) CREATE_NONCLUSTERED_COLUMNSTORE_INDEX[XI_TablaColumnStoreIndex_Index_ProductoID_Total]ON SalesOrderDetailWithColumnStoreIndex(ProductID, LineTotal)
Los índices también se pueden crear usando el asistente en SSMS (SQL Server Management Studio) ý no sólamente mediante T-SL script.
Si se hace doble click en el índice creado se puede consultar y modificar las propiedades del índice creado.
Si en las tablas creadas se añadimos millones de registros y se ejecuta una consulta simple como puede ser por ejemplo:
SELECT ProductoID, SUM(Total) AS 'SumaTotal' FROM
<NOMBRE_TABLA>
GROUP BY ProductoID
ORDER BY ProductoID
Si miramos mediante SSMS el coste relativo a la consulta ejecutada sobre cada una de las tablas se puede apreciar una diferencia abismal ya que al trabajar con la tabla que usa el Column Store Index hablamos de un coste que se mueve cerca del 10%, en cambio trabajando con la tabla que tiene el índice regular hablamos de un coste relativo al 90%.
Esto se debe a que en SQL Server 2012 se ha introducido un nuevo modo de ejecución llamado modo por lotes, que procesa lotes de filas, en caso de procesar fila a fila que es la forma de trabajar de los índices regulares.
Quiero finalizar esta reseña nombrando un estudio realizado por Microsoft en un equipo de 32bits cons 256GB de RAM y una tabla de 1TB de datos (1,44 mil millones de filas) contrastando que las consultas que se realizaban habían variado su tiempo de respuesta de forma más que significativa puesto que consultas que tardadan 500 segundos originalmente, se ejecutaban en un tiempo de 1,1 segundos.