CREATE INDEX (Transact-SQL)

Criar um índice clusterizado em uma tabela (heap) ou descartar e recriar um índice clusterizado existente requer espaço de trabalho adicional disponível no banco de dados, para acomodar a classificação de dados e uma cópia temporária da tabela original ou dos dados do índice clusterizado existente. Para obter mais informações, consulte Determinando requisitos de espaço em disco de índice. Para obter mais informações sobre índices clusterizados, consulte Criando índices clusterizados.

Quando existe um índice exclusivo, o Mecanismo de Banco de Dados verifica se há valores duplicados sempre que dados são adicionados por operações de inserção. As operações de inserção que geram valores de chave duplicados são revertidas, e o Mecanismo de Banco de Dados exibe uma mensagem de erro. Isso acontece mesmo que a operação de inserção altere muitas linhas, mas crie apenas uma duplicata. Se for feita uma tentativa de inserir dados para os quais há um índice exclusivo e a cláusula IGNORE_DUP_KEY for definida como ON, somente as linhas que violarem o índice UNIQUE falharão. Para obter mais informações sobre índices exclusivos, consulte Criando índices exclusivos.

Índices particionados são criados e mantidos de uma maneira semelhante às tabelas particionadas, mas, como índices comuns, são tratados como objetos de banco de dados separados. É possível haver um índice particionado em uma tabela não particionada, bem como é possível ter um índice não particionado em uma tabela particionada.

Se você estiver criando um índice em uma tabela particionada e não especificar um grupo de arquivos para colocar o índice, ele será particionado da mesma maneira que a tabela subjacente. Isso ocorre porque, por padrão, os índices são colocados nos mesmos grupos de arquivos que suas tabelas subjacentes e, para uma tabela particionada, no mesmo esquema de partição que usa as mesmas colunas de particionamento.

Ao particionar um índice clusterizado não exclusivo, por padrão, o Mecanismo de Banco de Dados adiciona quaisquer colunas de particionamento à lista de chaves de índice clusterizado, se já não estiverem especificadas.

As exibições indexadas podem ser criadas em tabelas particionadas da mesma maneira que os índices em tabelas. Para obter mais informações sobre índices particionados, consulte Tabelas e índices particionados.

Criar um índice clusterizado exclusivo em uma exibição melhora o desempenho da consulta porque a exibição é armazenada no banco de dados da mesma forma que uma tabela com um índice clusterizado é armazenada. O otimizador de consulta pode usar exibições indexadas para acelerar a execução da consulta. A exibição não precisa estar referenciada na consulta para o otimizador considerá-la para uma substituição.

As etapas a seguir são necessárias para criar uma exibição indexada e são essenciais para o êxito da implementação da exibição:

1. Verifique se as opções SET estão corretas para todas as tabelas existentes que serão referenciadas na exibição.

2. Verifique se as opções SET da sessão estão definidas corretamente antes de criar qualquer tabela nova e a exibição.

3. Verifique se a definição de exibição é determinística.

4. Crie a exibição usando a opção WITH SCHEMABINDING.

5. Crie o índice clusterizado exclusivo na exibição.

Opções SET necessárias para exibições indexadas

A avaliação da mesma expressão poderá produzir resultados diferentes no Mecanismo de Banco de Dados se houver opções SET diferentes ativas quando a consulta for executada. Por exemplo, depois que a opção SET CONCAT_NULL_YIELDS_NULL for definida como ON, a expressão 'abc' + NULL retornará o valor NULL. Entretanto, depois que CONCAT_NULL_YIEDS_NULL for definido como OFF, a mesma expressão produzirá 'abc'.

Para verificar se as exibições podem ser mantidas corretamente e retornar resultados consistentes, as exibições indexadas requerem valores fixos para várias opções SET. As opções SET da tabela a seguir devem ser definidas com os valores mostrados na coluna Valor necessário sempre que ocorrerem as seguintes condições:

• A exibição indexada é criada.

• Houver qualquer operação de inserção, atualização ou exclusão executada em qualquer tabela que participe da exibição indexada. Isso inclui operações como cópia em massa, replicação e consultas distribuídas.

• A exibição indexada for usada pelo otimizador de consulta para produzir o plano de consulta.

  Opções SET	Valor necessário	Valor do servidor padrão	Padrão OLE DB e ODBC padrão	Padrão Valor 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

  *A definição de ANSI_WARNINGS como ON definirá ARITHABORT implicitamente como ON quando o nível de compatibilidade do banco de dados estiver definido como 90 ou mais. Se o nível de compatibilidade do banco de dados estiver definido como 80 ou menos, a opção ARITHABORT deverá ser definida explicitamente como ON.

Se você estiver usando uma conexão de servidor OLE DB ou ODBC, o único valor que deve ser modificado é a configuração ARITHABORT. Todos os valores DB-Library devem ser definidos corretamente no nível do servidor usando sp_configure ou no aplicativo usando o comando SET. Para obter mais informações sobre as opções SET, consulte Usando as opções no SQL Server.

Importante
É altamente recomendável que a opção de usuário ARITHABORT seja definida em todo o servidor como ON assim que a primeira exibição indexada, ou índice, em uma coluna computada for criada em qualquer banco de dados no servidor.

Funções determinísticas

Requisitos adicionais

Índice filtrado é um índice não clusterizado otimizado, adequado a consultas que selecionam uma pequena porcentagem de linhas de uma tabela. Ele usa um predicado de filtro para indexar uma parte dos dados na tabela. Um índice filtrado bem projetado pode melhorar o desempenho da consulta, além de reduzir custos de armazenamento e de manutenção.

Opções SET necessárias para índices filtrados

Para obter mais informações sobre índices espaciais, consulte CREATE SPATIAL INDEX (Transact-SQL) e Trabalhando com índices espaciais (Mecanismo de Banco de Dados).

Para obter mais informações sobre índices XML, consulte CREATE XML INDEX (Transact-SQL) e Índices em colunas de tipo de dados XML.

O tamanho máximo de uma chave de índice é de 900 bytes. Os índices nas colunas varchar que excederem 900 bytes poderão ser criados se os dados existentes nas colunas não excederem 900 bytes no momento da criação do índice; entretanto, as ações subsequentes de inserção ou atualização nas colunas que fazem com que o tamanho total seja maior que 900 bytes falharão. Para obter mais informações, consulte Tamanho máximo de chaves de índice. A chave de um índice clusterizado não pode conter colunas varchar que tenham dados existentes na unidade de alocação ROW_OVERFLOW_DATA. Se um índice clusterizado for criado em uma coluna varchar e os dados existentes estiverem na unidade de alocação IN_ROW_DATA, ocorrerá falha nas ações subseqüentes de inserção ou atualização na coluna que envia os dados da linha por push. Para obter mais informações sobre unidades de alocação, consulte Organização de tabela e índice.

Índices não clusterizados podem incluir colunas não chave no nível folha do índice. Essas colunas não são consideradas pelo Mecanismo de Banco de Dados ao calcular o tamanho da chave de índice. Para obter mais informações, consulte Índice com colunas incluídas.

Observação

Quando as tabelas são particionadas, se as colunas de chave de particionamento não estiverem presentes em um índice clusterizado não exclusivo, elas são adicionadas ao índice pelo Mecanismo de Banco de Dados. O tamanho combinado das colunas indexadas (sem contar as colunas incluídas), mais as colunas de particionamento adicionadas, não pode exceder 1.800 bytes em um índice clusterizado não exclusivo.

Os índices podem ser criados em colunas computadas. Além disso, as colunas computadas podem ter a propriedade PERSISTED. Isso significa que o Mecanismo de Banco de Dados armazena os valores computados na tabela e os atualiza quando as outras colunas das quais a coluna computada depende são atualizadas. O Mecanismo de Banco de Dados usa esses valores persistentes ao criar um índice na coluna e quando o índice é referenciado em uma consulta.

Uma coluna computada deve ser determinística e precisa para ser indexada. Entretanto, o uso da propriedade PERSISTED expande o tipo das colunas computadas indexáveis para incluir:

• Colunas computadas baseadas nas funções Transact-SQL e CLR, e métodos de tipo CLR definidos pelo usuário que são marcados como determinísticos pelo usuário.

• Colunas computadas baseadas em expressões que são determinísticas, conforme definidas pelo Mecanismo de Banco de Dados, mas imprecisas.

As colunas computadas persistentes requerem que as seguintes opções SET sejam definidas como mostrado na seção anterior "Opções SET necessárias para exibições indexadas".

A restrição UNIQUE ou PRIMARY KEY pode conter uma coluna computada contanto que satisfaça todas as condições para indexação. Especificamente, a coluna computada deve ser determinística e precisa ou determinística e persistente. Para obter mais informações sobre determinismo, consulte Funções determinísticas e não determinísticas.

As colunas computadas derivadas de tipos de dados image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml podem ser indexadas tanto como coluna de chave ou como coluna não chave incluída, desde que o tipo de dados da coluna computada seja permitido como uma coluna de chave de índice ou coluna não chave. Por exemplo, não é possível criar um índice XML primário em uma coluna xml computada. Se o tamanho da chave de índice exceder 900 bytes, uma mensagem de aviso será exibida.

Criar um índice em uma coluna computada pode causar a falha de uma operação de inserção ou atualização que tenha funcionado anteriormente. Essa falha pode ocorrer quando a coluna computada resultar em erro aritmético. Por exemplo, na tabela a seguir, embora a coluna computada c resulte em um erro aritmético, a instrução INSERT funciona.

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

Se, em vez disso, depois de criar a tabela, você criar um índice em uma coluna computada c, a mesma instrução INSERT agora falhará.

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 obter mais informações, consulte Criando índices em colunas computadas.

As colunas não chave, chamadas de colunas incluídas, podem ser adicionadas ao nível folha de um índice não clusterizado para melhorar o desempenho da consulta ao abrangê-la. Isso quer dizer que todas as colunas referenciadas na consulta são incluídas no índice como colunas de chave ou não chave. Isso permite que o otimizador de consulta localize todas as informações necessárias de um exame de índice; os dados de tabela ou de índice clusterizado não são acessados. Para obter mais informações, consulte Índice com colunas incluídas.

O SQL Server 2005 introduziu novas opções de índice e também modifica o modo como as opções são especificadas. Nas sintaxe compatível com versões anteriores, WITH option_name é equivalente a WITH (<option_name> = ON ). Ao definir as opções de índice, aplicam-se as seguintes regras:

• Novas opções de índice só podem ser especificadas usando WITH (option_name = ON | OFF).

• As opções não podem ser especificadas com o uso de sintaxe compatível com versões anteriores e nova sintaxe na mesma instrução. Por exemplo, especificar WITH (DROP_EXISTING, ONLINE = ON) faz a instrução falhar.

• Ao criar um índice XML, as opções devem ser especificadas usando WITH (option_name = ON | OFF).

É possível usar a cláusula DROP_EXISTING para recriar o índice, adicionar ou descartar colunas, modificar opções, modificar a ordem de classificação de colunas, ou alterar o esquema de partição ou o grupo de arquivos.

Se o índice impuser uma restrição PRIMARY KEY ou UNIQUE e a definição de índice não for alterada de alguma maneira, o índice será descartado e recriado preservando a restrição existente. Entretanto, se a definição de índice for alterada, a instrução falhará. Para alterar a definição de uma restrição PRIMARY KEY ou UNIQUE, descarte a restrição e adicione uma restrição com a nova definição.

DROP_EXISTING melhora o desempenho quando você recria um índice clusterizado, com o mesmo conjunto de chaves ou um conjunto diferente, em uma tabela que também tenha índices não clusterizados. DROP_EXISTING substitui a execução de uma instrução DROP_INDEX no índice clusterizado antigo, seguida da execução de uma instrução CREATE_INDEX para o novo índice clusterizado. Os índices não clusterizados são recriados uma vez e, depois disso, somente se a definição de índice for alterada. A cláusula DROP_EXISTING não recria os índices não clusterizados quando a definição de índice tem o mesmo nome de índice, chave e colunas de partição, atributo de exclusividade e ordem de classificação que o índice original.

Os índices não clusterizados podem ser recriados ou não, mas sempre permanecem em seus grupos de arquivos ou esquemas de partição originais e usam as funções de partição originais. Se um índice clusterizado for recriado para um grupo de arquivos ou esquema de partição diferente, os índices não clusterizados não serão movidos para coincidir com o novo local do índice clusterizado. Portanto, mesmo os índices não clusterizados previamente alinhados ao índice clusterizado, podem não estar mais alinhados a ele. Para obter mais informações sobre alinhamento de índices particionados, consulte Diretrizes especiais para índices particionados.

A cláusula DROP_EXISTING não classificará os dados novamente se as mesmas colunas de chave de índice forem usadas na mesma ordem e com a mesma ordem crescente ou decrescente, a menos que a instrução de índice especifique um índice não clusterizado e a opção ONLINE seja definida como OFF. Se o índice clusterizado for desabilitado, a operação CREATE INDEX WITH DROP_EXISTING deverá ser executada com ONLINE definido como OFF. Se um índice não clusterizado for desabilitado e não for associado a um índice clusterizado desabilitado, a operação CREATE INDEX WITH DROP_EXISTING poderá ser executada com ONLINE definido como OFF ou ON.

Quando índices com 128 extensões ou mais são descartados ou recriados, o Mecanismo de Banco de Dados adia as desalocações de página atuais e seus bloqueios associados até depois da confirmação da transação. Para obter mais informações, consulte Descartando e recriando objetos grandes.

As diretrizes a seguir são aplicáveis ao executar operações de índice online:

• A tabela subjacente não pode ser alterada, truncada ou descartada quando uma operação de índice online estiver em andamento.

• É necessário espaço em disco temporário adicional durante a operação de índice. Para obter mais informações, consulte Determinando requisitos de espaço em disco de índice.

• As operações online podem ser executadas em índices particionados e índices que contenham colunas computadas ou colunas incluídas persistentes.

Para obter mais informações, consulte Executando operações de índice online.

Quando ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, os bloqueios em nível de linha, página e tabela são permitidos ao acessar o índice. O Mecanismo de Banco de Dados escolhe o bloqueio apropriado e pode escalar o bloqueio de uma linha ou página para um bloqueio de tabela. Para obter mais informações, consulte Escalonamento de bloqueios (Mecanismo de Banco de Dados).

Quando ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, somente um bloqueio em nível de tabela é permitido ao acessar o índice.

Para obter mais informações sobre como configurar a granularidade de bloqueio de um índice, consulte Personalizando bloqueio de um índice.

Para retornar informações sobre índices, é possível usar exibições do catálogo, funções do sistema e procedimentos armazenados do sistema. Para obter mais informações, consulte Exibindo informações de índice.

A compactação de dados é descrita no tópico Criando tabelas e índices compactados. A seguir estão os pontos-chave a serem considerados:

• A compactação pode permitir que mais linhas sejam armazenadas em uma página, mas não altera o tamanho máximo da linha.

• Páginas não folha de um índice não são compactadas por página, mas podem ser compactadas por linha.

• Cada índice não clusterizado tem uma configuração de compactação individual e não herda a configuração de compactação da tabela subjacente.

• Quando um índice clusterizado é criado em um heap, ele herda o estado de compactação do heap, a menos que um estado de compactação alternativo seja especificado.

As restrições a seguir se aplicam a índices particionados:

• Não será possível alterar a configuração de compactação de uma única partição se a tabela tiver índices não alinhados.

• A sintaxe ALTER INDEX <índice> ... REBUILD PARTITION ... recria a partição especificada do índice.

• A sintaxe ALTER INDEX <índice> ... REBUILD WITH... recria todas as partições do índice.

Para avaliar como a alteração do estado de compactação afetará uma tabela, um índice ou uma partição, use o procedimento armazenado sp_estimate_data_compression_savings.

O exemplo a seguir cria um índice não clusterizado na coluna BusinessEntityID da tabela 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

O exemplo a seguir cria um índice composto não clusterizado nas colunas SalesQuota e SalesYTD da tabela 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

O exemplo a seguir cria um índice não clusterizado exclusivo na coluna Name da tabela Production.UnitMeasure. O índice imporá a exclusividade dos dados inseridos na coluna 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

A consulta a seguir testa a restrição de exclusividade tentando inserir uma linha com o mesmo valor que o de uma linha 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());

A mensagem de erro resultante é:

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.

O exemplo a seguir demonstra o efeito da opção IGNORE_DUP_KEY inserindo várias linhas em uma tabela temporária primeiro com a opção definida como ON e, em seguida, com a opção definida como OFF. Uma única linha é inserida na tabela #Test, causando intencionalmente um valor duplicado quando a segunda instrução de várias linhas INSERT for executada. Uma contagem de linhas na tabela retorna o número de linhas inseridas.

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 seguir são apresentados os resultados da segunda instrução INSERT.

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

Number of rows

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

38

Observe que as linhas inseridas da tabela Production.UnitMeasure que não violaram a restrição de exclusividade foram inseridas com êxito. Um aviso foi emitido e a linha duplicada ignorada, mas a transação inteira não foi revertida.

As mesmas instruções são executadas novamente, mas com IGNORE_DUP_KEY definido como 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 seguir são apresentados os resultados da segunda instrução 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 nenhuma linha da tabela Production.UnitMeasure foi inserida na tabela, embora somente uma linha violasse a restrição de índice UNIQUE.

O exemplo a seguir descarta e recria um índice existente na coluna ProductID da tabela Production.WorkOrder usando a opção DROP_EXISTING. As opções FILLFACTOR e PAD_INDEX também são definidas.

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

O exemplo a seguir cria uma exibição e um índice nessa exibição. Duas consultas que usam a exibição indexada são incluídas.

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

O exemplo a seguir cria um índice não clusterizado com uma coluna de chave (PostalCode) e quatro colunas não chave (AddressLine1, AddressLine2, City, StateProvinceID). Uma consulta incluída pelo índice vem em seguida. Para exibir o índice que é selecionado pelo otimizador de consulta, no menu Consulta no SQL Server Management Studio, selecione Exibir Plano de Execução Real antes de executar a 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

O exemplo a seguir cria um índice particionado não clusterizado em TransactionsPS1, um esquema de partição existente. Este exemplo pressupõe que o exemplo de índice particionado tenha sido instalado.

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

O exemplo a seguir cria um índice filtrado na tabela Production.BillOfMaterials. O predicado de filtro pode incluir colunas que não são colunas chave no índice filtrado. O predicado deste exemplo seleciona apenas as linhas em que EndDate é não 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

O exemplo a seguir cria um índice em uma tabela não particionada usando a compactação de linha.

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

O exemplo a seguir cria um índice em uma tabela particionada usando a compactação de linha em todas as partições do índice.

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

O exemplo a seguir cria um índice em uma tabela particionada usando a compactação de página na partição 1 do índice e a compactação de linha nas partições 2 a 4 do í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