Cambiando índices clustered en SQL Azure

Hoy me he encontrado con un problemilla “gracioso” por el tema de las restricciones de SQL Azure. La restricción en cuestión es que todas las tablas en SQL Azure tienen que tener un índice CLUSTERED para poder operar con ellas (operaciones de INSERT, UPDATE, DELETE). Una vez creado un índice de este tipo, no se pueden eliminar.

El ejemplo siguiente ilustra la restricción. Está claro que el comando de eliminación es inútil justo después de crear la tabla, pero en cualquier otra versión de SQL el comando se ejecuta sin errores:

CREATE TABLE dbo.Articulos

(

       ArticuloID [int] IDENTITY(1,1) NOT NULL,

       NombreArticulo nvarchar(50)

)

DELETE FROM Articulos WHERE ArticuloID=1

Msg 40054, Level 16, State 1, Line 1

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

Esta y otras restricciones está en la lista de especificaciones y restricciones de SQL Azure, y es debido a que no se soportan tablas Heap, –tablas que por definición no tienen ningún índice clustered- y realmente no es un problema. Crearemos entonces un índice, por ejemplo, por ArticuloID.

CREATE UNIQUE CLUSTERED INDEX PK_Articulos ON dbo.Articulos (ArticuloID)

DELETE FROM Articulos WHERE ArticuloID=1

(0 row(s) affected)

Como podemos observar, el comando de eliminación, que sigue siendo más inútil que un paraguas en un submarino, ya no da errores.

Ahora imaginemos que agregamos a la tabla un nuevo campo en el que vamos a almacenar un nuevo código de artículo (no me preguntéis el fin del mismo, pueden haber muchas razones para ello y esto es sólo un ejemplo). Para ello podemos hacerlo ejecutando la siguiente sentencia:

ALTER TABLE dbo.Articulos ADD OtroArticuloID [int] NOT NULL

Ahora agreguemos un registro a la tabla para representar que no está vacía:

INSERT INTO Articulos (NombreArticulo, OtroArticuloID)

       VALUES ('Reloj', 4)

Una vez agregado el campo, en nuestro caso queremos que el índice clustered sea por este nuevo campo OtroArticuloID. Como sabemos, no puede haber más de un índice clustered por tabla, por lo que lo común es hacer un DROP del índice anterior y crear de nuevo el índice por el nuevo código. Sin embargo, al intentar realizar el primero paso nos encontramos con la restricción comentada porque un índice clustered no se puede eliminar (recordemos que esto sólo ocurre en la edición Azure de SQL Server).

DROP INDEX PK_Articulos ON dbo.Articulos

Msg 40054, Level 16, State 2, Line 1

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

Se puede comprobar que, aunque ahora vaciemos la tabla primero con un DELETE FROM Articulos el resultado sigue siendo el mismo, que no podemos eliminar el índice.

¿La solución?

Solución no hay. El workaround propuesto desde los foros de MSDN es el siguiente:

1. Empezar transacción
2. Crear una nueva tabla con la nueva estructura de índices
3. Copiar los datos de una tabla a otra
4. Renombrar las tablas e índices
5. Finalizar transacción

Aplicado a nuestro caso, quedaría algo así:

-- 1) Comenzar transaccion

BEGIN TRAN

-- 2) Crear la nueva estructura de tabla

CREATE TABLE dbo.ArticulosNew

(

       ArticuloID [int] IDENTITY(1,1) NOT NULL,

       NombreArticulo nvarchar(50),

       OtroArticuloID [int] NOT NULL

       CONSTRAINT [PK_ArticulosNew] PRIMARY KEY CLUSTERED (OtroArticuloID ASC)

)

-- 3) Copiar los datos de la tabla anterior a la nueva

SET IDENTITY_INSERT dbo.ArticulosNew ON

INSERT INTO dbo.ArticulosNew (ArticuloID, NombreArticulo, OtroArticuloID)

       SELECT ArticuloID, NombreArticulo, OtroArticuloID FROM dbo.Articulos

SET IDENTITY_INSERT dbo.ArticulosNew OFF

-- 4) Renombrar las tablas e índices

DROP TABLE dbo.Articulos

EXEC sp_rename 'ArticulosNew', 'Articulos'

EXEC sp_rename 'PK_ArticulosNew', 'PK_Articulos'

-- 5) Finalizar la transaccion

COMMIT TRAN

En el caso de que hubiera relaciones con otras tablas, cosa normal con las claves primarias, el script se complicaría algo más ya que hay que eliminar las relaciones entre el paso 1 y 2, para volver a crearlas entre el paso 4 y 5.

Tarea: comprobar si las herramientas de Red Gate ya soportan SQL Azure y tienen esto implementado. 

Actualización 24/03/2011: ¡Comprobado con la nueva versión 9! ver comentarios abajo

También se me ocurre hacer un procedimiento almacenado que, tirando de sys_objects, ejecute un script de inyección que lo haga en un solo paso. En cuanto lo tenga, actualizo el post.

¿Y si la tabla tiene índice clustered pero al operar con ella da el mismo error?

Al seguir haciendo un traspaso a SQL Azure, me he encontrado con que al hacer un intento de eliminación de una tabla me daba el mismo error aun comprobando más de 10 veces que en efecto el índice clustered estaba creado correctamente.

¿Dónde estaba el problema?

Se me ocurrió que la tabla podría tener relaciones con eliminaciones en cascada, y efectivamente tenía Foreing Keys. Fui mirando una a una hasta que vi que una de las tablas relacionadas tenía clave primaria pero no era clustered. La creé y se solucionó el problema.

Aconsejo utilizar la siguiente consulta -gracias al post de Thomas LaRock- para descubrir si tenemos alguna tabla sin índice clustered, ya que si inicialmente las tablas están vacías no nos vamos a dar cuenta hasta que intentemos operar con ellas.

/* Encontrar tablas sin ndices clustered */
SELECT name
FROM sys.objects
WHERE type = 'U'
  AND object_id NOT IN (SELECT object_id FROM sys.indexes WHERE index_id = 1)

Espero que haya servido de ayuda.

Happy coding!