Las tablas dentro del motor de base de datos SQL Server pueden ser de dos tipos, «heap» o «cluster». Ambos tipos de tablas almacenan la información de manera diferente, por lo que es importante conocer cada a detalle cada uno de estos tipos de tablas para poder tomar mejores decisiones al momento de diseñar una base de datos.
A las tablas de tipo «Heap», se les denomina así debido a que los registros se almacenan en forma de montón, es decir sin ningún orden en particular. Los registros se ingresaran en el primer espacio disponible dentro de las páginas pertenecientes a la tabla. Dicho esto se puede tener como conclusión dos cosas, la primera es que este tipo de tablas son muy rápidas para ingresos de información debido a que el motor de base de datos no hará otra cosa más que buscar el primer espacio libre dentro de las páginas de la tabla e ingresará el nuevo registro; la segunda es que son muy malas para las lecturas intensivas, quisiera resaltar la palabra intensiva porque si la tabla tiene pocos registros, como una tabla maestra, entonces este no es realmente un problema, pero si la tabla es bastante transaccional y tiene un numero de registros significativo, entonces si hay un problema porque las lecturas en disco serán bastante aleatorias cuando se busque un registro debido a que la información como ya mencionamos no se guarda en ningún orden en particular.
Para probar los puntos expuestos en el párrafo anterior, se planteara el siguiente ejemplo: una tabla de tipo heap que solo pueda almacenar dos registros por pagina de datos, esto se lograra debido a que esta tabla solo contara con dos columnas, una de tipo de dato «int» que como ya se sabe ocupa cuatro bytes y otra columna de tipo char(3000) la cual ocupara tres mil bytes, entonces si multiplicamos el espacio que ocupara cada registro por dos, nos dará 6016 bytes, y como hemos visto una página solo puede almacenar hasta 8 KB de información, es decir en una página no podrán entrar más de dos registros de esta tabla.
USE tempdb
GO
-- Eliminamos la tabla si ya existe
IF (OBJECT_ID('TablaHeap') IS NOT NULL)
DROP TABLE TablaHeap
-- Creamos la tabla heap
CREATE TABLE TablaHeap
(
Codigo INT
,Valor CHAR(3000)
)
GO
-- Ingresamos 6 registros, es decir 3 paginas
INSERT INTO TablaHeap
VALUES (1,REPLICATE('A',3000)),
(2,REPLICATE('A',3000)),
(3,REPLICATE('A',3000)),
(4,REPLICATE('A',3000)),
(5,REPLICATE('A',3000)),
(6,REPLICATE('A',3000))
GO
— El siguiente comando permite visualizar las paginas asignadas a la tabla
DBCC IND (‘tempdb’, ‘TablaHeap’, 1);
GO
— Este es el resultado, ha sido recortado por temas de espacio pero estas
— son las columnas q nos interesan. Basicamente hay 4 paginas la primera
— es de tipo «IAM Page» (PageType = 10), permite rastrear el espacio
— disponible en las demas paginas de datos (PageType = 1). Entonces como
— podemos ver tenemos 3 paginas de datos como lo predijimos
/*
PageFID PagePID IAMPID ObjectID IndexID PageType
——- ———– ———– ———– ———– ——–
1 121 NULL 261575970 0 10
1 120 121 261575970 0 1
1 126 121 261575970 0 1
1 127 121 261575970 0 1
*/
— Elimino un registro
DELETE TablaHeap
WHERE Codigo = 3
GO
— Inserto un nuevo registro, al eliminar el anterior, este deberia ingresar
— en la misma pagina del registro anterior sin necesidad de crear otra nueva
— pagina.
INSERT INTO TablaHeap
VALUES (9,REPLICATE(‘A’,3000))
GO
— Ahora comprobaremos que efectivamente siguen solo las 3 paginas de datos
DBCC IND (‘tempdb’, ‘TablaHeap’, 1);
GO
/*
PageFID PagePID IAMPID ObjectID IndexID PageType
——- ———– ———– ———– ———– ——–
1 121 NULL 261575970 0 10
1 120 121 261575970 0 1
1 126 121 261575970 0 1
1 127 121 261575970 0 1
*/
— Es mas para terminar de comprobarlo haremos un select a los datos de la
— tabla y veremos que el registro 9 aparece en el tercer lugar de la consulta
SELECT Codigo, Valor FROM TablaHeap
GO
/*
Codigo Valor
———– ——————–
1 AAAAAAAAAAAAAAAAA…
2 AAAAAAAAAAAAAAAAA…
9 AAAAAAAAAAAAAAAAA…
4 AAAAAAAAAAAAAAAAA…
5 AAAAAAAAAAAAAAAAA…
6 AAAAAAAAAAAAAAAAA…
*/
Ahora veremos un poco de lo que son las tablas de tipo «cluster», este tipo de tablas tiene la particularidad que esta compuestas de un índice de tipo «cluster», el cual en realidad es el que contiene toda la data de la tabla, es decir no hay una estructura aparte que contenga toda esta información, como lo es una tabla «heap». Entonces al estar toda la información contenida en un índice, esta siempre estará ordenada por la llave del índice. Estas tablas son muy optimas para las consultas, especialmente si se hacen por la llave del índice, debido a que la información ya se encuentra ordenada, la búsqueda será mucho más rápida y eficiente. En cuanto al ingreso de datos, estas tablas pueden tener algunos problemas, dependiendo de cómo este constituida la clave del índice «cluster» y el tamaño del registro de la tabla, estos dos factores pueden causar problemas a largo plazo debido a que puede estar altamente fragmentada, o puede tener muchos «forwarding pointers», los cuales finalmente causan que la tabla sea mucho más grande de lo que realmente es, y también causa problemas de fragmentación, los cuales a la larga afectan a las consultas.
A continuación mostrare un ejemplo del uso de esta tabla «cluster», para esto se creara una tabla, a la que posteriormente se le creara un índice «cluster». Luego se ingresaran 6 registros, los cuales ocuparan tres páginas de datos debido a que según el tamaño del registro definido para esta tabla solo permitirá hasta 2 registros por página. Seguidamente se procederá a borrar uno de esos registros y se ingresara otro. Se podrá observar que el comportamiento para el almacenamiento de esta tabla difiere con la tabla «heap», debido a que como el código del nuevo registro será mayor al de los demás, SQL Server asignara una nueva página para este nuevo registro, esta página se encontrara al final de índice.
USE tempdb
GO
-- Eliminamos la tabla si ya existe
IF (OBJECT_ID('TablaClustered') IS NOT NULL)
DROP TABLE TablaClustered
-- Creamos la tabla
CREATE TABLE TablaClustered
(
Codigo INT
,Valor CHAR(3000)
)
GO
-- Ahora la convierto en cluster
CREATE CLUSTERED INDEX CLIX_TablaClustered ON TablaClustered (Codigo)
GO
-- Ingresamos 6 registros, es decir 2 paginas
INSERT INTO TablaClustered
VALUES (1,REPLICATE('A',3000)),
(2,REPLICATE('A',3000)),
(3,REPLICATE('A',3000)),
(4,REPLICATE('A',3000)),
(5,REPLICATE('A',3000)),
(6,REPLICATE('A',3000))
GO
-- El siguiente comando permite visualizar las paginas asignadas a la tabla
DBCC IND ('tempdb', 'TablaClustered', 1);
GO
-- Este es el resultado, ha sido recortado por temas de espacio pero estas
-- son las columnas q nos interesan. Basicamente hay 5 paginas la primera
-- es de tipo "IAM Page" (PageType = 10), permite rastrear el espacio
-- disponible en las demas paginas de datos. Adicionalmente tenemos 1 pagina
-- de indice (PageType = 2). Finalmente podemos ver las 3 paginas de datos
-- (PageType = 1).
/*
PageFID PagePID IAMPID ObjectID IndexID PageType
------- ----------- ----------- ----------- ----------- --------
1 151 NULL 309576141 1 10
1 148 151 309576141 1 1
1 173 151 309576141 1 2
1 174 151 309576141 1 1
1 175 151 309576141 1 1
*/
-- Elimino un registro
DELETE TablaClustered
WHERE Codigo = 3
GO
-- Inserto un nuevo registro, este deberia ingresar al final de la tabla debido
-- al orden que tiene la tabla sobre la columna "Codigo", la cual definimos como
-- llave de nuestro indice cluster
INSERT INTO TablaClustered
VALUES (9,REPLICATE('A',3000))
GO
-- Ahora comprobaremos que efectivamente se ha agregado una nueva pagina de datos
DBCC IND ('tempdb', 'TablaClustered', 1);
GO
/*
PageFID PagePID IAMPID ObjectID IndexID PageType
------- ----------- ----------- ----------- ----------- --------
1 151 NULL 309576141 1 10
1 148 151 309576141 1 1
1 173 151 309576141 1 2
1 174 151 309576141 1 1
1 175 151 309576141 1 1
1 90 151 309576141 1 1
*/
-- Finalmente para terminar de comprobarlo haremos un select a los datos de la
-- tabla y veremos que los codigos aparecen en orden ascendente en la consulta
SELECT Codigo, Valor FROM TablaClustered
GO
/*
Codigo Valor
----------- --------------------
1 AAAAAAAAAAAAAAAAA...
2 AAAAAAAAAAAAAAAAA...
4 AAAAAAAAAAAAAAAAA...
5 AAAAAAAAAAAAAAAAA...
6 AAAAAAAAAAAAAAAAA...
9 AAAAAAAAAAAAAAAAA...
*/
Finalmente podemos sacar como conclusión que ambos tipos de tablas tienen sus ventajas y desventajas, ya es tarea de nosotros poder decidir donde debemos aplicar cada tipo, según sea el caso. Una cosa que es importante mencionar es que ambos tipos de tabla pueden tener índices «no cluster» los cuales ayudan a agilizar la búsqueda de los datos. Bueno eso fue todo lo que respecta a este tema, mas adelante hare otro post exclusivamente sobre índices y su funcionamiento.
Memorias de un DBA 