septembre
2010
Un article de zinzineti
Quels sont les conditions pour créer un index sur une colonne calculée ou une vue ?
Voici un scénario pour mettre en évidence les pré-réquis.
–> Soit la table T1 de DDL
CREATE TABLE dbo.T1
(
id int identity(1,1),
val varchar(10),
creation_date datetime ,
calc AS 2*id , --colonne calculée
mois AS datename(mm, creation_date)
);
go
–> Positionnons les options SET suivantes à OFF
SET QUOTED_IDENTIFIER OFF;
SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF ;
SET ANSI_PADDING OFF;
SET ANSI_NULLS OFF;
SET CONCAT_NULL_YIELDS_NULL OFF;
SET NUMERIC_ROUNDABORT OFF
–>Essayons de Créer un index sur la colonne calculée calc
CREATE INDEX IX_NC_calc ON T1(calc)
–>Résultat : Impossible de créer l’index
–Message d’erreur :
——————–
/*Msg 1934, Level 16, State 1, Line 1
Échec de CREATE INDEX car les options SET suivantes comportent des paramètres incorrects :
‘ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING’.
Assurez-vous que les options SET peuvent être utilisées avec vues indexées et/ou index sur
les colonnes calculées et/ou index filtrés et/ou notifications de requêtes et/ou méthodes de type
de données XML et/ou opérations d’index spatial.
*/
–>Appliquons ce que dit le message d’erreur en exécutant les commandes:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON ;
SET ANSI_PADDING ON;
–>Essayons à nouveau de créer l’index sur la colonne calculée calc
CREATE INDEX IX_NC_calc ON T1(calc)
–>Résultat : L’index est créé sans problème
–>Conclusion : pour créer un index sur colonne calculée il faut que les options SET soient positionnées comme ci-dessous:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON ;
SET ANSI_PADDING ON;
--Attention NUMERIC_ROUNDABORT doit être à OFF
SET NUMERIC_ROUNDABORT OFF
--Et le ARITHABORT peut être à ON ou OFF ça n'a pas d'importance !
—————————————————–
Auteur : Etienne ZINZINDOHOUE
—————————————————–
Bien vu
C’est exactement ce que j’ai fait ici : http://blog.developpez.com/zinzineti/p9321/sql-server-2005/index-sur-une-colonne-non-deterministe/#more9321
Thanks
On peut rajouter à cela le cas des colonnes ou vues qui embarquent des fonctions TSQL ou CLR qui doivent ètre déterministes et précises
A+