CREATE INDEX (Transact-SQL)

Cria um índice relacional em uma tabela ou exibição especificada de uma tabela especificada. Um índice pode ser criado antes que haja dados na tabela. Os índices relacionais podem ser criados em tabelas ou exibições de outro banco de dados com a especificação de um nome de banco de dados qualificado.

Observação
Para obter informações sobre como criar um índice XML, consulte CREATE XML INDEX (Transact-SQL). Para obter informações sobre como criar um índice espacial, consulte CREATE SPATIAL INDEX (Transact-SQL).

Convenções de sintaxe Transact-SQL

Sintaxe

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<range> ::= 
<partition_number_expression> TO <partition_number_expression>


Backward Compatible Relational Index
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
    table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

Argumentos

• UNIQUE
  Cria um índice exclusivo em uma tabela ou exibição. Um índice exclusivo é aquele no qual duas linhas não podem ter o mesmo valor de chave de índice. Um índice clusterizado em uma exibição deve ser exclusivo.

  O Mecanismo de Banco de Dados não permite a criação de um índice exclusivo em colunas que já contêm valores duplicados, independentemente de IGNORE_DUP_KEY estar definido como ON. Se isso for tentado, o Mecanismo de Banco de Dados exibirá uma mensagem de erro. Valores duplicados devem ser removidos para que um índice exclusivo possa ser criado em uma ou mais colunas. As colunas usadas em um índice exclusivo devem ser definidas como NOT NULL, pois vários valores nulos serão considerados duplicatas quando um índice exclusivo for criado.

• CLUSTERED
  Cria um índice no qual a ordem lógica dos valores de chave determina a ordem física das linhas correspondentes em uma tabela. O nível inferior, ou folha, do índice clusterizado contém as linhas de dados reais da tabela. Uma tabela ou exibição pode ter um índice clusterizado por vez. Para obter mais informações, consulte Estruturas de índice clusterizado.

  Uma exibição com um índice clusterizado exclusivo é chamada de exibição indexada. Criar um índice clusterizado exclusivo em uma exibição materializa fisicamente a exibição. Um índice clusterizado exclusivo deve ser criado em uma exibição para que qualquer outro índice possa ser definido na mesma exibição. Para obter mais informações, consulte Projetando exibições indexadas.

  Crie o índice clusterizado antes de criar quaisquer índices não clusterizados. Os índices não clusterizados existentes nas tabelas serão recriados quando um índice clusterizado for criado.

  Se CLUSTERED não for especificado, um índice não clusterizado será criado.

  Observação

  Como o nível folha de um índice clusterizado e as páginas de dados são os mesmos por definição, criar um índice clusterizado e usar a cláusula ON partition_scheme_name ou ON filegroup_name efetivamente move uma tabela do grupo de arquivos em que a tabela foi criada para o novo esquema de partição ou grupo de arquivos. Antes de criar tabelas ou índices em grupos de arquivos específicos, verifique quais grupos de arquivos estão disponíveis e se eles têm espaço vazio suficiente para o índice. Para obter mais informações, consulte Determinando requisitos de espaço em disco de índice.

• NONCLUSTERED
  Cria um índice que especifica a ordenação lógica de uma tabela. Com um índice não clusterizado, a ordem física das linhas de dados é independente de sua ordem indexada. Para obter mais informações, consulte Estruturas de índice não clusterizado.

  Cada tabela pode ter até 999 índices não clusterizados, independentemente de como eles são criados: seja implicitamente com as restrições PRIMARY KEY e UNIQUE ou explicitamente com CREATE INDEX.

  Para exibições indexadas, os índices não clusterizados podem ser criados somente em uma exibição que tenha um índice clusterizado exclusivo já definido.

  O padrão é NONCLUSTERED.

• index_name
  É o nome do índice. Os nomes de índice devem ser exclusivos em uma tabela ou exibição, mas não precisam ser exclusivos no banco de dados. Nomes de índice devem seguir as regras de identificadores.

• column
  É a coluna, ou colunas, em que o índice se baseia. Especifique dois ou mais nomes de coluna para criar um índice composto com os valores combinados das colunas especificadas. Liste as colunas que serão incluídas no índice composto, em ordem de prioridade de classificação, entre parênteses depois de table_or_view_name.

  Até 16 colunas podem ser combinadas em uma única chave de índice composto. Todas as colunas de uma chave de índice composto devem estar na mesma tabela ou exibição. O tamanho máximo permitido dos valores de índice combinados é de 900 bytes.

  As colunas de tipos de dados LOB (objeto grande) ntext, text, varchar(max), nvarchar(max), varbinary(max), xml ou image não podem ser especificadas como colunas de chave de um índice. Além disso, uma definição de exibição não pode incluir colunas ntext, text ou image, mesmo que elas não sejam referenciadas na instrução CREATE INDEX.

  É possível criar índices em colunas do tipo CLR definido pelo usuário se o tipo der suporte à ordenação binária. Também é possível criar índices em colunas computadas definidas como invocações de método de uma coluna de tipo definido pelo usuário, desde que os métodos sejam marcados como determinísticos e não executem operações de acesso aos dados. Para obter mais informações sobre como indexar colunas do tipo CLR definido pelo usuário, consulte Tipos CLR definidos pelo usuário.

• [ ASC | DESC]
  Determina a direção de classificação crescente ou decrescente da coluna de índice específica. O padrão é ASC.

• INCLUDE **(**column [ ,... n ] )
  Especifica as colunas não chave a serem adicionadas ao nível folha do índice não clusterizado. O índice não clusterizado pode ser exclusivo ou não exclusivo.

  Os nomes de coluna não podem ser repetidos na lista INCLUDE e não podem ser usados simultaneamente como colunas de chave e não chave. Índices não clusterizados sempre conterão as colunas de índice clusterizado se um índice clusterizado for definido na tabela. Para obter mais informações, consulte Índice com colunas incluídas.

  São permitidos todos os tipos de dados, exceto text, ntext e image. O índice deverá ser criado ou recriado offline (ONLINE = OFF) se qualquer uma das colunas não chave especificadas for dos tipos de dados varchar(max), nvarchar(max) ou varbinary(max).

  As colunas computadas que são determinísticas e precisas ou imprecisas podem ser colunas incluídas. Colunas computadas derivadas dos tipos de dados image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml podem ser incluídas em colunas não chave, contanto que os tipos de dados da coluna computada sejam permitidos como uma coluna incluída. Para obter mais informações, consulte Criando índices em colunas computadas.

  Para obter informações sobre como criar um índice XML, consulte CREATE XML INDEX (Transact-SQL).

• WHERE <filter_predicate>
  Cria um índice filtrado especificando quais linhas serão incluídas nele. O índice filtrado deve ser um índice não clusterizado em uma tabela. Cria estatísticas filtradas para as linhas de dados no índice filtrado.

  O predicado de filtro usa a lógica de comparação simples e não pode fazer referência a uma coluna computada, uma coluna UDT, uma coluna de tipo de dados espacial ou uma coluna de tipo de dados hierarchyID. As comparações que usam literais NULL não são permitidas com os operadores de comparação. Use os operadores IS NULL e IS NOT NULL em seu lugar.

  Estes são alguns exemplos de predicados de filtro para a tabela Production.BillOfMaterials:

  WHERE StartDate > '20040101' AND EndDate <= '20040630'

  WHERE ComponentID IN (533, 324, 753)

  WHERE StartDate IN ('20040404', '20040905') AND EndDate IS NOT NULL

  Índices filtrados não se aplicam a índices XML e índices de texto completo. Para índices UNIQUE, somente as linhas selecionadas devem ter valores de índice exclusivo. Índices filtrados não permitem a opção IGNORE_DUP_KEY.

• ON partition_scheme_name**(column_name)**
  Especifica o esquema de partição que define os grupos de arquivos nos quais as partições de um índice particionado serão mapeadas. O esquema de partição deve existir no banco de dados que está executando CREATE PARTITION SCHEME ou ALTER PARTITION SCHEME. column_name especifica a coluna na qual um índice particionado será particionado. Essa coluna deve corresponder ao tipo de dado, comprimento e precisão do argumento da função de partição que partition_scheme_name está usando. column_name não está restrito às colunas na definição de índice. Qualquer coluna da tabela base pode ser especificada, exceto ao se particionar um índice UNIQUE, column_name deve ser escolhido entre os usados como chave exclusiva. Essa restrição permite ao Mecanismo de Banco de Dados verificar a exclusividade de valores de chave em uma única partição apenas.

  Observação

  Ao particionar um índice clusterizado não exclusivo, por padrão, o Mecanismo de Banco de Dados adiciona a coluna de particionamento à lista de chaves de índices clusterizados, se ela já não estiver especificada. Ao particionar um índice não clusterizado e não exclusivo, o Mecanismo de Banco de Dados adiciona a coluna de particionamento como uma coluna não chave (incluída) do índice, se ela já não estiver especificada.

  Se partition_scheme_name ou filegroup não for especificado e a tabela for particionada, o índice será colocado no mesmo esquema de partição, usando a mesma coluna de particionamento que a tabela subjacente.

  Observação
  Não é possível especificar um esquema de particionamento em um índice XML. Se a tabela base for particionada, o índice XML usará o mesmo esquema de partição que a tabela. Para obter informações sobre como criar um índice XML, consulte CREATE XML INDEX (Transact-SQL).

  Para obter mais informações sobre como particionar índices, consulte Diretrizes especiais para índices particionados.

• ON filegroup_name
  Cria o índice especificado no grupo de arquivos especificado. Se nenhum local for especificado e a tabela ou exibição não for particionada, o índice usará o mesmo grupo de arquivos que a tabela ou exibição subjacente. O grupo de arquivos já deve existir.

• ON "default"
  Cria o índice especificado no grupo de arquivos padrão.

  Nesse contexto, default não é uma palavra-chave. É um identificador para o grupo de arquivos padrão e deve ser delimitado, como em ON "default" ou ON [default]. Se "default" for especificado, a opção QUOTED_IDENTIFIER deverá ser definida como ON para a sessão atual. Essa é a configuração padrão. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

• [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
  Especifica a colocação de dados FILESTREAM para a tabela quando um índice clusterizado é criado. A cláusula FILESTREAM_ON permite mover os dados FILESTREAM para outro grupo de arquivos ou esquema de partição FILESTREAM.

  filestream_filegroup_name é o nome de um grupo de arquivos FILESTREAM. O grupo de arquivos deve ter um arquivo definido para o grupo de arquivos usando uma instrução CREATE DATABASE ou ALTER DATABASE; caso contrário, será gerado um erro.

  Se a tabela for particionada, a cláusula FILESTREAM_ON deverá ser incluída e especificar um esquema de partição de grupo de arquivos FILESTREAM que use a mesma função de partição e colunas de partição que o esquema de partição da tabela. Caso contrário, será gerado um erro.

  Se a tabela não estiver particionada, a coluna FILESTREAM não poderá ser particionada. Os dados FILESTREAM da tabela devem ser armazenados em um único grupo de arquivos que é especificado na cláusula FILESTREAM_ON.

  FILESTREAM_ON NULL poderá ser especificado em uma instrução CREATE INDEX se um índice clusterizado estiver sendo criado e a tabela não contiver uma coluna FILESTREAM.

  Para obter uma lista de tópicos FILESTREAM, consulte Projetando e implementando armazenamento FILESTREAM.

<object>::=

É o objeto totalmente qualificado ou não totalmente qualificado que será indexado.

• database_name
  É o nome do banco de dados.

• schema_name
  É o nome do esquema ao qual a tabela ou exibição pertence.

• table_or_view_name
  É o nome da tabela ou exibição que será indexada.

  A exibição deve ser definida com SCHEMABINDING para criar um índice nela. Um índice clusterizado exclusivo deve ser criado em uma exibição antes que qualquer índice não clusterizado seja criado. Para obter mais informações sobre exibições indexadas, consulte a seção Comentários.

<relational_index_option>::=

Especifica as opções a serem usadas ao criar o índice.

• PAD_INDEX = { ON | OFF }
  Especifica o preenchimento do índice. O padrão é OFF.

  • ON
    A porcentagem de espaço livre especificada por fillfactor é aplicada às páginas de nível intermediário do índice.

  • OFF ou fillfactor não está especificado
    As páginas de nível intermediário são preenchidas até próximo de sua capacidade, deixando espaço suficiente para pelo menos uma linha do tamanho máximo que o índice pode ter, considerando o conjunto de chaves em páginas intermediárias.

  A opção PAD_INDEX só é útil quando FILLFACTOR é especificado, porque PAD_INDEX usa a porcentagem especificada por FILLFACTOR. Se a porcentagem especificada para FILLFACTOR não for grande o suficiente para permitir uma linha, o Mecanismo de Banco de Dados substituirá a porcentagem internamente para permitir o valor mínimo. O número de linhas em uma página de índice intermediária nunca é menor do que dois, independentemente de quão baixo seja o valor de fillfactor.

  Na sintaxe compatível com versões anteriores, WITH PAD_INDEX é equivalente a WITH PAD_INDEX = ON.

• FILLFACTOR **=**fillfactor
  Especifica uma porcentagem que indica quanto o Mecanismo de Banco de Dados deve preencher o nível folha de cada página de índice durante a criação ou recriação do índice. fillfactor deve ser um valor inteiro de 1 a 100. Se fillfactor for 100, o Mecanismo de Banco de Dados criará índices com páginas de folha preenchidas até a capacidade máxima.

  A configuração FILLFACTOR se aplica somente quando o índice é criado ou recriado. O Mecanismo de Banco de Dados não mantém dinamicamente a porcentagem especificada de espaço vazio nas páginas. Para exibir a configuração do fator de preenchimento, use a exibição do catálogo sys.indexes.

  Importante
  A criação de um índice clusterizado com FILLFACTOR inferior a 100 afeta a quantidade de espaço de armazenamento ocupado pelos dados, porque o Mecanismo de Banco de Dados redistribui os dados quando cria o índice clusterizado.

  Para obter mais informações, consulte Fator de preenchimento.

• SORT_IN_TEMPDB = { ON | OFF }
  Especifica se os resultados de classificação temporários devem ser armazenados em tempdb. O padrão é OFF.

  • ON
    Os resultados de classificação intermediários usados para criar o índice são armazenados em tempdb. Isso pode reduzir o tempo necessário para criar um índice se tempdb estiver em um conjunto de discos diferente do banco de dados de usuário. Entretanto, isso aumenta a quantidade de espaço em disco usado durante a criação do índice.

  • OFF
    Os resultados de classificação intermediários são armazenados no mesmo banco de dados que o índice.

  Além do espaço necessário no banco de dados de usuário para criar o índice, tempdb deve ter aproximadamente a mesma quantidade de espaço adicional para armazenar os resultados de classificação intermediários. Para obter mais informações, consulte tempdb e criação de índice.

  Na sintaxe compatível com versões anteriores, WITH SORT_IN_TEMPDB é equivalente a WITH SORT_IN_TEMPDB = ON.

• IGNORE_DUP_KEY = { ON | OFF }
  Especifica a resposta de erro quando uma operação de inserção tenta inserir valores da chave duplicada em um índice exclusivo. A opção IGNORE_DUP_KEY aplica-se apenas a operações de inserção depois que o índice é criado ou recriado. A opção não tem nenhum efeito ao executar CREATE INDEX, ALTER INDEX ou UPDATE. O padrão é OFF.

  • ON
    Uma mensagem de aviso ocorrerá quando valores de chave duplicada forem inseridos em um índice exclusivo. Haverá falha somente nas linhas que violarem a restrição de exclusividade.

  • OFF
    Ocorrerá uma mensagem de erro quando os valores de chave duplicados forem inseridos em um índice exclusivo. Toda a operação INSERT será revertida.

  IGNORE_DUP_KEY não pode ser definido como ON para índices criados em uma exibição, índices não exclusivos, índices XML, índices espaciais e índices filtrados.

  Para exibir IGNORE_DUP_KEY, use sys.indexes.

  Na sintaxe compatível com versões anteriores, WITH IGNORE_DUP_KEY é equivalente a WITH IGNORE_DUP_KEY = ON.

• STATISTICS_NORECOMPUTE = { ON | OFF}
  Especifica se as estatísticas de distribuição são recomputadas. O padrão é OFF.

  • ON
    As estatísticas desatualizadas não são recalculadas automaticamente.

  • OFF
    A atualização automática de estatísticas está habilitada.

  Para restaurar a atualização automática de estatísticas, defina STATISTICS_NORECOMPUTE como OFF ou execute UPDATE STATISTICS sem a cláusula NORECOMPUTE.

  Importante
  Desabilitar o recálculo automático de estatísticas de distribuição pode impedir o otimizador de consulta de selecionar planos de execução ideais para consultas que envolvam a tabela.

  Na sintaxe compatível com versões anteriores, WITH STATISTICS_NORECOMPUTE é equivalente a WITH STATISTICS_NORECOMPUTE = ON.

• DROP_EXISTING = { ON | OFF }
  Especifica que o índice nomeado clusterizado ou não clusterizado preexistente é descartado e recriado. O padrão é OFF.

  • ON
    O índice existente é descartado e recriado. O nome de índice especificado deve ser igual ao índice existente atualmente; no entanto, a definição de índice pode ser modificada. Por exemplo, você pode especificar colunas, ordens de classificação, esquemas de partição ou opções de índice diferentes.

  • OFF
    Um erro será exibido se o nome de índice especificado já existir.

  O tipo de índice não pode ser alterado com DROP_EXISTING.

  Na sintaxe compatível com versões anteriores, WITH DROP_EXISTING é equivalente a WITH DROP_EXISTING = ON.

• ONLINE = { ON | OFF }
  Especifica se as tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF.

  Observação
  As operações de índice online estão disponíveis somente nas edições Enterprise, Developer e Evaluation do SQL Server.

  • ON
    Bloqueios de tabela de longa duração não são mantidos pelo tempo de operação do índice. Durante a fase principal da operação de índice, apenas um bloqueio Tentativa Compartilhada é mantido na tabela de origem. Ele permite o prosseguimento de consultas ou atualizações feitas na tabela e nos índices subjacentes. No início da operação, um bloqueio Compartilhado (S) é mantido no objeto de origem por um período muito curto. Ao término da operação, por um curto período de tempo, um bloqueio S (Compartilhado) será adquirido na origem se um índice não clusterizado estiver sendo criado; ou um bloqueio de modificação de esquema (SCH-M) será adquirido quando um índice clusterizado for criado ou descartado online e quando um índice clusterizado ou não clusterizado estiver sendo recriado. Não será possível definir ONLINE como ON quando um índice estiver sendo criado em uma tabela temporária local.

  • OFF
    Os bloqueios de tabela são aplicados enquanto durar a operação de índice. Uma operação de índice offline que cria, recria ou cancela um índice clusterizado ou recria ou cancela um índice não clusterizado, adquire um bloqueio de esquema de modificação (Sch-M) na tabela. Isso evita o acesso de todos os usuários à tabela subjacente enquanto durar a operação. Uma operação de índice offline que cria um índice não clusterizado adquire um bloqueio compartilhado (S) na tabela. Ele impede a realização de atualizações na tabela subjacente, mas permite operações de leitura, como instruções SELECT.

  Para obter mais informações, consulte Como funcionam as operações de índice online. Para obter mais informações sobre bloqueios, consulte Modos de bloqueio.

  Índices, inclusive os índices em tabelas temporárias globais, podem ser criados online com as seguintes exceções:

  • Índice XML.

  • Índice em uma tabela temporária local.

  • Índice clusterizado exclusivo inicial em uma exibição.

  • Índices clusterizados desabilitados.

  • Índice clusterizado se a tabela subjacente contiver tipos de dados LOB: image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml.

  • Índice não clusterizado definido com colunas de tipo de dados LOB.

    Observação
    Um índice não clusterizado não exclusivo poderá ser criado online se a tabela contiver tipos de dados LOB, mas nenhuma dessas colunas for usada na definição de índice, seja como colunas de chave ou não chave.

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

• ALLOW_ROW_LOCKS = { ON | OFF }
  Especifica se bloqueios de linha são permitidos. O padrão é ON.

  • ON
    Bloqueios de linha são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de linha são usados.

  • OFF
    Bloqueios de linha não são usados.

• ALLOW_PAGE_LOCKS = { ON | OFF }
  Especifica se bloqueios de página são permitidos. O padrão é ON.

  • ON
    Bloqueios de página são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de página são usados.

  • OFF
    Bloqueios de página não são usados.

• MAXDOP = max_degree_of_parallelism
  Substitui a opção de configuração grau máximo de paralelismo enquanto durar a operação de índice. Use MAXDOP para limitar o número de processadores usados em uma execução de plano paralelo. O máximo é de 64 processadores.

  max_degree_of_parallelism pode ser:

  • 1
    Suprime a geração de plano paralelo.

  • >1
    Restringe o número máximo de processadores usados em uma operação de índice paralela ao número especificado, ou menos, com base na carga de trabalho atual do sistema.

  • 0 (padrão)
    Usa o número real de processadores, ou menos, com base na carga de trabalho atual do sistema.

  Para obter mais informações, consulte Configurando operações de índice paralelo.

  Observação
  As operações de índice paralelas estão disponíveis somente nas edições Enterprise, Developer e Evaluation do SQL Server.

• DATA_COMPRESSION
  Especifica a opção de compactação de dados para o índice, número de partição ou intervalo de partições especificado. As opções são as seguintes:

  • NONE
    O índice ou as partições especificadas não são compactados.

  • ROW
    O índice ou as partições especificadas são compactados com o uso da compactação de linha.

  • PAGE
    O índice ou as partições especificadas são compactados com o uso da compactação de página.

  Para obter mais informações sobre compactação, consulte Criando tabelas e índices compactados.

• ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
  Especifica as partições às quais se aplica a configuração DATA_COMPRESSION. Se o índice não for particionado, o argumento ON PARTITIONS irá gerar um erro. Se a cláusula ON PARTITIONS não for fornecida, a opção DATA_COMPRESSION será aplicada a todas as partições de um índice particionado.

  <partition_number_expression> pode ser especificado das seguintes maneiras:

  • Forneça o número de uma partição, por exemplo: ON PARTITIONS (2).

  • Forneça os números de várias partições individuais separados por vírgulas, por exemplo: ON PARTITIONS (1, 5).

  • Forneça os intervalos e as partições individuais, por exemplo: ON PARTITIONS (2, 4, 6 TO 8).

  <range> pode ser especificado como números de partição separados pela palavra TO, por exemplo: ON PARTITIONS (6 TO 8).

  Para definir tipos diferentes de compactação de dados para partições diferentes, especifique a opção DATA_COMPRESSION mais de uma vez, por exemplo:

  REBUILD WITH 
  (
  DATA_COMPRESSION = NONE ON PARTITIONS (1), 
  DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
  DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
  )
  

Comentários

A instrução CREATE INDEX é otimizada como qualquer outra consulta. Para salvar as operações de E/S, o processador de consultas pode optar por examinar outro índice em vez de executar um exame de tabela. A operação de classificação pode ser eliminada em algumas situações. Em computadores com multiprocessadores que executam o SQL Server 2005 Enterprise Edition ou o SQL Server 2008, CREATE INDEX pode usar mais processadores para executar operações de exame e classificação associadas à criação do índice, exatamente como fazem outras consultas. Para obter mais informações, consulte Configurando operações de índice paralelo.

A operação de criação de índice poderá ser registrada minimamente se o modelo de recuperação de banco de dados for definido como bulk-logged ou simples. Para obter mais informações, consulte Escolhendo um modelo de recuperação para operações de índice.

Os índices podem ser criados em uma tabela temporária. Quando a tabela for descartada ou a sessão encerrada, os índices serão descartados.

Os índices dão suporte a propriedades estendidas. Para obter mais informações, consulte Usando propriedades estendidas em objetos de banco de dados.

Índices clusterizados

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.

Índices exclusivos

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

Í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.

Exibições indexadas

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

A definição de uma exibição indexada deve ser determinística. Uma exibição será determinística se todas as expressões na lista selecionada, bem como as cláusulas WHERE e GROUP BY, forem determinísticas. As expressões determinísticas sempre retornam o mesmo resultado sempre que são avaliadas com um conjunto específico de valores de entrada. Somente as funções determinísticas podem participar de expressões determinísticas. Por exemplo, a função DATEADD é determinística porque sempre retorna o mesmo resultado para qualquer conjunto específico de valores de argumento para seus três parâmetros. GETDATE não é determinística porque sempre é invocada com o mesmo argumento, mas o valor que ela retorna é alterado a cada execução. Para obter mais informações, consulte Funções determinísticas e não determinísticas.

Mesmo que uma expressão seja determinística, se ela contiver expressões flutuantes, o resultado exato poderá depender da arquitetura do processador ou da versão de microcódigo. Para assegurar a integridade dos dados, tais expressões podem participar somente como colunas não chave de exibições indexadas. As expressões determinísticas que não contêm expressões flutuantes são chamadas de precisas. Somente as expressões determinísticas precisas podem participar de colunas de chave e de cláusulas WHERE ou GROUP BY de exibições indexadas.

Use a propriedade IsDeterministic da função COLUMNPROPERTY para determinar se uma coluna de exibição é determinística. Use a propriedade IsPrecise da função COLUMNPROPERTY para determinar se uma coluna determinística em uma exibição com associação de esquema é precisa. COLUMNPROPERTY retornará 1 se for TRUE, 0 se for FALSE e NULL para entrada inválida. Isso significa que a coluna não é determinística ou não é precisa.

Requisitos adicionais

Além das opções SET e dos requisitos de função determinística, os seguintes requisitos devem ser atendidos:

• O usuário que executa CREATE INDEX deve ser o proprietário da exibição.

• Se a definição de exibição contiver uma cláusula GROUP BY, a chave de índice clusterizado exclusivo poderá referenciar somente as colunas especificadas na cláusula GROUP BY.

• As tabelas base devem ter as opções SET corretas definidas quando a tabela for criada, caso contrário, ela não poderá ser referenciada pela exibição com a associação de esquema.

• As tabelas devem ser referenciadas através de nomes de duas partes, schema**.**tablename, na definição de exibição.

• As funções definidas pelo usuário devem ser criadas usando a opção WITH SCHEMABINDING.

• As funções definidas pelo usuário devem ser referenciadas através de nomes de duas partes, schema**.**function.

• A exibição deve ser criada usando a opção WITH SCHEMABINDING.

• A exibição deve referenciar apenas as tabelas base no mesmo banco de dados e não outras exibições.

• A definição de exibição não deve conter o seguinte:

  COUNT(*)	Função ROWSET
  Tabela derivada	Autojunção
  DISTINCT	STDEV, VARIANCE, AVG
  Colunas float*, text, ntext ou image	Subconsulta
  Predicados de texto completo (CONTAIN, FREETEXT).	SUM em expressão que permite valor nulo
  Função de agregação CLR definida pelo usuário	TOP
  MIN, MAX	UNION

  *A exibição indexada pode conter colunas float; porém, tais colunas não podem ser incluídas na chave de índice clusterizado.

Se GROUP BY estiver presente, a definição VIEW deverá conter COUNT_BIG(*) e não deverá conter HAVING. Essas restrições GROUP BY são aplicáveis apenas à definição de exibição indexada. Uma consulta pode usar uma exibição indexada em seu plano de execução mesmo que não satisfaça essas restrições GROUP BY.

As exibições indexadas podem ser criadas em uma tabela particionada e elas próprias podem ser particionadas. Para obter mais informações sobre particionamento, consulte a seção anterior "Índices particionados".

Para impedir que o Mecanismo de Banco de Dados use exibições indexadas, inclua a dica OPTION (EXPAND VIEWS) na consulta. Além disso, se qualquer uma das opções listadas for definida incorretamente, isso evitará que o otimizador use os índices nas exibições. Para obter mais informações sobre a dica OPTION (EXPAND VIEWS), consulte SELECT (Transact-SQL).

O nível de compatibilidade do banco de dados não pode ser menor que 80. Um banco de dados que contém uma exibição indexada não pode ser alterado para um nível de compatibilidade inferior a 80.

Índices filtrados

Í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

As opções SET na coluna Required Value são necessárias sempre que ocorre alguma das seguintes condições:

• Criar um índice filtrado.

• A operação INSERT, UPDATE, DELETE ou MERGE modificar os dados de um índice filtrado.

• O otimizador de consulta usar o índice filtrado no plano de execução de consulta.

  opções SET	Valor necessário
  ANSI_NULLS	ON
  ANSI_PADDING	ON
  ANSI_WARNINGS*	ON
  ARITHABORT	ON
  CONCAT_NULL_YIELDS_NULL	ON
  NUMERIC_ROUNDABORT	OFF
  QUOTED_IDENTIFIER	ON

  *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 as opções SET estiverem incorretas, as seguintes condições poderão ocorrer:

• O índice filtrado não é criado.

• O Mecanismo de Banco de Dados gera um erro e reverte as instruções INSERT, UPDATE, DELETE ou MERGE que alteram os dados no índice.

• O otimizador de consulta não considera o índice no plano de execução para qualquer instrução Transact-SQL.

Para obter mais informações sobre índices filtrados, consulte Diretrizes de criação de índice filtrado.

Índices espaciais

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

Índices XML

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

Tamanho de chave de índice

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.

Colunas computadas

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.

Colunas incluídas em índices

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.

Especificando opções de índice

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).

Cláusula DROP_EXISTING

É 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.

Opção ONLINE

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.

Opções de bloqueios de linha e de página

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.

Exibindo informações de í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.

Compactação de dados

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.

Permissões

Requer a permissão ALTER na tabela ou exibição. O usuário deve ser um membro da função de servidor fixa sysadmin ou das funções de banco de dados fixas db_ddladmin e db_owner.

Exemplos

A. Criando um índice não clusterizado simples

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

B. Criando um índice composto não clusterizado simples

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

C. Criando um índice não clusterizado exclusivo

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.

D. Usando a opção IGNORE_DUP_KEY

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.

E. Usando DROP_EXISTING para descartar e recriar um índice

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

F. Criando um índice em uma exibição

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

G. Criando um índice com colunas incluídas (não chave)

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

H. Criando um índice particionado

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

I. Criando um índice filtrado

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

J. Criando um índice compactado

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

Consulte também

Referência

ALTER INDEX (Transact-SQL)

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION SCHEME (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE STATISTICS (Transact-SQL)

CREATE TABLE (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

Tipos de dados (Transact-SQL)

DBCC SHOW_STATISTICS (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.xml_indexes (Transact-SQL)

Conceitos

Diretrizes de criação de índice filtrado

Diretrizes para criação de índice geral

Índices em colunas de tipo de dados XML

Outros recursos

Determinando requisitos de espaço em disco de índice

Arquitetura de estruturas de dados de índice e tabela