SQL Server Instrucciones DDL CREATE TABLE y CREATE INDEX

Introducción

Ya se ha indicado en la entrega anterior sobre las instrucciones relacionadas con Data Definition Language (DDL) relacionadas con CREATE. En esta ocasion hablaremos de las instrucciones CREATE TABLE y CREATE INDEX.

CREATE TABLE

El uso de esta instrucción crea una nueva tabla en Microsoft SQL Server y en Azure SQL Database. La sintaxis de la instrucción es básicamente, la misma. Sin embargo, cuando se crea una tabla, debe indicarse el conjunto de columnas y el tipo de dato que contendrá. 

La sintaxis básica en la siguiente:

CREATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( { <column_definition> } [ ,... n ] )[ ; ]

Donde:

database_name - El nombre de la base de datos en la que se creara la tabla. Si se indica el nombre de base de datos debe especificar una base de datos existente. Si no se especifica, el nombre de base de datos toma por defecto la base de datos actual.

schema_name – Indica el nombre del esquema al que pertenecerá la nueva tabla.

table_name – Indica el nombre de la nueva tabla y deben seguir las reglas para los identificadores.

<column_definition> - column_name <data_type>

column_name – Indica el nombre de la columna que se creará.

<data_type> - Especifica el tipo de datos de la columna, el cual debe ser un tipo valido y especificar las propiedades de longitud y precisión en caso necesario.

La sintaxis incluye el nombre de la tabla, es posible indicar dentro del nombre esta la posibilidad de incluir el nombre de la base de datos y el nombre del esquema, separados por un punto. Asimismo, se puede observar que se debe incluir un par de paréntesis y dentro del par, se debe indicar la definición de las columnas, donde al menos una debe ser definida.

Es importante mencionar que la definición de la llave primaria de una tabla puede ser indicada cuando se lleva a cabo la creación de la tabla, en caso contrario, debe ser definida utilizando la correspondiente instrucción de modificación.

Existen otras opciones dentro de la definición de las tablas, como la especificación de identidad en una columna, o bien la definición de una restricción, un valor por omisión, la asignación de particionamiento en la tabla y otras opciones, para ello será necesario revisar la documentación correspondiente.

Tablas temporales

En Microsoft SQL Server es posible crear tablas de uso temporal que son definidas como locales y como globales. Las tablas temporales locales son visibles solo en la sesión de trabajo actual y las tablas temporales globales son visibles para todas las sesiones de trabajo. 

Para definir este tipo de tablas, se debe antepoer a los nombres de las tablas temporales locales un signo de número único (#table_name) y en los nombres de las tablas temporales globales anteponga con un signo de número doble (##table_name).

Ejemplo 1:

CREATE TABLE test (
    a INT,
    b INT
);

GO 

 

En el ejemplo, se puede observar que se creará la tabla denominda test bajo el esquema dbo, que es el de omisión, en la base de datos actual. Dentro de la tabla se tendrán dos columnas, denominadas a y b, ambas de tipo INT (entero).

Ejemplo 2:

CREATE TABLE HR.Employees

(

    EmployeeID INT NOT NULL,

    Salary Money NOT NULL,

    ValidFrom DATETIME2 NOT NULL,

    ValidTo DATETIME2 NOT NULL

);

GO 

En este ejemplo, se creará una tabla denominada Employees en el esquema denominado HR en la base de datos actual. Contendrá cuatro columnas, denominadas EmployeeID de tipo INT, Salary de tipo Money, ValidFrom y ValidTo ambas de tipo DATETIME2. Asimismo, se puede observar que se ha indicado en todas la columnas se han indicado que tienen que validarse que siempre contengan informacion, no puede haber valores nulos con la clausula NOT NULL en cada definición de columna.

Ejemplo 3:

CREATE TABLE dbo.Sample

(

    col1 INT,

    col2 INT,

    PRIMARY KEY CLUSTERED (col1)

);

GO

En este ejemplo, se creará una tabla denominda Sample en el esquema dbo de la base de datos actual. Contendrá dos columnas identificadas como col1 y col2 ambas de tipo INT, ademas se ha indicado la creación de la llave primaria de tipo agrupado que incluye a la columna col1.

Ejemplo 4:

CREATE TABLE #MyTempTable (

    col1 INT PRIMARY KEY

);

GO

Este ejemplo, creara una tabla temporal denominada #MyTempTable, esta tabla se creará en la base de datos temp. Contendrá una columna denominada col1 de tipo INT, especificando que será asignada como llave primaria.

CREATE INDEX

Se sabe que el índice que se define en una base de datos es una estructura de datos que se utiliza para mejorar la velocidad de las operaciones de consulta, de esta forma, por medio de un identificador único de cada fila de una tabla, permite un rápido acceso a los registros de una tabla dentro de una base de datos. El uso de esta instrucción crea un índice relacional en una tabla o vista, previamente definida.

La sintaxis básica es la siguiente:

CREATE INDEX {index_name] ON {schema_name.table_name} ( {column_name} [,…n] )[;]

Donde:

Index_name - El nombre del índice, que debe ser único dentro de una tabla o vista, pero no necesariamente tienen que ser únicos dentro de una base de datos, y deben seguir las reglas para los identificadores.

schema_name.table_name – El nombre de la tabla al que se generará el indice indicando el esquema donde se encuentra definida.

column_name – nombre de columna o columnas en las que se basa el índice. Debe especificarse dos o más nombres de columnas para crear un índice compuesto sobre los valores combinados en las columnas especificadas.

Es necesario indicar que la sintaxis anterior, creará un índice de tipo no-agrupado, que es el tipo asignado por omisión. Debe recordarse que los índices de tipo agrupado solo pueden definirse uno por tabla, de tal forma debe indicarse que se desea definir uno de este tipo. Asimismo, es posible indicar que el índice sea único, indicando que no se permita que dos filas tengan el mismo valor de clave de índice. De igual forma, recuérdese que un índice agrupado en una vista debe ser único. Ya se ha mencionado que existen diversos tipos de indices, revise la entrada SQL Server Conociendo sobre Indices.

Existen otras opciones para la definición de los índices, usando la sintaxis adecuada, por lo que debe revisarse la documentación correspondiente.

Ejemplo 1:

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);

GO

En este ejemplo, se creará un índice no agrupado denominado IX_VendorID en la tabla ProductVendor del esquema dbo, sobre la columna VendorID.

Ejemplo 2:

CREATE CLUSTERED INDEX PK_Directorio_id ON Directorio (Id);

GO

En el ejemplo se establece la creación de un indice agrupado denominado PK_Directorio_id en la tabla Directorio del esquema dbo, sobre la columna id.

Conclusión

La creacion de tablas e indices en una base de datos a través de las instrucciones previamente indicadas es una de las actividades más importantes que se realiza cuando se crea o mantiene una base de datos. Recuerdese que los índices aplican principalmente en las tablas para ayudar en la consulta de información almacenada en ellas.

Es importante consultar la documentación relacionada con la sintaxis para la creación de los diversos tipos de tablas que se pueden manejar, asi como la correspondiente a la creación de índices.

SQL Server Conociendo sobre indices

Introducción

Siempre es importante conocer los tipos de índices que se manejan en el motor de base de datos de Microsoft, ya sea de Microsoft SQL Server o Azure SQL Database, de esta forma, en esta entrega hablare de los índices.

Es importante recordar que un índice de base de datos es una estructura de datos que mejora la velocidad de las operaciones de recuperación de datos en una tabla de base de datos a costa de escrituras adicionales y espacio de almacenamiento para mantener la estructura de datos del índice. Los índices se utilizan para ubicar datos rápidamente sin tener que escanear una tabla de la base de datos cada vez que se accede a una solicitud de consulta.

Actualmente se pueden catalogar en tres tipos:

• Índices agrupados (clustered indexes)
• Índices no-agrupados (non-clustered indexes)
• Índices únicos (unique Index)

Índices Agrupados

Los índices agrupados permiten ordenar y almacenar las filas de datos en una tabla o vista en función de sus valores clave. Es necesario indicar que un índice agrupado es una estructura que se define como unida a la misma tabla, son columnas incluidas en la definición del índice.

Es preciso indicar que sólo puede haber un índice agrupado por tabla, porque las filas de datos en sí mismas se pueden almacenar en un solo orden. Para Microsoft SQL Server, un índice es una estructura en disco asociada con una tabla o vista que mejora la velocidad de recuperación de filas de una tabla o vista. Un índice se compone de claves creadas a partir de una o más columnas en una tabla o vista. A este tipo de índices también se denominan como índice de almacén de filas porque es un índice de árbol B.

Microsoft SQL Server Clustered Index Representation

Estas claves que componen el índice se almacenan en una estructura (árbol B) que permite a Microsoft SQL Server encontrar las filas asociadas con valores clave de manera rápida y eficiente. Un árbol B es un árbol de búsqueda binario equilibrado, es un árbol que automáticamente mantiene su altura pequeña para una secuencia de inserciones y eliminaciones.

Para crear un índice agrupado se declara dentro de la creación de la tabla, como ejemplo se indicará la creación de una tabla de un directorio, de la siguiente forma:

--- Crear una tabla incluyendo indice agrupado

CREATE TABLE Directorio
(
id int NOT NULL PRIMARY KEY CLUSTERED,
Name varchar(100)
);

Otra forma de crear la misma tabla sería

--- Crear tabla incluyendo indice agrupado al final de las columnas

CREATE TABLE Directorio
(
id int NOT NULL,
Name varchar(100),
CONSTRAINT PK_Directorio_Id PRIMARY KEY CLUSTERED(Id)
);

Finalmente, es posible crear la tabla sin declarar algún índice y crearlo posteriormente, como se muestra a continuación:

--- Crear tabla y posteriormente crear indice

CREATE TABLE Directorio
(
id int NOT NULL,
Name varchar(100)
);

--- Crear indice agrupado para la tabla anterior

CREATE CLUSTERED INDEX PK_Directorio_id 

ON Directorio (Id);

Cualquiera de estos tres ejemplos crea la tabla nombrada directorio indicando que el índice agrupado este asociado a la tabla a través de la columna Id.

Con la llegada de la versión Microsoft SQL Server 2012 y para el uso de almacenes de datos, es posible llevara a cabo la definición de índices agrupados con almacenamiento de columnas. El índice incluye todas las columnas de la tabla y almacena la tabla completa. Si la tabla existente es un índice agrupado o de pila, la tabla se convierte en un índice de almacén de columnas agrupado. Si la tabla ya está almacenada como un índice de almacén de columnas agrupado, el índice existente se elimina y éste se reconstruye.

Para crear un índice agrupado con almacenamiento de columnas se usa la siguiente sentencia:

--- Crear un indice agrupado con almacenamiento de columnas

CREATE CLUSTERED COLUMNSTORE IXCS_Directorio
ON Directorio (Id, Name);

Indices No-Agrupados

Un índice no agrupado es aquel que contiene los valores clave del índice y los localizadores de filas que apuntan a la ubicación de los datos de la tabla en el almacenamiento. Se pueden crear varios índices no agrupados para una tabla o vista indexada. Los índices no agrupados están diseñados para mejorar el rendimiento de las consultas de uso frecuente que no están cubiertas por el índice agrupado.

Es importante tener en cuenta que los índices no agrupados tienen una estructura separada de las filas de datos, por lo que un índice no agrupado contiene los valores clave del índice no agrupado, y cada entrada de valor clave tiene un puntero a la fila de datos que contiene dicho valor clave. El puntero a una fila de índice en un índice no agrupado a una fila de datos se llama localizador de filas. La estructura del llamado localizador de filas depende de cómo se almacenan las páginas de datos en un montón o en una tabla agrupada.

Los índices no agrupados también tienen una estructura de árbol B como los índices agrupados, considerando que las filas de datos de la tabla subyacente no se ordenan ni almacenan en orden de acuerdo con sus claves no agrupadas y que el nivel de hoja de un índice no agrupado está compuesto de páginas de índice en lugar de páginas de datos.

En los índices no agrupados también podemos identificar los siguientes subtipos:

• Índice filtrado (filtered Index): se trata de un índice no agrupado optimizado, especialmente adecuado para admitir consultas que seleccionan de un subconjunto de datos bien definido. Un predicado de filtro se utiliza para indexar solo una parte de las filas de la tabla. Vale la pena señalar que un índice filtrado bien diseñado puede mejorar el rendimiento de la consulta, lo que ayuda a reducir los costos de mantenimiento del índice y a reducir los costos de almacenamiento del índice en comparación con los índices de tabla completa.
• Índice de cobertura (covered index): este tipo de índice puede incluir columnas sin clave en un índice no agrupado y, al mismo tiempo, evita exceder las limitaciones actuales de tamaño de índice con un máximo de 16 columnas de clave y un tamaño máximo de clave de índice de 900 bytes. Generalmente, el Motor de base de datos no considera columnas sin una clave al calcular el número de columnas de clave de índice o el tamaño de la clave de índice. Las columnas sin clave se definen en la cláusula INCLUDE de la instrucción CREATE INDEX.
• Índice de almacenamiento de columnas (columnstore index): una tecnología para almacenar, recuperar y administrar datos mediante el uso de un formato de datos en columnas, denominado almacenamiento de columnas. Cuando se habla de índices de almacenamiento de columnas, los términos almacenamiento de filas y almacenamiento de columnas se utilizan para enfatizar el formato para el almacenamiento de datos. Los índices de almacén de columnas utilizan ambos tipos de almacenamiento.

 

SQL Server Non-Clustered Index Representation

Para la creación de los índices no agrupados, se requiere usar la sentencia CREATE INDEX, a continuación, veremos un ejemplo para cada uno de los subtipos indicados.

--- Indice No Agrupado
CREATE NONCLUSTERED INDEX IX_Directorio_Name 
ON Directorio (Name);

--- Indice No-Agrupado Filtrado
CREATE NONCLUSTERED INDEX IXF_Directorio_Friend 
ON Directorio (Name) 
WHERE Friend = 1;

--- Indice No-Agrupado de Cobertura
CREATE NONCLUSTERED INDEX IXC_Directorio_NameFriend 
WITH (Friend);

ON Directorio (Name) 

--- Indice No-agrupado de almacenamiento de columna
CREATE NONCLUSTER COLUMNSTORE INDEX IXCS_Directorio
ON Directorio (id,name);

Índices únicos

Un índice único en una tabla o vista es aquel en el que no se permite que dos filas tengan el mismo valor de clave de índice. Es importante indicar que un índice agrupado en una vista debe ser único. No se permite la creación de un índice único en columnas que incluyen valores duplicados, independientemente de que la opción IGNORE_DUP_KEY esté establecido en ON o no. Es importante mencionar, si se intenta la creación en una tabla con duplicados en la columna seleccionada, se muestra un mensaje de error. Los valores duplicados deben eliminarse antes de que se pueda crear un índice único en la columna o columnas. Es requerido que las columnas que se utilizan en un índice único deben establecerse en NOT NULL, ya que varios valores nulos se consideran duplicados cuando se crea un índice único.

Es importante indicar que aunque se indica como un tipo diferente a los índices agrupados y no agrupados, este tipo puede ser asociado a los anteriores, en general si no se especifica alguno de los tipos anteriores, por omisión se considera un índice no-agrupado.

Para crear un índice único, se puede utilizar la siguiente sentencia:

--- Crear índice único al crear la tabla
CREATE TABLE Directorio
(
Id int PRIMARY KEY,
Name varchar(100) NOT NULL UNIQUE 
);

--- Crear un índice unico
CREATE UNIQUE INDEX IXU_Directorio_Name 
ON Directorio (Name);

Conclusión

A partir de Microsoft SQL Server 2005 se permitían hasta 249 índices no agrupados por tabla, mientras que a partir de Microsoft SQL Server 2008 se permite hasta 999 índices no agrupados por tabla.

Como se ha visto, los índices son estructuras que apoyan el desempeño de las consultas, la existencia de índices en una base de datos incrementa el tamaño de esta, así que, aunque el máximo numero de índices en una tabla es de 1000, se hace necesario llevar a cabo un análisis de la necesidad y relevancia de los índices que se utilizan. 

Es importante mencionar que existen otros aspectos a considerar cuando se crea un índice, como el factor de relleno, si se utilizara la base de datos tempdb para llevar a cabo el ordenamiento, o si debe ser creado para uso en un grupo de archivos, particiones o usar paralelismo, consideraciones que no se han indicado en esta ocasión. 

SQL Server Comparativo de DBCC para índices y ALTER INDEX

Regeneración y desfragmentación de índices con DBCC y ALTER INDEX

En esta ocasión no continuare con los comandos de consola de base de datos de Microsoft SQL Server (Comandos DBCC), sino que hare un espacio para hablar de los paralelismos y diferencias del uso de los comandos de consola de base de datos relacionados con el mantenimiento de índices y la instrucción ALTER INDEX.

Si bien, ya se ha indicado que los Comandos DBCC relacionados con las actividades de mantenimiento de índices, específicamente DBCC DBREINDEX y DBCC INDEXDEFRAG, pueden aun ser utilizados, también se ha mencionado que debe utilizarse la instrucción ALTER INDEX para llevar a cabo las actividades relacionadas con estas tareas, ya que en un futuro estos comandos serán quitados y dejados de usar en Microsoft SQL Server.

Antes de hablar de la instrucción ALTER INDEX, mencionare algunos paralelismos en los comandos indicados y la instrucción. Tomando como referencia la base de datos AdventureWorks2014, una base de ejemplo y demostraciones muy utilizada para la capacitación. Dicha base de datos la he restaurado en una de las instancias y he encontrado que sus tablas presentan diversos índices con fragmentación.  

Ahora bien, si recordamos que Microsoft SQL Server tiende a mantener los índices de forma automática, cuando se llevan a cabo operaciones de actualización en los datos y estas operaciones pueden hacer que los índices queden dispersos en la base de datos, esto es, se lleve a cabo la fragmentación. Si se define la fragmentación como “La ocurrencia de cuando los índices tienen páginas en las que la ordenación lógica, basada en el valor de clave, no coincide con la ordenación física dentro del archivo de datos”. Se sabe que los índices fragmentados generalmente son la causa de la reducción en el rendimiento de las consultas, lo que conlleva la ralentización de la aplicación.

No es mi intención mencionar en este espacio la forma de detectar la fragmentación de los índices, sin embargo, si mencionaré que una vez que se han encontrado los valores del porcentaje de fragmentación de un índice en una tabla, los criterios para determinar si es mejor reorganizar o reconstruir el índice es la siguiente:

% de fragmentación	Acción correctiva
Frag > 5 and frag < = 30	Reorganizar o desfragmentar
Frag > 30	Reconstruir

Es importante indicar que la regeneración de un índice se puede ejecutar en línea, esto es no es necesario que la base de datos este en modo de mantenimiento, o sin conexión, esto es, en modo de mantenimiento. En cambio, la reorganización de un índice siempre se ejecuta en línea. Para lograr una disponibilidad similar a la opción de reorganización, debe volver a generar los índices en línea.

Pero más allá de las recomendaciones para la determinación que hacer, este documento intenta mostrar las características entre el uso de los comandos de consola de base de datos (DBCC) relacionados con el mantenimiento de índices y la instrucción ALTER INDEX para llevar a cabo estas tareas.
Primero veremos el paralelismo en el comando DBCC DBREINDEX y posteriormente DBCC INDEXDEFRAG, finalmente mencionaré algunas de las características de la instrucción ALTER INDEX.

DBCC DBREINDEX y ALTER INDEX

Se llevó a cabo la revisión de las características de los índices de las tablas de la base de datos mencionada y se encontró que el índice de la tabla denominada Sales.SpecialOfferProduct, tiene un porcentaje de fragmentación de 66.6 y se trata un índice agrupado (clustered index), la tabla cuenta con 538 registros. Utilizaremos primero el comando DBCC DBREINDEX para llevar a cabo la regeneración del índice con el objetivo de disminuir la fragmentación, de esta forma tenemos, pero en este caso especificaremos que se utilice un factor de llenado (fillfactor) de 80%, quedando el comando como se indica a continuación:

USE [AdventureWorks2014];
GO

DBCC DBREINDEX ([Sales.SpecialOfferProduct], PK_SpecialOfferProduct_SpecialOfferID_ProductID, 80);

El comando se efectúa  sin problemas, y se puede comprobar que el índice se ha regenerado para quedar en un porcentaje de fragmentación de 50 y el factor de llenado se ha establecido en 80%. Si bien en esta ocasión, se ha mejorado el  porcentaje de fragmentación, este no se abatió mas dadas las características de la información en la tabla, esto no es un tema que analizaremos en este momento, solo mostraremos los datos obtenidos para llevar a cabo las comparaciones.

Si llevamos a cabo la restauración de la base de datos, para dejarla en su forma original y utilizamos la instrucción ALTER INDEX para generar nuevamente el índice de la siguiente forma:

USE [AdventureWorks2014];

GO

ALTER INDEX PK_SpecialOfferProduct_SpecialOfferID_ProductID ON Sales.SpecialOfferProduct

REBUILD WITH (FILLFACTOR = 80);

Se puede fácilmente comprobar que el índice se ha regenerado y queda con un porcentaje de fragmentación de 50, al igual que el obtenido cuando se llevó a acción con el comando DBCC DBREINDEX.  En estos momentos podemos decir que el uso del comando o de la instrucción permite obtener los mismos resultados.

Ahora bien, después de revisar los demás índices de la tabla, se ha observado que todos presentan un porcentaje de fragmentación mayor a 5 y se ha decidido que todos los índices deben regenerarse, asimismo se ha determinado que todos queden con un factor de llenado de 90%, primero utilizaremos el comando DBCC DBREINDEX de la siguiente forma

USE [AdventureWorks2014];
GO

DBCC DBREINDEX ( [Sales.SpecialOfferProduct], 80);

En este caso se observa que la ejecución del comando no puede llevarse a cabo, ya que se indica el siguiente mensaje de error:

Msg 2560, Level 16, State 9, Line 1

Parameter 2 is incorrect for this DBCC statement.

En el comando solo puede indicarse la recreación de todos los índices, pero no puede indicarse el factor de llenado, ya que éste solo puede especificarse cuando se indica el nombre del índice correspondiente, ahora bien, utilizaremos la instrucción ALTER INDEX para ver cómo se comporta.

USE [AdventureWorks2014];
GO

ALTER INDEX ALL ON Sales.SpecialOfferProduct

REBUILD WITH (FILLFACTOR = 80);

En este caso la instrucción ALTER INDEX sí me permite indicar que se lleve a cabo la regeneración de todos (ALL) los índices de la tabla indicada y además especificar el factor de llenado. En este caso el uso de la instrucción ALTER INDEX tiene ventaja sobre el uso del comando DBCC DBREINDEX.

El comando DBCC DBREINDEX solo permite la regeneración de todos los índices, indicando la tabla, lo cual se lleva a cabo con las características ya establecidas previamente. De la siguiente manera:

USE [AdventureWorks2014];
GO

DBCC DBREINDEX ([Sales.SpecialOfferProduct]);

La instrucción ALTER INDEX queda de la siguiente manera:

USE [AdventureWorks2014];
GO

ALTER INDEX ALL ON Sales.SpecialOfferProduct

REBUILD;

De esta forma, ambos llevan a cabo la tarea obteniendo los mismos resultados. Asimismo la instrucción ALTER INDEX permite indicar algunos otros argumentos entre ellos se pueden indicar:

SORT_IN_TEMPDB = {ON | OFF }	Especifica si se deben almacenar los resultados de ordenación en tempdb. El valor predeterminado es OFF.
ON LINE = {ON | OFF }	Especifica si las tablas subyacentes y los índices asociados están disponibles para realizar consultas y modificar datos durante la operación de índice. El valor predeterminado es OFF.
MAXDOP = max_degree_of_parallelism	Invalida el grado máximo de paralelismo opción de configuración para la duración de la operación de índice.
DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE	Especifica la opción de compresión de datos para el índice, número de partición o intervalo de particiones especificado.

Una de las características principales del uso de la instrucción ALTER INDEX es la de indicar la partición que se desea regenerar, ya que en el comando DBCC DBREINDEX no puede indicarse, esto proporciona una desventaja para el comando, ya que en caso de que sean generadas varias particiones y estas sean, por ejemplo, para mantener información  histórica, las particiones que contienen información antigua no requieren que se regeneren, de esta forma el comando  no es una buena opción. En base a lo anterior, se puede ejecutar la instrucción ALTER INDEX como:

USE [AdventureWorks2014];
GO

ALTER INDEX PK_SpecialOfferProduct_SpecialOfferID_ProductID ON Sales.SpecialOfferProduct

REBUILD PARTITION = 2;

Debe asegurarse de que se trata de una tabla e índices que se encuentran particionados, ya que en caso de que no se tenga una tabla o índice particionado, se presentara un error. Es posible que se indique que se lleve a cabo la regeneración de los índices de todas las particiones, para ello se indica de la siguiente manera:

USE [AdventureWorks2014];
GO

ALTER INDEX ALL ON Sales.SpecialOfferProduct

REBUILD PARTITION = ALL;

Es posible en estos casos utilizar alguno de los argumentos que se han indicado anteriormente para poder efectuar mas opciones en la regeneración de los índices con la instrucción ALTER INDEX que con el uso del comando DBCC DBREINDEX.

DBCC INDEXDEFRAG y ALTER INDEX

Ahora veremos el comportamiento del comando DBCC INDEXDEFRAG contra lo que ofrece la instrucción ALTER INDEX, para la reorganización de los índices de una tabla.  Se llevó a cabo la revisión de los índices de la base de datos que se menciona anteriormente, y se encontró que en la tabla Sales.SalesOrderDetail que cuenta con 121,315 registros, el indice IX_SalesOrderDetail_ProductID tiene un porcentaje de fragmentación de 5.1, justo en el límite para la reorganización, por lo que procedemos a llevarlo a cabo con el comando, quedando:

USE [AdventureWorks2014];

GO

DBCC INDEXDEFRAG(0, [Sales.SalesOrderDetail], IX_SalesOrderDetail_ProductID);

La ejecución de este comando proporciona la siguiente información:

Pages Scanned	Pages Moved	Pages Removed
268	266	0

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Y al revisar el estado del índice, se observa que se ha reducido el porcentaje de fragmentación a 4.4, que si bien, la reducción no es alta, también ofrece una mejora que puede ser importante en el rendimiento de las consultas. Ahora bien, utilizaremos la instrucción ALTER INDEX y veremos cuál es el resultado, para ello restauraremos nuevamente la base de datos, ejecutando la instrucción:

USE [AdventureWorks2014];
GO

ALTER INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail

REORGANIZE;

En este caso, el mensaje que se recibe es el siguiente:

Command(s) completed successfully.

Sin embargo, al revisar nuevamente la información del índice, se observa que se obtienen los mismos resultados que los obtenidos con el comando DBCC INDEXDEFRAG. De tal forma que se puede decir que el uso de cualquiera de los dos nos proporciona las mismas características. Aquí uno se puede preguntar, si en ambos casos obtengo el mismo resultado, ¿no sería mejor que este comando pudiera mantenerse? Veamos algunas otras opciones.

Si bien el comando DBCC INDEXDEFRAG permite indicar que partición se desea reorganizar, como en el siguiente caso:

USE [AdventureWorks2014];
GO

DBCC INDEXDEFRAG(0, [Sales.SalesOrderDetail], IX_SalesOrderDetail_ProductID, 1);

Donde se establece que se desea que tome la partición 1, en este caso la partición 1 o única no tiene problema,  solo debe asegurarse que la tabla contenga particiones y el índice también pertenezca a la partición que se indique.  El resultado de esto es el mismo, se mostrará la información como se indicó anteriormente. Ahora bien, utilizaremos la instrucción ALTER INDEX para validar su comportamiento.

USE [AdventureWorks2014];
GO

ALTER INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail

REORGANIZE PARTITION = 1;

En este caso, como la tabla no está particionada, se presentara un error como el siguiente:

Msg 7729, Level 16, State 1, Line 1

Cannot specify partition number in the alter index statement as the index 'IX_SalesOrderDetail_ProductID' is not partitioned.

En este caso, el uso de ALTER INDEX es mejor ya que ha detectado que la tabla y el índice no se encuentra particionado, a diferencia del comando DBCC INDEXDEFRAG que no detecta y toma como valido el uso de la partición única. Otro de las características del uso de la instrucción ALTER INDEX es el uso de parámetros adicionales, para la reorganización de los índices. Los cuales son:

LOB_COMPACTION = { ON | OFF }	Especifica que se debe compactar todas las páginas que contienen datos de estos tipos de datos de objetos grandes.
COMPRESS_ALL_ROW_GROUPS =  { ON | OFF}	Proporciona una manera para obligar a los grupos de filas delta abierto o cerrado en el COLUMNSTORE. Disponible a partir de Microsoft SQL Server 2016.

Comentarios

Una de las características del cambio entre el uso de comandos DBCC al de la instrucción ALTER INDEX es la posibilidad de manejar opciones adicionales, por ejemplo, el uso del argumento SORT_IN_TEMPDB en la regeneración de un indice, permite mejorar el desempeño de la tarea ya que se toma espacio del disco donde se encuentra la base de datos tempdb para las actividades. Otro ejemplo es el uso de la regeneración de índices en particiones, con diferentes características, que no puede llevarse a cabo con el comando DBCC DBREINDEX, es otra de las ventajas.

Si bien las opciones del uso de la instrucción ALTER INDEX, no se limitan a la reorganización o regeneración de los índices, ya que nos permite deshabilitar,  o establece diversas características adicionales a los índices, son cosas que nos permiten determinar que es mejor usar ALTER INDEX que los comandos DBCC relacionados con los índices.

Microsoft ya ha mencionado que es recomendable el uso de ALTER INDEX sobre DBCC DBREINDEX y DBCC INDEXDEFRAG, y después de haber analizado los resultados, coincidimos en ello.