Évidemment, l’indexation d'une seule colonne de type "bit" ne servira à rien parce que le nombre des valeurs uniques ne dépassera jamais "2" et donc la sélectivité sera mauvaise. Par contre, si une table contient plusieurs colonnes "bit" l'index vous permettra gagner de la performance.
On crée la table pour notre exemple en en replissant par les donnes aléatoires.
USE AdventureWorks GO CREATE TABLE BitTest ( Id int IDENTITY(1, 1) PRIMARY KEY, Value nvarchar(20), F1 bit, F2 bit, F3 bit, F4 bit ) GO DECLARE @RowsCount int = 1000; DECLARE @i int = 0; WHILE @i < @RowsCount BEGIN INSERT INTO BitTest (Value, F1, F2, F3, F4) SELECT convert(nvarchar(10), @i), convert(int, rand() + 0.5), convert(int, rand() + 0.5), convert(int, rand() + 0.5), convert(int, rand() + 0.5) SET @i = @i + 1 END
On crée l'index sur l'ensemble des colonnes "bit"
CREATE INDEX IX1_BitTest ON BitTest (F1, F2, F3, F4)
Maintenant, si on lance la requête dont la filtrage concerne les valeurs bits on verra que... cet indexe n'est pas utilisé !
SET STATISTICS PROFILE ON GO SELECT * FROM BitTest WHERE F1 = 1 AND F2 = 1 AND F3 = 1 AND F4 = 1; GO SET STATISTICS PROFILE OFF GO
Rows Executes StmtText ----- -------- --------------------------------------------------------------------------------- 79 1 SELECT * FROM [BitTest] WHERE [F1]=@1 AND [F2]=@2 AND [F3]=@3 AND [F4]=@4 79 1 |--Filter(WHERE:([AdventureWorks].[dbo].[BitTest].[F1]=(1) AND [AdventureWorks] 0 0 |--Compute Scalar(DEFINE:([AdventureWorks].[dbo].[BitTest].[CF1]=[Adventur 0 0 |--Compute Scalar(DEFINE:([AdventureWorks].[dbo].[BitTest].[CF1]=(([A 1000 1 |--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[BitTest]
Pourquoi ? Parce que l'utilisation de "SELECT étoile" n'est pas une bonne pratique. Alors, spécifiez explicitement les colonnes retournées. Et voila !
SELECT Id, F1, F2, F3, F4 FROM BitTest WHERE F1 = 1 AND F2 = 1 AND F3 = 1 AND F4 = 1;
Rows Executes StmtText ---- -------- ----------------------------------------------------------------------- 79 1 SELECT [Id],[F1],[F2],[F3],[F4] FROM [BitTest] WHERE [F1]=@1 AND [F2]=@ 79 1 |--Index Seek(OBJECT:([AdventureWorks].[dbo].[BitTest].[IX1_BitTest])
Également, il existe les autres méthodes de traiter les colonnes bits.
Vous pouvez créer une ou plusieurs colonnes calculées basées sur les expressions plus utilisées dans vos requêtes et puis la indexer.
ALTER TABLE BitTest ADD CF1 AS (F1 & F2 & F3 & F4) GO CREATE INDEX IX2_BitTest ON BitTest (CF1); GO SELECT Id, CF1 FROM BitTest WHERE CF1 = 1;
Rows Executes StmtText ---- -------- ---------------------------------------------------------------------------- 79 1 SELECT [Id],[CF1] FROM [BitTest] WHERE [CF1]=@1 0 0 |--Compute Scalar(DEFINE:([AdventureWorks].[dbo].[BitTest].[CF1]=[Adventur 79 1 |--Index Seek(OBJECT:([AdventureWorks].[dbo].[BitTest].[IX2_BitTest])
Ensuit, vous pouvez créer une colonne calculée pour le traitement des nombres entières en utilisant la représentation de ces nombres au niveau binaire.
ALTER TABLE BitTest ADD CF2 AS (convert(int, F1) + convert(int, F2) * 2 + convert(int, F3) * 4 + convert(int, F4) * 8 ) GO CREATE INDEX IX3_BitTest ON BitTest (CF2); GO SELECT Id, CF2 FROM BitTest WHERE CF2 = 15; -- 15 (décimal) = 1111 (binaire)
Rows Executes StmtText ---- -------- ---------------------------------------------------------------------------- 79 1 SELECT [Id],[CF2] FROM [BitTest] WHERE [CF2]=@1 0 0 |--Compute Scalar(DEFINE:([AdventureWorks].[dbo].[BitTest].[CF2]=[Adventur 79 1 |--Index Seek(OBJECT:([AdventureWorks].[dbo].[BitTest].[IX3_BitTest])