Erstellen eines Indexes für eine Tabellenvariable

Können Sie erstellen eine index auf eine table-variable in SQL Server 2000?

d.h.

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

Kann ich erstellen Sie einen index auf Name?

InformationsquelleAutor der Frage GordyII | 2009-05-20

285

Die Frage ist tagged SQL Server 2000, sondern zum nutzen der Menschen entwickeln, auf die neueste version werde ich an diesem ersten.

SQL Server 2014

Zusätzlich zu den Methoden zum hinzufügen constraint-basierte Indizes, die unten besprochen SQL Server 2014 ermöglicht auch nicht eindeutige Indizes angegeben werden, die direkt mit inline-syntax auf table-Variablen-Deklarationen.

Beispiel-syntax für die, die unten ist.
```
/*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*/
);
```
Gefilterte Indizes und Indizes mit eingeschlossenen Spalten können derzeit nicht erklärt werden mit dieser syntax jedoch SQL Server 2016 entspannt ein bisschen weiter. Von CTP-Version 3.1 ist es nun möglich zu erklären, gefilterte Indizes für table-Variablen. Durch RTM-es kann der Fall sein, dass die enthaltenen Spalten sind auch erlaubt, aber die aktuelle position ist, dass Sie "wahrscheinlich wird es nicht in SQL16 aufgrund knapper Ressourcen"
```
/*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

Kann ich erstellen Sie einen index auf Name?

Kurze Antwort: ja.
```
DECLARE @TEMPTABLE TABLE (
  [ID]   [INT] NOT NULL PRIMARY KEY,
  [Name] [NVARCHAR] (255) COLLATE DATABASE_DEFAULT NULL,
  UNIQUE NONCLUSTERED ([Name], [ID]) 
  ) 
```
Eine ausführlichere Antwort ist unten.

Traditionellen Tabellen in SQL Server kann entweder über einen gruppierten index oder strukturiert sind als Haufen.

Clustered-Indizes können entweder als eindeutig deklariert zu verbieten, doppelte Schlüsselwerte oder default, um nicht einzigartig. Wenn nicht eindeutig dann SQL Server automatisch fügt eine uniqueifierum alle doppelten Schlüssel, die Sie einzigartig machen.

Nicht gruppierte Indizes können auch explizit als eindeutig deklariert. Ansonsten für die nicht eindeutigen Fall SQL Server fügt die Zeile locator (Schlüssel des gruppierten Indexes oder RID für einen heap) alle index-Schlüssel (nicht nur Duplikate) diese wieder sorgt dafür, dass Sie einzigartig sind.

In der SQL Server 2000 - 2012 Indizes auf table-Variablen können nur erstellt werden, implizit durch die Schaffung einer UNIQUE oder PRIMARY KEY Einschränkung. Der Unterschied zwischen den beiden constraint-Arten sind, dass der primary key muss auf nicht-nullwertfähige Spalte(s). Die Spalten in einer eindeutigkeits-integritätsregel kann null sein. (wenn SQL Server-Implementierung von unique-Einschränkungen in der Präsenz von NULLs ist nicht pro, angegeben in der SQL-Standard). Auch eine Tabelle kann nur ein Primärschlüssel, aber mehrere unique-Einschränkungen.

Diese beiden logischen Einschränkungen werden physisch umgesetzt mit einem eindeutigen index. Wenn nicht ausdrücklich anders festgelegt, die PRIMARY KEY werden die gruppierten index und unique-Einschränkungen nicht gruppierten aber dieses Verhalten kann überschrieben werden, durch die Angabe CLUSTERED oder NONCLUSTERED ausdrücklich mit der Einschränkung Erklärung (Z.B. syntax)
```
DECLARE @T TABLE
(
A INT NULL UNIQUE CLUSTERED,
B INT NOT NULL PRIMARY KEY NONCLUSTERED
)
```
Als Folge der oben genannten die folgenden Indizes wird implizit erstellt, die auf table-Variablen 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                                 |
+-------------------------------------+-------------------------------------+
```
Die Letzte erfordert ein wenig Erklärung. In der Tabelle Variablen-definition am Anfang dieser Antwort die nicht eindeutig nicht gruppierten index auf Name wird simuliert durch eine einzigartige index auf Name,Id (man erinnere sich, dass der SQL-Server würde schweigend fügen Sie dem Schlüssel des gruppierten Indexes, der nicht eindeutig NCI Schlüssel sowieso).

Einen nicht eindeutigen gruppierten index kann auch erreicht werden, indem manuell hinzufügen IDENTITY Spalte als uniqueifier.
```
DECLARE @T TABLE
(
A INT NULL,
B INT NULL,
C INT NULL,
Uniqueifier INT NOT NULL IDENTITY(1,1),
UNIQUE CLUSTERED (A,Uniqueifier)
)
```
Aber das ist keine genaue simulation, wie einer nicht eindeutigen gruppierten index normalerweise tatsächlich in SQL Server implementiert, da dies fügt den "Uniqueifier", um alle Zeilen. Nicht nur diejenigen, die es benötigen.

InformationsquelleAutor der Antwort Martin Smith
11

Es sollte verstanden werden, dass aus performance-Sicht gibt es keine Unterschiede zwischen @temp Tabellen und #temp-Tabellen für Variablen. Sie wohnen im gleichen Ort (tempdb) und implementiert die gleiche Weise. Die Unterschiede erscheinen zusätzliche Funktionen. Sehen Sie dieses unglaublich vollständige Dokument: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

Obwohl es Fälle gibt, in denen eine temp-Tabelle kann nicht verwendet werden, wie in Tabelle oder Skalare Funktionen, für die meisten anderen Fällen-vor v2016 (wo auch gefilterte Indizes Hinzugefügt werden können, um eine table-variable) kannst du einfach mit Hilfe einer Tabelle #temp.

Den Nachteil zu benannten Indizes (oder Einschränkungen) in tempdb ist, dass die Namen können dann aufeinander prallen. Nicht nur theoretisch mit anderen Verfahren aber oft sehr leicht mit anderen Instanzen des Verfahrens selbst, die versuchen würde, um den gleichen index für die Kopie der Tabelle #temp.

Vermeiden name-clashes, so etwas wie dies funktioniert in der Regel:
```
declare @cmd varchar(500)='CREATE NONCLUSTERED INDEX [ix_temp'+cast(newid() as varchar(40))+'] ON #temp (NonUniqueIndexNeeded);';
exec (@cmd);
```
Dieser versichert, der name ist immer einmalig, auch zwischen die gleichzeitige Ausführung der gleichen Verfahren.

InformationsquelleAutor der Antwort bielawski

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.