Tworzenie indeksu na zmiennej tabeli

Czy można utworzyć indeks dla zmiennej tabeli w SQL Server 2000?

to znaczy

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

Czy mogę utworzyć indeks na Name?

Pytanie jest oznaczone jako SQL Server 2000, ale najpierw rozwiążę tę kwestię z korzyścią dla osób rozwijających najnowszą wersję.

SQL Server 2014

Oprócz metod dodawania indeksów opartych na ograniczeniach omówionych poniżej SQL Server 2014 umożliwia także bezpośrednie określanie indeksów nie unikatowych za pomocą wbudowanej składni deklaracji zmiennych tabeli.

Przykładowa składnia tego jest poniżej.

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

Filtrowanych indeksów i indeksów z dołączonymi kolumnami nie można obecnie zadeklarować za pomocą tej składni, jednak SQL Server 2016 nieco to rozluźnia. Od CTP 3.1 można teraz deklarować przefiltrowane indeksy dla zmiennych tabeli. Według RTM może się zdarzyć, że uwzględnione kolumny są również dozwolone, ale obecna pozycja jest taka, że „prawdopodobnie nie dostaną się do SQL16 z powodu ograniczeń zasobów”

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

Czy mogę utworzyć indeks w nazwie?

Krótka odpowiedź: tak.

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

Bardziej szczegółowa odpowiedź znajduje się poniżej.

Tradycyjne tabele w SQL Server mogą mieć indeks klastrowany lub mają strukturę stosów .

Indeksy klastrowe można zadeklarować jako unikalne, aby uniemożliwić powielanie wartości kluczy, lub domyślnie jako niepowtarzalne. Jeśli nie jest unikalny, to SQL Server cicho dodaje unikatowy do każdego zduplikowanego klucza, aby były unikalne.

Indeksy nieklastrowane można również jawnie zadeklarować jako unikalne. W przeciwnym razie w przypadku nie unikatowego przypadku SQL Server dodaje lokalizator wierszy (klastrowany klucz indeksu lub RID dla sterty) do wszystkich kluczy indeksu (nie tylko duplikatów), co ponownie zapewnia ich unikalność.

W SQL Server 2000 - 2012 indeksy zmiennych tabeli można tworzyć niejawnie tylko przez utworzenie ograniczenia UNIQUElub PRIMARY KEY. Różnica między tymi typami ograniczeń polega na tym, że klucz podstawowy musi znajdować się w kolumnach, które nie mają wartości NULL. Kolumny uczestniczące w unikalnym ograniczeniu mogą mieć wartość null. (chociaż implementacja unikalnych ograniczeń przez SQL Server w obecności NULLs nie jest zgodna ze specyfikacją SQL Standard). Również tabela może mieć tylko jeden klucz podstawowy, ale wiele unikalnych ograniczeń.

Oba te ograniczenia logiczne są fizycznie realizowane za pomocą unikalnego indeksu. Jeśli nie zostanie to wyraźnie określone inaczej, PRIMARY KEYstanie się indeksem klastrowanym i unikatowymi ograniczeniami nieklastrowanymi, ale to zachowanie można zastąpić poprzez określenie CLUSTEREDlub NONCLUSTEREDjawnie za pomocą deklaracji ograniczeń (przykładowa składnia)

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

W wyniku powyższego można domyślnie utworzyć następujące indeksy na zmiennych tabeli w 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                                 |
+-------------------------------------+-------------------------------------+

Ten ostatni wymaga trochę wyjaśnienia. W definicji zmiennej tabeli na początku tej odpowiedzi symulowany jest nie unikalny indeks nieklastrowany Nameprzez unikalny indeks włączony Name,Id(pamiętaj, że SQL Server i tak po cichu dodałby klastrowany klucz indeksu do nie unikatowego klucza NCI).

IDENTITYNiejednoznaczny indeks klastrowy można również uzyskać ręcznie dodając kolumnę, która będzie działać jako unikatator.

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

Ale nie jest to dokładna symulacja tego, jak normalnie klaster klastrowy normalnie zostałby faktycznie zaimplementowany w SQL Server, ponieważ dodaje to „Uniqueifier” do wszystkich wierszy. Nie tylko ci, którzy tego wymagają.

Należy rozumieć, że z punktu widzenia wydajności nie ma różnic między tabelami @temp a tabelami #temp, które faworyzują zmienne. Znajdują się w tym samym miejscu (tempdb) i są wdrażane w ten sam sposób. Wszystkie różnice pojawiają się w dodatkowych funkcjach. Zobacz ten niezwykle kompletny opis: /dba/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

Chociaż istnieją przypadki, w których nie można użyć tabeli tymczasowej, takie jak funkcje tabelowe lub skalarne, w większości innych przypadków przed wersją v2016 (gdzie nawet zmienne indeksy można dodać do zmiennej tabeli), można po prostu użyć tabeli #temp.

Wadą używania indeksów nazwanych (lub ograniczeń) w tempdb jest to, że nazwy mogą się następnie kolidować. Nie tylko teoretycznie z innymi procedurami, ale często dość łatwo z innymi instancjami samej procedury, które próbowałyby umieścić ten sam indeks na swojej kopii tabeli #temp.

Aby uniknąć konfliktów nazw, zwykle działa coś takiego:

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

Zapewnia to, że nazwa jest zawsze unikalna, nawet między jednoczesnymi wykonywaniem tej samej procedury.

Jeśli zmienna tabeli ma duże dane, zamiast zmiennej tabeli (@table) utwórz tabelę temp (#table). Zmienna table nie pozwala na utworzenie indeksu po wstawieniu.

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

1. Utwórz tabelę z unikalnym indeksem klastrowym

2. Wstaw dane do tabeli Temp „#Table”

3. Utwórz indeksy klastrowe.

    CREATE NONCLUSTERED INDEX IX1  ON #Table (C2,C3);