Criando um índice em uma variável de tabela

Você pode criar um index em uma variável de tabela no SQL Server 2000 ?

ou seja

 DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) 

Posso criar um índice no nome?

    A questão é marcada com o SQL Server 2000, mas para o benefício das pessoas que estão desenvolvendo a versão mais recente, vou abordar isso primeiro.

    SQL Server 2014

    Além dos methods de adição de índices baseados em restrições discutidos abaixo, o SQL Server 2014 também permite que índices não exclusivos sejam especificados diretamente com a syntax inline nas declarações de variables ​​de tabela.

    Exemplo de syntax para isso está abaixo.

     /*SQL Server 2014+ compatible inline index syntax*/ DECLARE @T TABLE ( C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/ C2 INT INDEX IX2 NONCLUSTERED, INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/ ); 

    Índices e índices filtrados com colunas incluídas não podem ser declarados com essa syntax, no entanto, o SQL Server 2016 relaxa um pouco mais. A partir do CTP 3.1, agora é possível declarar índices filtrados para variables ​​de tabela. Por RTM, pode ser que colunas incluídas também sejam permitidas, mas a posição atual é que elas “provavelmente não chegarão ao SQL16 devido a restrições de resources”.

     /*SQL Server 2016 allows filtered indexes*/ DECLARE @T TABLE ( c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/ ) 

    SQL Server 2000 – 2012

    Posso criar um índice no nome?

    Resposta curta: sim.

     DECLARE @TEMPTABLE TABLE ( [ID] [INT] NOT NULL PRIMARY KEY, [Name] [NVARCHAR] (255) COLLATE DATABASE_DEFAULT NULL, UNIQUE NONCLUSTERED ([Name], [ID]) ) 

    Uma resposta mais detalhada está abaixo.

    As tabelas tradicionais no SQL Server podem ter um índice clusterizado ou são estruturadas como heaps .

    Índices em cluster podem ser declarados como exclusivos para não permitir valores de chave duplicados ou padrão para não exclusivos. Se não for exclusivo, o SQL Server adiciona silenciosamente um exclusivo a qualquer chave duplicada para torná-lo exclusivo.

    Índices não agrupados também podem ser explicitamente declarados como únicos. Caso contrário, para o caso não exclusivo, o SQL Server adiciona o localizador de linha (chave de índice clusterizada ou RID para um heap) a todas as chaves de índice (não apenas duplicatas), o que garante novamente que sejam exclusivas.

    No SQL Server 2000 – 2012, os índices em variables ​​de tabela só podem ser criados implicitamente, criando uma restrição UNIQUE ou PRIMARY KEY . A diferença entre esses tipos de restrição é que a chave primária deve estar em colunas não anuláveis. As colunas que participam de uma restrição exclusiva podem ser anuláveis. (embora a implementação de restrições exclusivas do SQL Server na presença de NULL s não seja aquela especificada no SQL Standard). Além disso, uma tabela só pode ter uma chave primária, mas várias restrições exclusivas.

    Ambas as restrições lógicas são implementadas fisicamente com um índice exclusivo. Se não for especificado explicitamente, a PRIMARY KEY se tornará o índice clusterizado e as restrições únicas sem cluster, mas esse comportamento pode ser substituído especificando CLUSTERED ou NONCLUSTERED explicitamente com a declaração de restrição (syntax de exemplo)

     DECLARE @T TABLE ( A INT NULL UNIQUE CLUSTERED, B INT NOT NULL PRIMARY KEY NONCLUSTERED ) 

    Como resultado do acima, os seguintes índices podem ser implicitamente criados em variables ​​de tabela no SQL Server 2000 – 2012.

     +-------------------------------------+-------------------------------------+ | Index Type | Can be created on a table variable? | +-------------------------------------+-------------------------------------+ | Unique Clustered Index | Yes | | Nonunique Clustered Index | | | Unique NCI on a heap | Yes | | Non Unique NCI on a heap | | | Unique NCI on a clustered index | Yes | | Non Unique NCI on a clustered index | Yes | +-------------------------------------+-------------------------------------+ 

    O último requer um pouco de explicação. Na definição de variável de tabela no início dessa resposta, o índice não agrupado não exclusivo em Name é simulado por um índice exclusivo em Name,Id (lembre-se de que o SQL Server adicionaria silenciosamente a chave de índice clusterizado à chave NCI não exclusiva).

    Um índice clusterizado não exclusivo também pode ser obtido adicionando manualmente uma coluna IDENTITY para atuar como um uniqueifier.

     DECLARE @T TABLE ( A INT NULL, B INT NULL, C INT NULL, Uniqueifier INT NOT NULL IDENTITY(1,1), UNIQUE CLUSTERED (A,Uniqueifier) ) 

    Mas essa não é uma simulação precisa de como um índice em cluster não exclusivo normalmente seria implementado no SQL Server, pois adiciona o “Uniqueifier” a todas as linhas. Não apenas aqueles que exigem isso.

    Deve ser entendido que, do ponto de vista do desempenho, não há diferenças entre as tabelas @temp e #temp que favorecem as variables. Eles residem no mesmo local (tempdb) e são implementados da mesma maneira. Todas as diferenças aparecem em resources adicionais. Veja este incrivelmente completo writeup: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

    Embora haja casos em que uma tabela temporária não possa ser usada, como na tabela ou funções escalares, para a maioria dos outros casos anteriores a v2016 (onde até mesmo índices filtrados podem ser adicionados a uma variável de tabela), você pode simplesmente usar uma tabela #temp.

    A desvantagem de usar índices nomeados (ou restrições) no tempdb é que os nomes podem entrar em conflito. Não apenas teoricamente com outros procedimentos, mas muitas vezes facilmente com outras instâncias do próprio procedimento que tentaria colocar o mesmo índice em sua cópia da tabela #temp.

    Para evitar confrontos de nome, algo assim normalmente funciona:

     declare @cmd varchar(500)='CREATE NONCLUSTERED INDEX [ix_temp'+cast(newid() as varchar(40))+'] ON #temp (NonUniqueIndexNeeded);'; exec (@cmd); 

    Isso garante que o nome seja sempre único, mesmo entre execuções simultâneas do mesmo procedimento.