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

Question

Peut-on créer un index sur une variable de table dans SQL Server 2000 ?

c'est-à-dire

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

Puis-je créer un index sur Name ?

Answer 1

La question porte sur le serveur SQL 2000, mais pour le bénéfice des personnes qui développent sur la dernière version, je vais d'abord l'aborder.

SQL Server 2014

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

Voici un exemple de syntaxe pour cela.

/*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 des colonnes incluses ne peuvent actuellement pas être déclarés avec cette syntaxe, mais SQL Server 2016 assouplit encore un peu plus la situation. Depuis CTP 3.1, il est désormais possible de déclarer des index filtrés pour les variables de table. Avec la RTM, il mai Il se peut que les colonnes incluses soient également autorisées, mais la position actuelle est qu'elles ne sont pas autorisées. "ne sera probablement pas intégré à SQL16 en raison de contraintes 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 ?

Réponse courte : Oui.

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

Vous trouverez ci-dessous une réponse plus détaillée.

Les tables traditionnelles dans SQL Server peuvent soit avoir un index en cluster, soit être structurées en tant que amas .

Les index en grappe peuvent être déclarés uniques pour empêcher la duplication des valeurs des clés ou non uniques par défaut. S'ils ne sont pas uniques, le serveur SQL ajoute silencieusement un élément uniqueifier à toute clé dupliquée pour la rendre unique.

Les index non clusterisés peuvent également être déclarés explicitement comme uniques. Sinon, pour le cas non unique, le serveur SQL ajoute le localisateur de ligne (clé d'indexation en grappe ou RID pour un tas) à toutes les clés de l'index (pas seulement les doublons), ce qui garantit à nouveau qu'elles sont uniques.

Dans les versions 2000 à 2012 de SQL Server, les index sur les variables de table ne peuvent être créés que de manière implicite en créant un fichier UNIQUE o PRIMARY KEY contrainte. La différence entre ces types de contraintes est que la clé primaire doit porter sur une ou plusieurs colonnes non annulables. Les colonnes participant à une contrainte unique peuvent être nullables. (bien que l'implémentation des contraintes uniques par le serveur SQL en présence d'une clé primaire puisse être nulle). NULL n'est pas conforme à celle spécifiée dans la norme SQL). De plus, une table ne peut avoir qu'une seule clé primaire mais plusieurs contraintes uniques.

Ces deux contraintes logiques sont physiquement mises en œuvre avec un index unique. Si cela n'est pas explicitement spécifié, l'index PRIMARY KEY deviendra l'index clusterisé et les contraintes uniques non clusterisées mais ce comportement peut être modifié en spécifiant CLUSTERED o NONCLUSTERED explicitement avec la déclaration de la contrainte (exemple de syntaxe)

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

En conséquence de ce qui précède, les index suivants peuvent être créés implicitement 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                                 |
+-------------------------------------+-------------------------------------+

Le dernier point nécessite un peu d'explication. Dans le tableau de définition des variables au début de cette réponse, la variable non unique index non clusterisé sur Name est simulée par un unique index sur Name,Id (rappelons que SQL Server ajouterait silencieusement la clé de l'index clusterisé à la clé NCI non unique de toute façon).

Un index en grappe non unique peut également être obtenu en ajoutant manuellement un élément de type IDENTITY pour servir d'identificateur 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 précise de la façon dont un index clusterisé non unique serait normalement mis en œuvre dans SQL Server, car cela ajoute le "Uniqueifier" à toutes les lignes. Pas seulement à celles qui le nécessitent.

Answer 2

Il faut comprendre que du point de vue des performances, il n'y a aucune différence entre les tables @temp et les tables #temp qui favorise les variables. Elles résident au même endroit (tempdb) et sont implémentées de la même manière. Toutes les différences apparaissent dans les fonctionnalités supplémentaires. Voir cet article incroyablement complet : https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

Bien qu'il y ait des cas où une table temporaire ne peut pas être utilisée, comme dans les fonctions de table ou scalaires, pour la plupart des autres cas avant la v2016 (où même les index filtrés peuvent être ajoutés à une variable de table), vous pouvez simplement utiliser une table #temp.

L'inconvénient de l'utilisation d'index (ou de contraintes) nommés dans Tempdb est que les noms peuvent alors entrer en conflit. Pas seulement en théorie avec d'autres procédures, mais souvent assez facilement avec d'autres instances de la procédure elle-même qui essaierait de mettre le même index sur sa copie de la table #temp.

Pour éviter les conflits de noms, on peut généralement procéder comme suit :

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

Cela garantit que le nom est toujours unique, même entre les exécutions simultanées de la même procédure.

Answer 3

Si la variable Table a de grandes données, alors au lieu de la variable Table (@table), créez une table temporaire (#table). La variable Table ne permet pas de créer un index après l'insertion.

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

1. Créer une table avec un index unique en grappe

2. Insertion de données dans la table Temp "#Table".

3. Créer des index non clusterisés.

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

Answer 4

La réponse est non. Vous pouvez uniquement ajouter des contraintes PRIMARY KEY (comme vous l'avez fait) et UNIQUE dans la définition de la variable de table.

Vous trouverez ici de plus amples informations :

http://support.microsoft.com/default.aspx/kb/305977

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html