Bitwise operators in SQL Server: Bitwise Exclusive OR

Bron: Microsoft TechNet
Download: het gehele behandelde script uit deze bijdrage kan hier worden gedownload.

Met bitwise operators kan data op bit niveau worden geëvalueerd. Hoewel deze techniek al sinds SQL Server 2008 beschikbaar is, is deze techniek nieuw voor mij. In deze bijdrage wil ik graag dieper ingaan op de functie Bitwise Exclusive OR en een handige toepassing van deze functie. Microsoft beschrijft deze specifieke functie op deze Technet pagina.

De functie bitwise Exclusive OR vergelijkt twee expressies op bit niveau. Het resultaat wordt per bit als volgt bepaald:
1 Wanneer op bitniveau één van de bits het resultaat 1 laat zien, is het resultaat van de Exclusive OR 1
2 Wanneer op bitniveau beide bits 1 of 0 zijn, is het resultaat van de Exclusive OR 0

Waarom bitwise operators?
Omdat er op bitniveau wordt gewerkt, kan afhankelijk van de complexiteit van de query de performance bij gebruik van de bitwise operators significant beter zijn dan het gebruik van traditionele oplossingen, zoals een samengestelde WHERE clause of het gebruik van CASE WHEN.

Voorbeeld van een bitwise operator met één bit
Een datum kan wel (1) of geen (0) feestdag zijn. Met de Exclusive OR is dit eenvoudig vast te stellen: (1 ^ Feestdag)

Verklaring
Is de datum een feestdag (dus 1), dan is de uitkomst van de Exclusive OR (1 ^ 1) dus 0, immers op bitniveau zijn beide bits gelijk aan elkaar (voorwaarde 2)

Toepassing
Natuurlijk is het gebruik in bovenstaand voorbeeld wat triviaal, immers Feestdag = 1 geeft hetzelfde resultaat. Het wordt een ander verhaal wanneer er twee tests uitgevoerd moeten worden, bijvoorbeeld in het geval bepaald moet worden of een datum een werkdag is (dus geen feestdag en geen dag in het weekeinde). Wanneer in de datumtabel geen veld ‘IsWerkdag’ aanwezig is, maar de velden ‘IsFeestdag’ (bit) en ‘IsWeekenddag’ (bit) wel, zijn met de Exclusive OR functie werkdagen eenvoudig vast te stellen. Onderstaande query illustreert dit:

-- Gebruik een testdatabase (niet in productie)
USE TestDB
GO
-- Aanmaken tijdelijke tabel
CREATE TABLE Datumtabel (
Datum datetime,
Dag char(20),
IsWeekenddag bit,
IsFeestdag bit
)
GO

-- Tabel vullen
INSERT INTO Datumtabel (Datum, Dag, IsWeekenddag, IsFeestdag)
VALUES
('2016-12-24 00:00:00.000','Zaterdag',1,0),
('2016-12-25 00:00:00.000','Zondag',1,1),
('2016-12-26 00:00:00.000','Maandag',0,1),
('2016-12-27 00:00:00.000','Dinsdag',0,0),
('2016-12-28 00:00:00.000','Woensdag',0,0),
('2016-12-29 00:00:00.000','Donderdag',0,0),
('2016-12-30 00:00:00.000','Vrijdag',0,0),
('2016-12-31 00:00:00.000','Zaterdag',1,0),
('2017-01-01 00:00:00.000','Zondag',1,1),
('2017-01-02 00:00:00.000','Maandag',0,0)
GO

-- Voorbeeldquery om werkdag te bepalen
SELECT
Datum,
Dag,
IsWeekenddag,
'(1 ^ IsWeekenddag), dus 1 ^ ' + CONVERT(char(1), IsWeekenddag) +
' = ' + CONVERT(char(1),(1 ^ IsWeekenddag)) +
' ' + CASE WHEN (1 ^ IsWeekenddag) = 0 THEN '(Gelijk)'
ELSE '(Niet gelijk)' END AS BWEO_Feestdag,
IsFeestdag,
'(1 ^ IsFeestdag), dus 1 ^ ' + CONVERT(char(1), IsFeestdag) +
' = ' + + CONVERT(char(1),(1 ^ IsFeestdag)) +
' = ' + CONVERT(char(1),(1 ^ IsFeestdag)) +
' ' + CASE WHEN (1 ^ IsFeestdag) = 0 THEN '(Gelijk)'
ELSE '(Niet gelijk)'END AS BWEO_Weekenddag,
CONVERT(char(1),(1 ^ IsWeekenddag)) + ' (BWEO_Feestdag) & ' +
CONVERT(char(1),(1 ^ IsFeestdag)) +' (BWEO_Weekenddag = ' +
CONVERT(char(1),(1 ^ IsFeestdag) & (1 ^ IsWeekenddag)) AS BepalingWerkdag,
CONVERT(char(1),(1 ^ IsFeestdag) & (1 ^ IsWeekenddag)) +
' -- (' + RTRIM(Dag) + ' en ' + CASE WHEN IsFeestdag = 1 THEN 'een feestdag)'
ELSE 'geen feestdag)' END AS Werkdag,
(1 ^ IsFeestdag) & (1 ^ IsWeekenddag) AS IsWerkdag -- uiteindelijk resultaat van de bepaling van de werkdag
FROM Datumtabel
-- WHERE (1 ^ IsFeestdag) & (1 ^ IsWeekenddag) = 0 -- Alle niet werkdagen
-- WHERE (1 ^ IsFeestdag) & (1 ^ IsWeekenddag) = 1 -- Alle werkdagen
ORDER BY Datum
GO

Wanneer de query op traditionele wijze had worden uitgevoerd, zou de query voor het ophalen van de niet werkdagen er als volgt eruit hebben gezien:

SELECT
Datum,
Dag,
IsWeekenddag,
'(1 ^ IsWeekenddag), dus 1 ^ ' + CONVERT(char(1), IsWeekenddag) +
' = ' + CONVERT(char(1),(1 ^ IsWeekenddag)) +
' ' + CASE WHEN (1 ^ IsWeekenddag) = 0 THEN '(Gelijk)'
ELSE '(Niet gelijk)' END AS BWEO_Feestdag,
IsFeestdag,
'(1 ^ IsFeestdag), dus 1 ^ ' + CONVERT(char(1), IsFeestdag) +
' = ' + + CONVERT(char(1),(1 ^ IsFeestdag)) +
' = ' + CONVERT(char(1),(1 ^ IsFeestdag)) +
' ' + CASE WHEN (1 ^ IsFeestdag) = 0 THEN '(Gelijk)'
ELSE '(Niet gelijk)'END AS BWEO_Weekenddag,
CONVERT(char(1),(1 ^ IsWeekenddag)) + ' (BWEO_Feestdag) & ' +
CONVERT(char(1),(1 ^ IsFeestdag)) +' (BWEO_Weekenddag = ' +
CONVERT(char(1),(1 ^ IsFeestdag) & (1 ^ IsWeekenddag)) AS BepalingWerkdag,
CONVERT(char(1),(1 ^ IsFeestdag) & (1 ^ IsWeekenddag)) +
' -- (' + RTRIM(Dag) + ' en ' + CASE WHEN IsFeestdag = 1 THEN 'een feestdag)'
ELSE 'geen feestdag)' END AS Werkdag,
(1 ^ IsFeestdag) & (1 ^ IsWeekenddag) AS IsWerkdag
FROM Datumtabel
WHERE (IsFeestdag = 1 AND IsWeekenddag = 0)
OR (IsFeestdag = 0 AND IsWeekenddag = 1)
OR (IsFeestdag = 1 AND IsWeekenddag = 1) -- Alle niet werkdagen
ORDER BY Datum
GO

-- De rommel achter onze kont opruimen
DROP TABLE Datumtabel

Door gebruik te maken van execution plan, live query plan en IO en TIME STATISTICS kan de impact per query worden bepaald en worden bepaald of het gebruik van bitwise operators voordeliger is. Vergeet niet om in de testomgeving de cache en buffers leeg te maken voor elke test, zodat de test zuiver wordt uitgevoerd.

Conclusie
Het gebruik van bitwise operators kan de doorlooptijd van query’s significant positief beïnvloeden en zijn daarom voor mij een welkome uitbreiding op mijn kennis (toolbox) bij query performance tuning.