Von: Sergey Gigoyan | Aktualisiert: 2020-06-29 | Comments | Related: Mehr > Datentypen
Problem
In der Datenbankentwicklung gibt es einige spezielle Fälle, in denen es notwendig ist, mit dem booleschen Datentyp zu arbeiten. Dies ist ein Datentyp, der nur zwei mögliche Werte „Wahr“ oder „Falsch“ zulässt. Wenn eine Variable oder Spalte nur zwei mögliche Werte haben kann, ist es auf jeden Fall einfacher und logischer, ihren Typ als Boolean zu definieren. Verfügt SQL Server also über Boolean oder einen ähnlichen Datentyp? In SQL Server gibt es keinen Datentyp namens Boolean. Es gibt jedoch einen Datentyp namens Bit, der verwendet werden kann, um boolesche Werte zu speichern. In diesem Artikel werden wir den SQL Server Bit-Datentyp vorstellen und untersuchen.
Lösung
Der SQL Server Bit-Datentyp ist ein Integer-Datentyp, der nur einen der folgenden Werte annehmen kann: 0, 1, NULL. Was die Speicherung angeht, so werden die Bitdaten, wenn weniger als 9 Spalten in der Tabelle vorhanden sind, als 1 Byte gespeichert. Wenn es 9 bis 16 solcher Spalten gibt, verbrauchen sie 2 Byte und so weiter. Auf diese Weise optimiert SQL Server die Speicherung von Spalten des Bit-Datentyps. Zusätzlich können die String-Werte TRUE undFALSE in 1 und 0 konvertiert werden, die den Bit-Werten entsprechen.
Features
Nun wollen wir uns die Features und Spezifikationen des Bit-Datentyps ansehen. Zunächst werden, wie oben erwähnt, die Zeichenketten ‚TRUE‘ und ‚FALSE‘ in 1 und 0 umgewandelt. Dieses Verhalten wird im folgenden Beispiel veranschaulicht:
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
Im obigen Code haben wir zwei Variablen deklariert – eine vom Datentyp Bit und eine vom Datentyp String. Dann weisen wir die Zeichenkette einer Variablen des Datentyps Bit zu. Wie wir sehen, werden die Werte ‚TRUE‘ und ‚FALSE‘ erfolgreich in die entsprechenden Werte (1 und 0) des Bit-Datentyps umgewandelt:
Wenn wir einer Bit-Variablen eine andere Zeichenkette zuweisen, führt dies jedoch zu einem Fehler. In unserem Beispiel haben wir einer Bitvariablen ‚YES‘ zugewiesen und einen Fehler erhalten:
Zweitens ist es wichtig zu erwähnen, dass die Zuweisung eines beliebigen Wertes, der nicht Null ist, an einen Bitdatentyp diesen in 1 konvertiert. Im nächsten Beispiel weisen wir einer Variablen vom Bit-Datentyp positive und negative Ganzzahlen zu:
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
Das Ergebnis zeigt, dass in beiden Fällen der Wert der Bit-Variablen 1 ist:
SQL-Server-Bit-Datentyp-Vorteile
Wie oben erwähnt, werden Spalten des Bit-Datentyps, wenn wir weniger als 9 Spalten in unserer Tabelle haben, als ein Byte gespeichert. Daher könnte die Frage aufkommen, warum wir Bit anstelle von char(1) oder tinynint verwenden, wenn wir nur eine Spalte in der Tabelle haben, die nur boolesche Werte annimmt? Nun, erstellen wir eine Testumgebung und sehen uns folgendes Beispiel an:
USE masterGO CREATE DATABASE TestDBGO USE TestDBGO CREATE TABLE .( NOT NULL PRIMARY KEY, (1) NULL, NULL, )GO
Wir haben die Datenbank TestDB und eine Beispieltabelle erstellt, in der die medizinischen Testergebnisse der Patienten gespeichert werden. Es wird angenommen, dass das Geschlecht nur ‚Männlich‘, ‚Weiblich‘, ‚Unbekannt‘, ‚Undefiniert‘ oder ‚Andere‘ sein kann. Für die ersten beiden Fälle verwenden wir entsprechend ‚M‘ und ‚F‘ und für die letzten drei Fälle verwenden wir ‚NULL‘. Um unser Beispiel zu vereinfachen, wird außerdem angenommen, dass die Testergebnisse entweder positiv oder negativ sein können (NULL wird als unbekannt betrachtet). Die in der Tabellendefinition gewählten Datentypen (char(1) und tinyint) reichen jedoch nicht aus, um diese Logik zu implementieren. Wir müssen sicherstellen, dass die genannten Spalten keine anderen Werte annehmen können. Daher müssen wir Checkconstraints erstellen:
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
Wenn wir dagegen den Bit-Datentyp für die Spalten Geschlecht und Test1Ergebnis verwenden, müssen wir keine Checkconstraints erstellen:
USE TestDBGO DROP TABLE .GO CREATE TABLE .( NOT NULL PRIMARY KEY, NULL, NULL)GO
Für die Spalte Geschlecht können wir beispielsweise 1 als männlich, 0 als weiblich und NULL als unbekannt, undefiniert oder Sonstiges betrachten. Bei der Spalte Test1Result können wir 1 als positiv und 0 als negativ betrachten.
In Bezug auf die Speicheroptimierung besteht der Hauptvorteil darin, dass wir bei mehrerenBooleschen Spalten durch die Verwendung von Bittypen den Platzbedarf erheblich reduzieren können. Als einfaches Beispiel können wir eine Tabelle verwenden, die die verschiedenen medizinischen Testergebnisse der Patienten speichert (die entweder positiv oder negativ sind):
USE TestDBGO DROP TABLE .GO CREATE TABLE .( NOT NULL PRIMARY KEY, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)GO
Hier haben wir 8 Spalten des Bit-Datentyps und daher wird 1 Byte zum Speichern der Werte verwendet. Würden wir stattdessen tinyint oder char(1) verwenden, würde jeweils 1 Byte verwendet.
Wir können auch Vorteile aus der Verwendung des Bit-Datentyps in Funktionen oder Storedprocedures ziehen, die boolesche Werte akzeptieren oder zurückgeben. Nehmen wir an, wir benötigen eine Funktion, die prüft, ob ein Patient getestet wird oder nicht. Offensichtlich wird die Funktion TRUE oder FALSE zurückgeben. Daher können wir den Datentyp Bit als Rückgabetyp verwenden:
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'
Wir können sehen, dass der Patient nicht getestet wird, da es keinen Datensatz für den Patienten mit PatientID=1 gibt:
Fazit
Zusammenfassend kann der Bit-Datentyp recht nützlich sein, wenn Spalten oder Variablen nur zwei Werte (plus NULL) akzeptieren. In diesen Fällen kann die Verwendung des Bit-Typs anstelle des String- oder tinyint-Datentyps den Code logischer und kompakter machen. Da SQL Server die Speicherung von Bit-Spalten optimiert, kann die Verwendung dieses Typs außerdem Speicherplatz einsparen, insbesondere wenn mehr als 8 Spalten vom Bit-Datentyp in der Tabelle vorhanden sind.
Nächste Schritte
Weitere Informationen zum besprochenen Thema finden Sie unter den folgenden Links:
- 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
Letzte Aktualisierung: 2020-06-29
Über den den Autor
Sergey Gigoyan ist ein Datenbankprofi mit mehr als 10 Jahren Erfahrung, mit Schwerpunkt auf Datenbankdesign, Entwicklung, Performance-Tuning, Optimierung, Hochverfügbarkeit, BI und DW-Design.Alle meine Tipps ansehen
- Mehr Tipps für Datenbankentwickler…