Creazione di un indice su una variabile di tabella

È ansible creare un index su una variabile di tabella in SQL Server 2000 ?

vale a dire

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

Posso creare un indice su Nome?

La domanda è contrassegnata da SQL Server 2000 ma, a beneficio delle persone che si stanno sviluppando nell’ultima versione, parlerò prima di tutto.

SQL Server 2014

Oltre ai metodi di aggiunta degli indici basati sui vincoli discussi sotto SQL Server 2014, anche gli indici non univoci possono essere specificati direttamente con la syntax inline sulle dichiarazioni delle variabili di tabella.

La syntax di esempio per questo è di seguito.

 /*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*/ ); 

Gli indici e gli indici filtrati con colonne incluse non possono attualmente essere dichiarati con questa syntax, tuttavia SQL Server 2016 si distende ulteriormente. Da CTP 3.1 è ora ansible dichiarare gli indici filtrati per le variabili di tabella. Tramite RTM è ansible che anche le colonne incluse siano consentite, ma la posizione corrente è che “probabilmente non lo faranno in SQL16 a causa di limiti di risorse”

 /*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 creare un indice su Nome?

Risposta breve: sì.

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

Di seguito una risposta più dettagliata.

Le tabelle tradizionali in SQL Server possono avere un indice cluster o sono strutturate come heap .

Gli indici cluster possono essere dichiarati come unici per non consentire valori di chiavi duplicati o valori di default non univoci. Se non è univoco, SQL Server aggiunge silenziosamente un identificatore a qualsiasi chiave duplicata per renderli univoci.

Gli indici non cluster possono anche essere esplicitamente dichiarati come unici. Altrimenti per il caso non univoco, SQL Server aggiunge il localizzatore di riga (chiave dell’indice cluster o RID per un heap) a tutte le chiavi dell’indice (non solo ai duplicati), ciò garantisce nuovamente che siano univoci.

In SQL Server 2000 gli indici 2012 sulle variabili di tabella possono essere creati implicitamente solo creando un vincolo UNIQUE o PRIMARY KEY . La differenza tra questi tipi di vincoli è che la chiave primaria deve essere su una colonna non nullable. Le colonne che partecipano a un vincolo univoco potrebbero essere annullabili. (sebbene l’implementazione di SQL di vincoli univoci in presenza di NULL non sia conforms a quella specificata nello standard SQL). Inoltre, una tabella può avere solo una chiave primaria ma più vincoli univoci.

Entrambi questi vincoli logici sono fisicamente implementati con un indice univoco. Se non specificato esplicitamente, la PRIMARY KEY diventerà l’indice cluster ei vincoli univoci non raggruppati, ma questo comportamento può essere sovrascritto specificando CLUSTERED o NONCLUSTERED esplicitamente con la dichiarazione del vincolo (Sintassi di esempio)

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

Come risultato di quanto sopra i seguenti indici possono essere creati implicitamente su variabili di tabella in 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 | +-------------------------------------+-------------------------------------+ 

L’ultimo richiede un po ‘di spiegazione. Nella definizione della variabile di tabella all’inizio di questa risposta, l’indice non univoco non clusterizzato su Name viene simulato da un indice univoco su Name,Id (ricorda che SQL Server aggiungerà automaticamente la chiave dell’indice cluster alla chiave NCI non univoca).

Un indice cluster non univoco può anche essere ottenuto aggiungendo manualmente una colonna IDENTITY per fungere da unificatore.

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

Ma questa non è una simulazione accurata di come un indice cluster non univoco verrebbe effettivamente implementato in SQL Server poiché questo aggiunge “Unificatore” a tutte le righe. Non solo quelli che lo richiedono.

Dovrebbe essere chiaro che dal punto di vista delle prestazioni non ci sono differenze tra le tabelle @temp e le tabelle #temp che favoriscono le variabili. Risiedono nello stesso posto (tempdb) e sono implementati allo stesso modo. Tutte le differenze appaiono in funzioni aggiuntive. Vedi questo articolo incredibilmente completo: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

Sebbene esistano casi in cui non è ansible utilizzare una tabella temporanea come nelle funzioni tabella o scalare, per la maggior parte degli altri casi precedenti alla v2016 (in cui anche gli indici filtrati possono essere aggiunti a una variabile di tabella) è sufficiente utilizzare una tabella #temp.

Lo svantaggio di usare indici nominati (o vincoli) in tempdb è che i nomi possono quindi scontrarsi. Non solo teoricamente con altre procedure, ma spesso abbastanza facilmente con altre istanze della procedura stessa che proverebbero a mettere lo stesso indice sulla sua copia della tabella #temp.

Per evitare conflitti di nomi, qualcosa come questo di solito funziona:

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

Ciò assicura che il nome sia sempre univoco anche tra le esecuzioni simultanee della stessa procedura.