By: Sergey Gigoyan | Updated: 2020-06-29 | Komentarze | Powiązane: Więcej > Typy danych
Problem
W tworzeniu baz danych istnieją pewne szczególne przypadki, kiedy konieczne jest użycie typu danych Boolean. Jest to typ danych, który dopuszcza tylko dwie możliwe wartości „True” lub „False”. Zdecydowanie, jeżeli zmienna lub kolumna może mieć tylko dwie możliwe wartości, łatwiej i logiczniej będzie ustawić jej typ jako Boolean. Czy zatem SQL Server posiada typ Boolean lub podobny typ danych? W SQL Server nie ma typu danych o nazwie Boolean. Istnieje jednak typ danych zwany bitthat, który może być użyty do przechowywania wartości Boolean. W tym artykule przedstawimy i przeanalizujemy bitowy typ danych SQL Server.
Rozwiązanie
Bitowy typ danych SQL Server jest całkowitym typem danych, który może przyjąć tylko jedną z tych wartości: 0, 1, NULL. Jeśli chodzi o przechowywanie danych, to jeśli w tabeli jest mniej niż 9 kolumn z danymi bitowymi, to są one przechowywane jako 1 bajt. Jeżeli takich kolumn jest od 9 do 16, to zajmują one 2 bajty itd. W ten sposób SQL Server optymalizuje przechowywanie kolumn z bitowym typem danych. Dodatkowo, wartości łańcuchowe TRUE iFALSE mogą być konwertowane na 1 i 0 odpowiadające wartościom bitowym.
Właściwości
Poznajmy teraz właściwości i specyfikacje bitowego typu danych. Po pierwsze, jak wspomniano powyżej, ciągi 'TRUE' i 'FALSE' są konwertowane na 1 i 0. To zachowanie jest zilustrowane w poniższym przykładzie:
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
W powyższym kodzie zadeklarowaliśmy do zmiennych – jedną typu danych bit, a drugą typu danych string. Następnie przypisujemy ciąg znaków do zmiennej o typie bitowym. Jak widać, wartości 'TRUE' i 'FALSE' zostały pomyślnie przekonwertowane na odpowiednie wartości (1 i 0) bitowego typu danych:
Przypisanie dowolnego innego ciągu znaków do zmiennej bitowej powoduje jednak błąd. W naszym przykładzie przypisaliśmy 'TAK' do zmiennej bitowej i otrzymaliśmy błąd:
Po drugie, należy wspomnieć, że przypisanie dowolnej wartości niezerowej do typu bitowego konwertuje ją na 1. W kolejnym przykładzie do zmiennej typu bitowego przypisujemy liczby całkowite dodatnią i ujemną:
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
Wynik pokazuje, że w obu przypadkach wartość zmiennej bitowej wynosi 1:
Zalety bitowego typu danych serwera MySQL
Jak wspomniano powyżej, jeśli w naszej tabeli mamy mniej niż 9 kolumn o bitowym typie danych, są one przechowywane jako jeden bajt. W związku z tym, może pojawić się pytanie, dlaczego używać bitu zamiast char(1) lub tinynint, jeśli mamy tylko jedną kolumnę w tabeli przyjmującą tylko wartości logiczne? Cóż, stwórzmy środowisko testowe i zobaczmy poniższy przykład:
USE masterGO CREATE DATABASE TestDBGO USE TestDBGO CREATE TABLE .( NOT NULL PRIMARY KEY, (1) NULL, NULL, )GO
Powstała baza danych TestDB oraz przykładowa tabela przechowująca wyniki badań medycznych pacjentów. Zakładamy, że płeć może być tylko 'Male', 'Female', 'Unknown', 'Undefined' lub 'other'. Dla dwóch pierwszych przypadków będziemy używać odpowiednio 'M' i 'F', a dla trzech ostatnich 'NULL'. Aby ułatwić nasz przykład, zakłada się również, że wyniki testu mogą być albo pozytywne, albo negatywne (NULL będzie uważane za niewiadomą). Typy danych (char(1) i tinyint) wybrane w definicji tabeli nie są jednak wystarczające do zaimplementowania tej logiki. Musimy się upewnić, że wspomniane kolumny nie mogą przyjmować innych wartości. W związku z tym musimy stworzyć checkconstraints:
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
Z kolei jeśli dla kolumn Płeć i Test1Result użyjemy bitowego typu danych, nie musimy tworzyć check constraints:
USE TestDBGO DROP TABLE .GO CREATE TABLE .( NOT NULL PRIMARY KEY, NULL, NULL)GO
Dla kolumny Płeć możemy uznać 1 jako np. mężczyzna, 0 jako kobieta, a NULL jako nieznany, nieokreślony lub inny. W odniesieniu do kolumny Test1Result, możemy uznać 1 jako wartość dodatnią, a 0 jako ujemną.
Pod względem optymalizacji przechowywania danych, główną zaletą jest to, że jeśli mamy kilka kolumnBoolean, używając typu bitowego możemy znacznie zmniejszyć ilość zajmowanego miejsca. Jako przykład może posłużyć tabela, która przechowuje wyniki testów medycznych pacjentów (które są albo pozytywne albo negatywne):
USE TestDBGO DROP TABLE .GO CREATE TABLE .( NOT NULL PRIMARY KEY, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)GO
Mamy tutaj 8 kolumn typu bitowego i dlatego do przechowywania wartości używany jest 1 bajt. Gdybyśmy zamiast tego użyli tinyint lub char(1), do każdej z nich zostałby użyty 1 bajt.
Możemy również czerpać korzyści z używania bitowego typu danych w funkcjach lub procedurach przechowywanych, które przyjmują lub zwracają wartości logiczne. Załóżmy, że potrzebujemy funkcji, która sprawdzi, czy pacjent jest przebadany czy nie. Oczywiście, funkcja zwróci TRUE lub FALSE. Możemy więc użyć bitowego typu danych jako typu zwracanej wartości:
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'
Widzimy, że pacjent nie jest badany, ponieważ nie ma rekordu dla pacjenta z PatientID=1:
Wnioski
Podsumowując, bitowy typ danych może być całkiem przydatny, jeśli kolumny lub zmienne przyjmują tylko dwie wartości (plus NULL). W takich przypadkach, użycie typu bitowego zamiast typu string lub tinyint może sprawić, że kod będzie bardziej logiczny i zwarty. Dodatkowo, ponieważ SQL Server optymalizuje przechowywanie kolumn bitowych, użycie tego typu może oszczędzić pamięć masową, szczególnie jeśli w tabeli mamy więcej niż 8 kolumn z bitowym typem danych.
Następne kroki
Aby znaleźć więcej informacji na omawiany temat, proszę skorzystać z poniższych linków:
- 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
Ostatnia aktualizacja: 2020-06-29
O autorze
O autorze
O autorze the author
Sergey Gigoyan jest profesjonalistą w dziedzinie baz danych z ponad 10-letnim doświadczeniem, z naciskiem na projektowanie baz danych, rozwój, strojenie wydajności, optymalizację, wysoką dostępność, BI i projektowanie DW.Zobacz wszystkie moje wskazówki
- Więcej wskazówek dla programistów baz danych…