Indexer les colonnes de type "bit"

Submitted by st on

É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])