Columnstore y DML

Esta semana recibí una petición algo rara de un cliente que un batch no se ejecutaba con un error 35330, el proceso involucraba una columnstore en SQL Server 2014 en lo cual debo de admitir que tengo poca experiencia y también he experimentado poco con ellos.

Actualizado: 02 de Feberero del 2018

Como tal query involucraba en el mismo batch el hacer drop de un índice no clustered de columnstore y seguido de esto una sentencia delete, esto no causa ningún problema en un índice normal (no clustered o un índice clustured) pero los columnstore al ordenarse verticalmente y no por filas, puede hacer las consultas más rápidamente pero como podemos apreciar se sujetan a algunas restricciones.

• Columnstore Indexes Described

"To update a table with a columnstore index, drop the columnstore index, perform any required INSERT, DELETE, UPDATE, or MERGE operations, and then rebuild the columnstore index."

• http://connect.microsoft.com/SQLServer/feedbackdetail/view/963585/unable-to-modify-data-in-tables-that-have-a-columnstore-index-within-a-stored-procedure-msg-35330  (Nota connect a sido depreciado y no es posible ver este texto).

Aquí podemos ver este error declarado como bug pero como vemos esto es un comportamiento descrito en la documentación, ahora en 2012 esto era imposible pero en 2014 esto es por medio de unos componentes llamadas deltas.

Un query que podemos crear y probar fácilmente será el siguiente, en cualquier base de datos.

USE Test
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
  DROP TABLE dbo.test
CREATE TABLE [dbo].[test](
    [char1] [NVARCHAR](50) NULL
) ON [PRIMARY]
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCS ON [dbo].[test]
(
       [char1]
)WITH (DROP_EXISTING = OFF)
DROP INDEX NCCS ON test; 
DELETE FROM test;

Para poder solventar se intentaron varias opciones entre las cuales esta:

WAITFOR

Esta opción no funciona realmente debido a que un plan debe de ser compilado antes de ser ejecutado, un procedimiento almacenado cuando es creado se usa una revisión de sintaxis más esto no quiere decir que el plan sea compilado hasta que es ejecutado, en el caso de un query este es compilado antes de ser ejecutado, cuando hacemos la verificación del código nos informara el error:

"Msg 35330, Level 15, State 1, Procedure pruebacaso, Line 38
DELETE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, then rebuilding the columnstore index after DELETE is complete."

El mismo error aparecerá cuando intentamos ejecutarlo todo en un batch.

Soluciones:

Una opción aquí es el hecho de que usemos el batch separador que por default es la palabra "GO" aunque puede ser cambiada en SSMS.

Otra opción cuando se usa procedimiento almacenado seria separar el batch que está originando el problema en este caso la instrucción de DML "delete" en otro procedimiento y llamarlo al final del primero.

Una más que encontré es que podemos hacer es el uso de la OPTION(RECOMPILE) en una sentencia en específico, esto forzó una recopilación y creo un propio batch de ejecución aunque este dentro de un batch padre al forzar un plan propio y esto nos separa los procesos dentro de los batchs dando una solución que nos permite poner todas estas sentencias en el mismo store o batch sin llamar a otro procedimiento almacenado.

USE Test
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
  DROP TABLE dbo.test
CREATE TABLE [dbo].[test](
    [char1] [NVARCHAR](50) NULL
) ON [PRIMARY]
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCS ON [dbo].[test]
(
       [char1]
)WITH (DROP_EXISTING = OFF)
DROP INDEX NCCS ON test; 
DELETE FROM test OPTION(RECOMPILE);

Más información:

Pre compiled stored procedures fact or myth
Using the option recompile option for a statement