Erstellen eines index für eine Tabelle variable

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?

Es ist eine kostengünstige erstellen beide Arten von temporären Tabellen; und wenn Sie so viele Daten, dass Sie, müssen Sie einen index wäre es an der Zeit zu schauen, mit einem echten Tisch ist; dass Sie, um die Transaktion sicher; filter durch spid oder Benutzer-id ein und deaktivieren Sie es unten am Ende. Echte Tabellen v temp-Tabellen, beide haben Ihre Höhen und tiefen, aber wenn die Leistung ist ein Problem, versuchen Sie es mit einem echten Tisch zu.
Eine temp-Tabelle 'IST' eine echte Tabelle, Sie geht einfach Weg, wenn Sie fertig sind. Der wirkliche Unterschied (außer es geht automatisch entfernt) ist, dass es in TempDB. Dies ist tatsächlich enorm, wenn es um die Indizes und Einschränkungen, weil Sie könnte Ende-up mit dem Namen Auseinandersetzungen, nicht nur mit anderen Ausführungen des Codes aber mit der code-Ausführung in anderen Datenbanken in der Instanz.
dies ist eine table-variable nicht in eine temp-Tabelle. Tabelle Variablen, die nicht erlauben, explizit benannte Einschränkungen, die vom system generierten Namen garantiert eindeutig sein. Sie gestatten die genannten Indizes aus 2014 das ist aber kein problem, da nur Indizes müssen mit einem eindeutigen Namen innerhalb eines Objekts nicht über Objekte.
Mein Punkt war 2 Falten. 1) für Andere als die Verwendung einer Variablen zu vermeiden, die Transaktion Verschränkung es ist kein wesentlicher Unterschied zwischen einer temp-Tabelle und die Tabelle variable. In V-2000 jedoch gibt es keine syntax für das hinzufügen von constraints, Indizes... auf eine variable. 2) Gegeben, kann man mithilfe einer temp-Tabelle statt, genannt Tisch Hautanhangsgebilde wie Indizes WIRD clash mit der gleichzeitigen Ausführung von Kopien der gleichen SP, wenn einen statischen Namen verwendet! Der Mechanismus unten wurde entwickelt, ausdrücklich, weil ich verfolgt SP-Ausfälle zu benannten Indizes aufeinander, während diese genauen Umstände. Sie MÜSSEN einzigartig sein.
Keine index Namen müssen nicht eindeutig sein, zwischen Objekten - nur constraint-names zu tun. dies ist trivial zu testen. Führen Sie einfach CREATE TABLE #T1(X INT); CREATE TABLE #T2(X INT); CREATE INDEX IX ON #T1(X); CREATE INDEX IX ON #T2(X);

InformationsquelleAutor GordyII | 2009-05-20

  1. 324

    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 uniqueifier, um 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 ENTFERNEN 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.

    Hinweis: 2000-2012 Lösung funktioniert nur, wenn die Spalte text <= 900 bytes. dh. varchar(900), nvarchar(450)
    yep, das ist die maximale Größe für einen Indexschlüssel auf permanente Tabellen auch in diesen Versionen.
    Ich wollte nur zu beachten, dass der SQL 2014 Antwort, funktioniert in Azure. Danke Martin!

    InformationsquelleAutor Martin Smith

  2. 12

    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.

    Es hat eine fehlende Klammer nach varchar(40), hinzufügen, dass
    Es ist kein problem mit dem Namen-Indizes - Indizes nur noch mit einem eindeutigen Namen innerhalb einer Tabelle. Das problem mit den benannten Einschränkungen und die beste Lösung im Allgemeinen nicht zu benennen in temp-Tabellen - benannte Einschränkungen verhindern, dass temp-Tabelle, Objekt-caching.
    Das muss wahr sein, nur für bestimmte Versionen (wenn es wahr ist für jede beliebige version). Ich hatte zu kommen mit diesem workaround speziell, weil ich verfolgt sp-Ausfälle auf den Zusammenprall der genannten Indizes während der gleichzeitigen Ausführungen.
    Verwenden Sie 2016? Ich bin sehr neugierig, ob die benannte Indizes auf temporäre Tabellen sind ein Risiko für die gleichzeitige Umgebungen.
    ja, das sind Sie. Wir fanden Konflikte in der SQL-meta-Daten-Tabellen, wenn unter Last, entfernen Sie den Namen des index ist das problem gelöst. Dies war SQL 2016.

    InformationsquelleAutor bielawski

  3. 0

    Wenn Table-variable hat großen Daten, dann statt der Tabelle-variable(@Tabelle) erstellen der temporären Tabelle (#table).Tabelle variable nicht erlauben create index nach einfügen.

     CREATE TABLE #Table(C1 int,       
  C2 NVarchar(100) , C3 varchar(100)
  UNIQUE CLUSTERED (c1) 
 );

    1. Erstellen der Tabelle mit eindeutigen gruppierten index

    2. Einfügen von Daten in Temp "#Table"

    3. Erstellen Sie nicht gruppierte Indizes.

       CREATE NONCLUSTERED INDEX IX1  ON #Table (C2,C3);
    erstellen index nach der insert-Anweisung, zur Vermeidung unnötiger Sortieren

    InformationsquelleAutor Boopathi.Indotnet

Schreibe einen Kommentar