CREATE INDEX (Transact-SQL)

La creación de un índice clúster en una tabla (montón) o la eliminación y nueva creación de un índice clúster existente requiere área de espacio adicional disponible en la base de datos para acomodar la ordenación de datos y una copia temporal de la tabla original o datos del índice clúster existente. Para obtener más información, vea Determinar requisitos de espacio en disco del índice. Para obtener más información sobre los índices clúster, vea Crear ndices clúster.

Cuando existe un índice único, Motor de base de datos comprueba si hay valores duplicados cada vez que se agregan datos con una operación de inserción. Las operaciones de inserción que generarían valores de clave duplicados se revierten y el Motor de base de datos muestra un mensaje de error. Esto se cumple incluso si la operación de inserción cambia muchas filas pero crea un único duplicado. Si se intenta indicar datos donde existe un índice único y se ha especificado la cláusula IGNORE_DUP_KEY en ON, solo causarán un error las filas que infrinjan el índice UNIQUE. Para obtener más información acerca de los índices únicos, vea Crear índices únicos.

La creación y el mantenimiento de los índices con particiones son similares a los de las tablas con particiones pero, al igual que en índices ordinarios, éstos son tratados como objetos de base de datos independientes. Puede tener un índice con particiones en una tabla que carezca de particiones, y puede tener un índice sin particiones en una tabla que tenga particiones.

Si crea un índice en una tabla con particiones y no especifica un grupo de archivos en el que desea ubicar el índice, se crean particiones en el índice de la misma manera que en la tabla subyacente. Esto se debe a que, de manera predeterminada, los índices se ubican en los mismos grupos de archivos que las tablas subyacentes, y en una tabla con particiones del mismo esquema de partición que utiliza las mismas columnas de partición.

Cuando se crean particiones en un índice clúster no único, Motor de base de datos agrega, de forma predeterminada, las columnas de partición en la lista de claves del índice clúster, en caso de que no se hubieran especificado aún.

Se pueden crear vistas indizadas en tablas con particiones de la misma manera que se hace con índices en tablas. Para obtener más información acerca de los índices con particiones, vea Tablas e índices con particiones.

La creación de un índice clúster único en una vista mejora el rendimiento de la consulta porque la vista se almacena en la base de datos de la misma manera que se almacena una tabla con un índice clúster. El optimizador de consultas puede utilizar vistas indizadas para acelerar la ejecución de las consultas. No es necesario hacer referencia a la vista en la consulta para que el optimizador tenga en cuenta esa vista al hacer una sustitución.

Para crear una vista indizada, es necesario seguir los pasos descritos a continuación que son fundamentales para la correcta implementación de la vista:

1. Compruebe que las opciones SET sean correctas para todas las tablas existentes a las que se hará referencia en la vista.

2. Compruebe que las opciones SET de la sesión estén establecidas correctamente antes de crear cualquier tabla nueva y la vista.

3. Compruebe que la definición de vista sea determinista.

4. Cree la vista mediante la opción WITH SCHEMABINDING.

5. Cree el índice clúster único en la vista.

Opciones SET requeridas para vistas indizadas

La evaluación de la misma expresión puede producir resultados diferentes en Motor de base de datos si hay diferentes opciones SET activas cuando se ejecuta la consulta. Por ejemplo, después de establecer la opción SET CONCAT_NULL_YIELDS_NULL en ON, la expresión 'abc' + NULL devuelve el valor NULL. Sin embargo, después de establecer CONCAT_NULL_YIEDS_NULL en OFF, la misma expresión produce 'abc'.

Para asegurar el correcto mantenimiento de las vistas y la generación de resultados coherentes, las vistas indizadas requieren valores fijos para varias opciones SET. Las opciones SET de la tabla siguiente se deben establecer según los valores mostrados en la columna Valor obligatorio cuando se producen las siguientes condiciones:

• Se crea la vista indizada.

• Se realiza una operación de inserción, actualización o eliminación en cualquier tabla que participa en la vista indizada. Esto incluye operaciones como copia masiva, replicación y consultas distribuidas.

• El optimizador de consultas utiliza la vista indizada para producir el plan de consulta.

  Opciones SET	Valor requerido	Valor de servidor predeterminado	Valor predeterminado Valor OLE DB y ODBC	Valor predeterminado Valor de DB-Library
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS*	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  *Al establecer ANSI_WARNINGS en ON, ARITHABORT se establece de forma implícita en ON cuando el nivel de compatibilidad de base de datos está establecido en 90 o un valor superior. Si el nivel de compatibilidad de la base de datos está establecido en 80 o en un nivel inferior, debe configurarse explícitamente la opción ARITHABORT en ON.

Si utiliza una conexión de servidor OLE DB u ODBC, el único valor que se debe modificar es la configuración de ARITHABORT. Todos los valores de DB-Library se deben establecer correctamente en el nivel de servidor mediante sp_configure o desde la aplicación utilizando el comando SET. Para obtener más información sobre opciones SET, vea Usar las opciones de SQL Server.

Importante
Se recomienda que la opción de usuario ARITHABORT se establezca en todo el servidor como ON tan pronto como se cree la primera vista indizada o índice en una columna calculada en cualquier base de datos del servidor.

Funciones deterministas

Requisitos adicionales

Un índice filtrado es un índice no clúster optimizado, adecuado para las consultas que seleccionan un porcentaje pequeño de las filas de una tabla. Utiliza un predicado de filtro para indizar una parte de los datos de la tabla. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, reducir los costos de almacenamiento y de mantenimiento.

Opciones SET requeridas para los índices filtrados

Para obtener información sobre índices espaciales, vea CREATE SPATIAL INDEX (Transact-SQL) y Trabajar con índices espaciales (motor de base de datos).

Para obtener información acerca de los índices XML, vea CREATE XML INDEX (Transact-SQL) y Índices en columnas del tipo de datos XML.

El tamaño máximo para una clave de índice es 900 bytes. Se pueden crear índices en las columnas varchar cuyo tamaño sea superior a 900 bytes si los datos que contienen no superan ese tamaño al crearse el índice; sin embargo, se producirá un error en las acciones de inserción o actualización posteriores en las columnas que hagan que el tamaño total sea mayor que 900 bytes. Para obtener más información, vea Tamaño máximo de las claves de índices. La clave de índice de un índice clúster no puede contener columnas varchar que tengan datos existentes en la unidad de asignación ROW_OVERFLOW_DATA. Si un índice clúster se crea en una columna varchar y los datos existentes se encuentran en la unidad de asignación IN_ROW_DATA, las acciones de inserción o actualización siguientes en la columna que provocarán el desbordamiento de los datos de la fila no serán correctas. Para obtener más información acerca de las unidades de asignación, vea Organización de tablas e índices.

Los índices no clúster pueden incluir columnas que no son de clave en el nivel de hoja del índice. Motor de base de datos no tiene en cuenta estas columnas al calcular el tamaño de clave de índice. Para obtener más información, vea Índice con columnas incluidas.

Nota
Cuando se dividen las tablas, si las columnas de clave de la partición no están aún presentes en un índice clúster no único, el Motor de base de datos las agrega al índice. El tamaño combinado de las columnas indizadas (sin contar las columnas incluidas) más cualquier columna de partición agregada no puede exceder 1800 bytes en un índice clúster no único.

Los índices se pueden crear en columnas calculadas. Además, las columnas calculadas pueden tener la propiedad PERSISTED. Esto significa que Motor de base de datos almacena los valores calculados en la tabla y los actualiza cuando se actualiza cualquier otra columna de la que depende la columna calculada. Motor de base de datos utiliza estos valores persistentes cuando crea un índice en la columna y cuando se hace referencia al índice en una consulta.

Para indizar una columna calculada, ésta debe ser determinista y precisa. No obstante, si se usa la propiedad PERSISTED, se amplía el tipo de columnas calculadas indizables para incluir:

• Las columnas calculadas basadas en Transact-SQL, funciones CLR y métodos de tipos definidos por el usuario CLR que el usuario ha marcado como deterministas.

• Las columnas calculadas basadas en expresiones que son deterministas, como se definen en Motor de base de datos, aunque imprecisas.

Las columnas calculadas persistentes requieren que se establezcan las siguientes opciones SET de la manera indicada en la sección anterior, "Opciones SET requeridas para vistas indizadas".

Las restricciones UNIQUE o PRIMARY KEY pueden contener una columna calculada siempre que cumplan con todas las condiciones de creación del índice. En concreto, la columna calculada debe ser determinista y precisa, o determinista y persistente. Para obtener más información acerca del determinismo, vea Funciones deterministas y no deterministas.

Las columnas calculadas derivadas de los tipos de datos image, ntext, text, varchar(max), nvarchar(max), varbinary(max) y xml se pueden indizar como columnas de clave o sin clave incluida, siempre que el tipo de datos de la columna calculada esté disponible como una columna de clave de índice o columna que no es de clave. Por ejemplo, no puede crear un índice XML principal en una columna xml calculada. Si el tamaño de clave de índice supera los 900 bytes, se muestra un mensaje de advertencia.

La creación de un índice en una columna calculada puede producir un error en una operación de inserción o actualización que antes funcionaba. Este error podría ocurrir cuando la columna calculada produce un error aritmético. Por ejemplo, aunque la columna calculada c de la tabla siguiente produzca un error aritmético, la instrucción INSERT funcionará.

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

En cambio, si después de crear la tabla crea un índice en la columna calculada c, la misma instrucción INSERT producirá un error.

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Para obtener más información, vea Crear índices en columnas calculadas.

Las columnas que no son de clave, denominadas columnas incluidas, se pueden agregar en el nivel hoja de un índice no clúster para mejorar el rendimiento de las consultas al cubrir la consulta. Es decir, todas las columnas a las que se hace referencia en la consulta se incluyen en el índice como columnas de clave o que no son de clave. De este modo, el optimizador de consultas puede ubicar toda la información requerida con un examen del índice; no se tiene acceso a los datos de la tabla o del índice clúster. Para obtener más información, vea Índice con columnas incluidas.

SQL Server 2005 incluye opciones de índice nuevas y también modifica el modo en que se especifican las opciones. En la sintaxis compatible con versiones anteriores, WITH option_name es equivalente a WITH ( <option_name> = ON ). Al establecer opciones de índice, se aplican las siguientes reglas:

• Solo se pueden especificar nuevas opciones de índice mediante WITH (option_name = ON | OFF).

• Las opciones no se pueden especificar utilizando la sintaxis compatible con versiones anteriores y la nueva sintaxis en la misma instrucción. Por ejemplo, al especificar WITH (DROP_EXISTING, ONLINE = ON), se genera un error en la instrucción.

• Cuando se crea un índice XML, las opciones se deben especificar mediante WITH (option_name = ON | OFF).

Puede utilizar la cláusula DROP_EXISTING para volver a generar el índice, agregar o quitar columnas, modificar opciones, modificar el criterio de ordenación de las columnas o cambiar el grupo de archivos o el esquema de partición.

Si el índice exige una restricción PRIMARY KEY o UNIQUE, y la definición de índice no se ha modificado en absoluto, se quita el índice y se vuelve a crear conservando la restricción existente. Sin embargo, si se ha modificado la definición de índice, se genera un error en la instrucción. Para cambiar la definición de una restricción PRIMARY KEY o UNIQUE, quite la restricción y agregue una restricción con la nueva definición.

DROP_EXISTING mejora el rendimiento cuando se vuelve a crear un índice clúster (con el mismo conjunto de claves o con uno distinto) en una tabla que también tiene índices no clúster. DROP_EXISTING reemplaza la ejecución de una instrucción DROP INDEX en el antiguo índice clúster seguida de la ejecución de una instrucción CREATE INDEX para el nuevo índice clúster. Los índices no clúster se vuelven a generar una vez, siempre que la definición de índice haya cambiado. La cláusula DROP_EXISTING no vuelve a generar los índices no clúster cuando la definición de índice posee los mismos nombres de índice, clave y columnas de partición, atributo de unicidad y criterio de ordenación que el índice original.

Independientemente de si se vuelven a generar o no los índices no clúster, éstos siempre permanecen en sus esquemas de partición o grupos de archivos originales, y utilizan las funciones de partición originales. Si un índice clúster se vuelve a generar en un esquema de partición o grupo de archivos diferente, los índices no clúster no se mueven para coincidir con la nueva ubicación del índice clúster. Por lo tanto, es posible que incluso los índices no clúster alineados previamente con el índice clúster no se puedan alinear con éste. Para obtener más información sobre la alineación de índices con particiones, vea Directrices especiales para índices con particiones.

La cláusula DROP_EXISTING no volverá a ordenar los datos si se utilizan las mismas columnas de clave de índice en el mismo orden y con la misma disposición ascendente o descendente, a menos que la instrucción del índice especifique un índice no clúster y la opción ONLINE se establezca en OFF. Si se deshabilita el índice clúster, se debe establecer ONLINE en OFF para la operación CREATE INDEX WITH DROP_EXISTING. Si se deshabilita un índice no clúster y no se asocia con un índice clúster deshabilitado, se puede establecer ONLINE en OFF u ON para la operación CREATE INDEX WITH DROP_EXISTING.

Cuando se quitan o vuelven a generar índices con 128 o más extensiones, Motor de base de datos aplaza las cancelaciones de asignación de página reales y los bloqueos asociados, hasta después de que se confirme la transacción. Para obtener más información, vea Quitar y volver a generar objetos grandes.

Las directrices siguientes se aplican para el desarrollo de operaciones de índice en línea:

• La tabla subyacente no se podrá alterar, truncar ni quitar mientras haya una operación de índice en línea en curso.

• La operación de índice requiere un espacio en disco temporal adicional. Para obtener más información, vea Determinar requisitos de espacio en disco del índice.

• Las operaciones en línea se pueden realizar en índices con particiones e índices que contienen columnas calculadas persistentes, o columnas incluidas.

Para obtener más información, vea Realizar operaciones de índices en línea.

Si ALLOW_ROW_LOCKS = ON y ALLOW_PAGE_LOCK = ON, se permiten los bloqueos de nivel de fila, página y tabla cuando se tiene acceso al índice. Motor de base de datos elige el bloqueo apropiado y puede cambiar de escala el bloqueo desde un bloqueo de fila o página a un bloqueo de tabla. Para obtener más información, vea Concentración de bloqueos (motor de base de datos).

Si ALLOW_ROW_LOCKS = OFF y ALLOW_PAGE_LOCK = OFF, solo se permiten los bloqueos de nivel de tabla cuando se tiene acceso al índice.

Para obtener más información sobre la configuración de la granularidad del bloqueo de un índice, vea Personalizar el bloqueo de un índice.

Para devolver información sobre índices, puede utilizar vistas de catálogo, funciones del sistema y procedimientos almacenados del sistema. Para obtener más información, vea Ver información de índice.

La compresión de datos se describe en el tema Crear tablas e índices comprimidos. A continuación se muestran los puntos clave que se deben tener en cuenta:

• La compresión puede permitir que se almacenen más filas en una página, pero no cambia el tamaño máximo de la fila.

• Las páginas no hoja de un índice no tienen compresión de página pero pueden tener compresión de fila.

• Cada índice no clúster tiene una configuración de compresión individual y no hereda la configuración de compresión de la tabla subyacente.

• Cuando se crea un índice clúster en un montón, el índice clúster hereda el estado de compresión del montón, a menos que se especifique otro estado de compresión.

Las restricciones siguientes se aplican a los índices con particiones:

• No se puede cambiar la configuración de compresión de una partición única si la tabla tiene índices no alineados.

• La sintaxis ALTER INDEX <index> ... REBUILD PARTITION ... vuelve a generar la partición especificada del índice.

• La sintaxis ALTER INDEX <index> ... REBUILD WITH ... vuelve a generar todas las particiones del índice.

Para evaluar cómo afecta el cambio el estado de compresión a una tabla, índice o partición, utilice el procedimiento almacenado sp_estimate_data_compression_savings.

El ejemplo siguiente crea un índice no clúster en la columna BusinessEntityID de la tabla Purchasing.ProductVendor.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (BusinessEntityID); 
GO

El ejemplo siguiente crea un índice compuesto no agrupado en las columnas SalesQuota y SalesYTD de la tabla Sales.SalesPerson.

USE AdventureWorks2008R2
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

El ejemplo siguiente crea un índice no clúster único en la columna Name de la tabla Production.UnitMeasure. El índice exigirá unicidad en los datos insertados en la columna Name.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

La consulta siguiente prueba la restricción de unicidad al intentar insertar una fila con el mismo valor que el de una fila existente.

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
    VALUES ('OC', 'Ounces', GetDate());

El mensaje de error resultante es:

Server: Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

El ejemplo siguiente muestra el efecto de la opción IGNORE_DUP_KEY al insertar varias filas en una tabla temporal primero con la opción establecida en ON y luego con la opción establecida en OFF. Se inserta una única fila en la tabla #Test que intencionadamente proporcionará un valor duplicado cuando se ejecuta la segunda instrucción INSERT de varias filas. Un recuento de las filas de la tabla devuelve el número de filas insertadas.

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

A continuación se muestran los resultados de la segunda instrucción INSERT.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows

--------------

38

Observe que las filas insertadas desde la tabla Production.UnitMeasure que no infringieron la restricción de unicidad se insertaron correctamente. Se emitió una advertencia y se omitió la fila duplicada, pero no se revirtió la transacción completa.

Las mismas instrucciones se ejecutan nuevamente, pero con IGNORE_DUP_KEY establecido en OFF.

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

A continuación se muestran los resultados de la segunda instrucción INSERT.

Server: Msg 2601, Level 14, State 1, Line 5

Cannot insert duplicate key row in object '#Test' with unique index

'AK_Index'. The statement has been terminated.

Number of rows

--------------

1

Observe que ninguna de las filas de la tabla Production.UnitMeasure se insertó en la tabla aunque solo una fila de la tabla infringió la restricción de índice UNIQUE.

El ejemplo siguiente quita y vuelve a crear un índice existente en la columna ProductID de la tabla Production.WorkOrder utilizando la opción DROP_EXISTING. También se establecen las opciones FILLFACTOR y PAD_INDEX.

USE AdventureWorks2008R2;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

Este ejemplo siguiente crea una vista y un índice en esa vista. Se incluyen dos consultas que utilizan la vista indizada.

USE AdventureWorks2008R2;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

El ejemplo siguiente crea un índice no clúster con una columna de clave (PostalCode) y cuatro columnas que no son de clave (AddressLine1, AddressLine2, City, StateProvinceID). A continuación se presenta una consulta cubierta por el índice. Para mostrar el índice seleccionado con el optimizador de consultas, en el menú Consulta de SQL Server Management Studio, seleccione Mostrar plan de ejecución estimado antes de ejecutar la consulta.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
    ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

El ejemplo siguiente crea un índice no agrupado con particiones en TransactionsPS1, un esquema de partición existente. En este ejemplo se supone que se ha instalado el ejemplo de índice con particiones.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

En el ejemplo siguiente se crea un índice filtrado en la tabla Production.BillOfMaterials. El predicado de filtro puede incluir columnas que no son columnas de clave en el índice filtrado. El predicado de este ejemplo selecciona solo las filas en que EndDate no es NULL.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

En el ejemplo siguiente se crea un índice en una tabla sin particiones utilizando la compresión de fila.

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

En el ejemplo siguiente se crea un índice en una tabla con particiones utilizando la compresión de fila en todas las particiones del índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

En el ejemplo siguiente se crea un índice en una tabla con particiones utilizando la compresión de página en la partición 1 del índice y la compresión de fila en las particiones 2 a 4 del índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO