Door: Sergey Gigoyan | Bijgewerkt: 2020-06-29 | Reacties | Gerelateerd: Meer > Gegevenstypen
Probleem
In databaseontwikkeling zijn er enkele speciale gevallen waarin het nodig is om met het Booleaanse gegevenstype te werken. Dit is een gegevenstype dat slechts twee mogelijke waarden toestaat: “True” of “False”. Zeker, als een variabele of kolom slechts twee mogelijke waarden kan hebben, zal het eenvoudiger en logischer zijn om het type als Boolean in te stellen. Dus, heeft SQL Server Boolean of een soortgelijk datatype? In SQL Server is er geen gegevenstype dat Boolean heet. Er is echter een gegevenstype genaamd bit dat kan worden gebruikt om Booleaanse waarden op te slaan. In dit artikel introduceren en exploreren we het SQL Server bit data type.
Oplossing
SQL Server bit data type is een integer data type dat slechts een van deze waarden kan aannemen: 0, 1, NULL. Wat de opslag betreft: als de tabel minder dan 9 kolommen met bitgegevens bevat, worden deze opgeslagen als 1 byte. Indien er 9 tot 16 van dergelijke kolommen zijn, verbruiken zij 2 bytes enzovoort. SQL Server optimaliseert dus de opslag van kolommen van het bit data type. Bovendien kunnen string-waarden TRUE en FALSE worden geconverteerd naar 1 en 0 die overeenkomen met bit-waarden.
Features
Nu gaan we eens kijken naar de features en specificaties van het bit data type. Ten eerste worden, zoals hierboven al is gezegd, “TRUE” en “FALSE” strings geconverteerd naar 1 en 0. Dit gedrag wordt in het volgende voorbeeld geïllustreerd:
DECLARE @isUsed BITDECLARE @isUsedStr NCHAR(5) SELECT @isUsed AS BitType, @isUsedStr AS String --TRUE is converted to 1SET @isUsedStr='TRUE' SET @ SELECT @isUsed AS BitType, @isUsedStr AS String --FALSE is converted to 0SET @isUsedStr='FALSE' SET @ SELECT @isUsed AS BitType, @isUsedStr AS String --Assigning any other string value to a bit variable causes an errorSET @isUsedStr='YES' SET @ SELECT @isUsed AS BitType, @isUsedStr AS String
In de bovenstaande code declareren we twee variabelen – een van het bit-gegevenstype en de andere van het string-gegevenstype. Vervolgens wijzen we de string toe aan een variabele van het datatype bit. Zoals we kunnen zien, worden de waarden ‘TRUE’ en ‘FALSE’ met succes geconverteerd naar de overeenkomstige waarden (1 en 0) van het bit-gegevenstype:
Het toewijzen van een andere tekenreeks aan een bit-variabele veroorzaakt echter een fout. In ons voorbeeld hebben we ‘JA’ aan een bitvariabele toegewezen en kregen we een foutmelding:
Ten tweede is het belangrijk om te vermelden dat het toewijzen van een waarde die niet nul is aan een bitdatatype, deze omzet in 1. In het volgende voorbeeld wijzen we positieve en negatieve gehele getallen toe aan een variabele van het bitdatatype:
DECLARE @isUsed BIT SELECT @isUsed AS BitType --Assigning any nonzero value converts it to 1SET @isUsed=9 SELECT @isUsed AS BitType --Assigning any nonzero value converts it to 1SET @isUsed=-100 SELECT @isUsed AS BitType
Het resultaat laat zien dat in beide gevallen de waarde van de bitvariabele 1 is:
SQL Server Bit Data Type Voordelen
Zoals hierboven vermeld, worden kolommen van het bit data type die minder dan 9 kolommen in onze tabel hebben, opgeslagen als één byte. Daarom kan de vraag rijzen waarom we bit gebruiken in plaats van char(1) of tinynint als we maar één kolom in de tabel hebben die alleen Booleaanse waarden accepteert? Laten we een testomgeving maken en het volgende voorbeeld bekijken:
USE masterGO CREATE DATABASE TestDBGO USE TestDBGO CREATE TABLE .( NOT NULL PRIMARY KEY, (1) NULL, NULL, )GO
We hebben de TestDB-database gemaakt en een voorbeeldtabel waarin de medische testresultaten van patiënten worden opgeslagen. Aangenomen wordt dat het geslacht alleen ‘Man’, ‘Vrouw’, ‘Onbekend’, ‘Onbepaald’ of ‘overig’ kan zijn. Voor de eerste twee gevallen gebruiken wij “M” en “F”, en voor de laatste drie gevallen “NULL”. Om ons voorbeeld te vergemakkelijken, wordt ook aangenomen dat de testresultaten positief of negatief kunnen zijn (NULL wordt als onbekend beschouwd). De in de tabeldefinitie gekozen gegevenstypen (char(1) en tinyint) zijn echter niet voldoende om deze logica te implementeren. Wij moeten ervoor zorgen dat de genoemde kolommen geen andere waarden kunnen aannemen. Daarom moeten we controlebeperkingen creëren:
USE GO --Adding check constraintsALTER TABLE . WITH CHECK ADD CONSTRAINT CHECK ((='M' OR ='F'))GO ALTER TABLE . CHECK CONSTRAINT GO ALTER TABLE . WITH CHECK ADD CONSTRAINT CHECK ((=(1) OR =(0)))GO
Als we daarentegen het bit-datatype gebruiken voor de kolommen Geslacht en Test1Resultaat, hoeven we geen controlebeperkingen te creëren:
USE TestDBGO DROP TABLE .GO CREATE TABLE .( NOT NULL PRIMARY KEY, NULL, NULL)GO
Voor de kolom Geslacht kunnen we bijvoorbeeld 1 als mannelijk beschouwen, 0 als vrouwelijk en NULL als onbekend, ongedefinieerd of anderszins. Voor de Test1Result kunnen we 1 als positief en 0 als negatief beschouwen.
In termen van opslagoptimalisatie is het belangrijkste voordeel dat als we meerdere Boolean-kolommen hebben, we door gebruik te maken van het bit-type de gebruikte ruimte aanzienlijk kunnen beperken. Als eenvoudig voorbeeld kunnen we een tabel gebruiken waarin de resultaten van verschillende medische tests van patiënten (positief of negatief) worden opgeslagen:
USE TestDBGO DROP TABLE .GO CREATE TABLE .( NOT NULL PRIMARY KEY, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)GO
Hier hebben we 8 kolommen van het bit-gegevenstype en daarom wordt 1 byte gebruikt om de waarden op te slaan. Als we in plaats daarvan de tinyint of char(1) zouden gebruiken, zou voor elk van hen 1 byte worden gebruikt.
We kunnen ook voordelen ontlenen aan het gebruik van het bit-gegevenstype in functies of opgeslagen procedures die Booleaanse waarden accepteren of retourneren. Laten we eens aannemen dat we een functie nodig hebben die controleert of een patiënt is getest of niet. Het is duidelijk dat de functie WAAR of ONWAAR zal teruggeven. We kunnen dus het gegevenstype bit gebruiken als type voor de retourwaarde:
USE TestDBGO CREATE FUNCTION isPatientTested( @PatientID INT)RETURNS bitASBEGIN IF EXISTS (SELECT PatientID FROM PatientTestResults WHERE ) RETURN 1 RETURN 0 ENDGONow, we can easily call this boolean function:USE TestDBGO IF (dbo.isPatientTested(1)=1) PRINT 'The patient is tested'ELSE PRINT 'The patient is not tested'
We kunnen zien dat de patiënt niet is getest omdat er geen record is voor de patiënt met PatientID=1:
Conclusie
In conclusie, het bit data type kan heel nuttig zijn als kolommen of variabelen slechts twee waarden accepteren (plus NULL). In deze gevallen kan het gebruik van het bit type in plaats van de string of tinyint data types de code logischer en compacter maken. Aangezien SQL Server de opslag van bit-kolommen optimaliseert, kan het gebruik van dit type bovendien opslagruimte besparen, vooral wanneer we meer dan 8 kolommen van het bit-gegevenstype in de tabel hebben.
Volgende stappen
Voor meer informatie over het besproken onderwerp, volg de links hieronder:
- https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15
Laatst bijgewerkt: 2020-06-29
Over de auteur
Sergey Gigoyan is een database professional met meer dan 10 jaar ervaring, met een focus op database ontwerp, ontwikkeling, performance tuning, optimalisatie, hoge beschikbaarheid, BI en DW ontwerp.Bekijk al mijn tips
- Meer tips voor database-ontwikkelaars…