septembre
2010
Un article de zinzineti
Peut-on créer un index sur une colonne non déterministe ?
Qu’est ce qu’une colonne déterministe ou non ? comment les identifier ?
–> Soit la table T1
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)
);
–>Positionnons les options SET
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON ;
SET ANSI_PADDING ON;
SET NUMERIC_ROUNDABORT OFF ;
–> Essayons de créer un index sur la colonne mois
CREATE INDEX IN_NC_mois ON T1 (mois);
–>Résultat : Impossible de créer l’index !!!
–Message d’erreur :
———————-
/*Msg 2729, Level 16, State 1, Line 1
Impossible d’utiliser la colonne ‘MOIS’ de table ‘T1′ dans un index,
des statistiques ou en tant que clé de partition car elle n’est pas déterministe.
*/
–>Comment identifier une colonne déterministe/non-déterministe
SELECT CASE COLUMNPROPERTY (OBJECT_ID('T1'), 'mois', 'IsDeterministic')
WHEN 0 THEN 'Non déterministe'
WHEN 1 THEN 'Est déterministe'
WHEN NULL THEN 'Entrée non valide. Aucune colonne calculée ou colonne de la vue'
ELSE ''
END AS [Déterminisme];
–> Résultat : Colonne Non déterministe
–>Une colonne non déterministe est-elle indexable ?
SELECT CASE COLUMNPROPERTY (OBJECT_ID('T1'), 'mois', 'IsIndexable')
WHEN 0 THEN 'Pas indexable'
WHEN 1 THEN 'Est indexable'
WHEN NULL THEN 'Entrée non valide'
ELSE ''
END AS [indexable];
–>Résultat : Non, la colonne non déterministe n’est pas indexable
–> Alors qu’est ce qu’une colonne non déterministe ?
La même question est posée à SQLPro, voici sa réponse :
Définition du déterminisme (informatique) : traitement qui donne un même résultat à même conditions d’entrée et code d’exécution. Par opposition toutes les fonctions non déterministes ne donnent pas les mêmes résultats si même exécution, comme RAND(), NEWID(), CURRENT_TIMESTAMP, GETDATE()… mais aussi celles que vous pouvez créer (UDF) et que SQL Server estimera non déterministes. En effet par essence une fonction ou expression qui ne donne jamais le même résultat ne peut être persistante et la colonne n’est pas indexable.
Et SQLPro ajoute :
Woody Allen, lorsqu’on lui demande l’heure qu’il est répond systématiquement « je ne peux pas vous la donner, ça change tout le temps »… C’est à mon avis la meilleure façon de comprendre l’indéterminisme ! »
——————————————————————————
Auteur : Etienne ZINZINDOHOUE
——————————————————————————
Merci pour la remarque
Là il manque peut être la vérification de précision : IsPrecise.
En effet, une fonction peut être déterministe mais selon le type de données renvoyé (comme un float par exemple) ne pas donner le même résultat .. par exemple 2 processeurs qui ne donneront pas la même valeur de précision
Mais bon dans tout les cas l’usage de : IsIndexable permet sans équivoque de savoir si une fonction peut être indexée ou non.
A+