Création d'un index sur une variable de table

la question Est tagged SQL Server 2000 mais pour le bénéfice des gens qui développent sur la dernière version je vais aborder cela en premier.

SQL Server 2014

en plus des méthodes d'ajout des index basés sur les contraintes discutées ci-dessous SQL Server 2014 permet également de spécifier des index non uniques directement avec la syntaxe en ligne sur les déclarations de variables de table.

exemple de syntaxe est ci-dessous.

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

les index filtrés et les index avec les colonnes incluses ne peuvent pas actuellement être déclarés avec cette syntaxe cependant SQL Server 2016 détends ceci un peu plus loin. De CTP 3.1 il est maintenant possible de déclarer des index filtrés pour des variables de table. Par RTM it mai être le cas que les colonnes incluses sont également autorisés, mais la position actuelle est qu'ils " ne sera probablement pas en SQL16 dû contraintes en matière de ressources"

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

puis-je créer un index sur le nom?

brève réponse: Oui.

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

voici une réponse plus détaillée.

les tables traditionnelles dans SQL Server peuvent avoir un index groupé ou sont structurées comme suit: tas .

Les index groupés

peuvent être déclarés comme uniques pour interdire les valeurs clés dupliquées ou par défaut comme non uniques. Si ce N'est pas unique, alors SQL Server ajoute silencieusement un uniqueifier à toutes les clés dupliquées pour les rendre uniques.

les indices non groupés peuvent aussi être explicitement déclarés comme uniques. Autrement pour le cas Non unique SQL Server ajoute le Localisateur de ligne (clé d'index groupée ou RID pour un tas) à toutes les clés d'index (pas seulement les doublons) cela assure encore une fois qu'ils sont uniques.

dans les index SQL Server 2000 - 2012 sur les variables de table ne peut être créé implicitement qu'en créant une contrainte UNIQUE ou PRIMARY KEY . La différence entre ces types de contraintes sont que la clé primaire doit être non nullable colonne(s). Les colonnes participant à une contrainte unique peuvent être nulles. (si SQL La mise en œuvre par le serveur de contraintes uniques en présence de NULL s n'est pas conforme à celle spécifiée dans la norme SQL). Aussi un tableau peut avoir une seule clé primaire, mais plusieurs contraintes unique.

ces deux contraintes logiques sont matériellement implémentées avec un index unique. Si ce n'est pas spécifié explicitement, sinon le PRIMARY KEY deviendra l'indice groupé et les contraintes uniques non groupées mais ce comportement peut être dépassé en spécifiant CLUSTERED ou NONCLUSTERED explicitement avec la déclaration de contrainte (exemple de syntaxe)

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

comme résultat de ce qui précède les index suivants peuvent être implicitement créés sur les variables de table dans 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                                 |
+-------------------------------------+-------------------------------------+

la dernière demande un peu d'explication. Dans le tableau définition de la variable au début de cette réponse le non unique indice non groupé sur Name est simulé par un unique index sur Name,Id (rappelons que SQL Server ajouterait silencieusement la touche clustered index à la touche NCI non unique de toute façon).

il est également possible d'obtenir un indice de regroupement non unique en ajoutant manuellement une colonne IDENTITY pour agir à titre d'indicateur unique.

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

mais ce n'est pas une simulation exacte de la façon dont un index groupé non unique serait normalement mis en œuvre dans SQL Server as ceci ajoute le "Uniqueifier" à toutes les lignes. Pas seulement ceux qui en ont besoin.