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 UNIQUE
lub 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 NULL
s 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 KEY
stanie się indeksem klastrowanym i unikatowymi ograniczeniami nieklastrowanymi, ale to zachowanie można zastąpić poprzez określenie CLUSTERED
lub NONCLUSTERED
jawnie 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 Name
przez 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).
IDENTITY
Niejednoznaczny 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ą.