By: Sergey Gigoyan｜更新日時 2020-06-29｜コメント｜関連 その他 > データ型
問題
データベース開発では、Booleanデータ型を扱う必要がある特殊なケースがあります。 これは、”True “または “False “という2つの値しか許容しないデータ型です。 確かに、変数やカラムに2つの値しか設定できない場合は、ブール型に設定した方が簡単で論理的ですね。 では、SQL ServerにはBooleanや同様のデータ型があるのでしょうか？ SQL ServerにはBooleanというデータ型はありません。 しかし、Booleanの値を格納するために使用できるbitというデータ型があります。
解決策
SQL Serverのビットデータ型は整数データ型で、次のいずれかの値のみを取ることができます。 0、1、NULL です。 保存に関しては、テーブル内にビットデータの列が9個以下の場合は、1バイトとして保存されます。 ビットデータの列が9個以下の場合は1バイトで、9～16個の場合は2バイトで保存されます。 このように、SQL Serverはビットデータ型の列の保存を最適化しています。 また、文字列のTRUEとFALSEは、ビット値に対応する1と0に変換することができます。
特徴
ここで、ビットデータ型の特徴と仕様について説明します。 まず、前述のように「TRUE」と「FALSE」の文字列は1と0に変換されますが、この動作を以下の例で説明します。
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
上のコードでは、ビットデータ型と文字列データ型の2つの変数を宣言しました。 そして、文字列をビットデータ型の変数に割り当てています。
ただし、ビット変数に他の文字列を代入するとエラーが発生します。 今回の例では、ビット変数に「YES」を代入したところ、エラーが発生しました。
次に、ビットデータ型に0以外の値を代入すると、1に変換されることを指摘しておきましょう。 次の例では、ビットデータ型の変数に正と負の整数を割り当てます：
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
結果は、どちらの場合もビット変数の値が1であることを示しています。
SQL Serverのビットデータ型の利点
前述のように、テーブル内にビットデータ型の列が9個以下の場合、それらは1バイトとして格納されます。 したがって、テーブルにブール値を受け入れる列が1つしかない場合、char(1)やtinynintではなく、なぜbitを使うのかという疑問が生じるかもしれません。
USE masterGO CREATE DATABASE TestDBGO USE TestDBGO CREATE TABLE .( NOT NULL PRIMARY KEY, (1) NULL, NULL, )GO
TestDBデータベースを作成し、患者の検査結果を格納するサンプルテーブルを作成しました。 性別は「男性」、「女性」、「不明」、「未定義」、「その他」のいずれかであると仮定します。 最初の2つのケースでは、それぞれ「M」と「F」を使用し、最後の3つのケースでは「NULL」を使用します。 また，例題を簡単にするために，テスト結果は陽性か陰性のいずれかであると仮定しています（NULLは不明とみなします）． しかし、表題の定義で選択したデータ型（char(1)とtinyint）だけでは、この論理を実装するには不十分です。 前述の列が他の値を受け入れられないようにする必要があります．
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
これに対して、Gender列とTest1Result列にbitデータ型を使用した場合は、チェック制約を作成する必要はありません。
USE TestDBGO DROP TABLE .GO CREATE TABLE .( NOT NULL PRIMARY KEY, NULL, NULL)GO
「Gender」列については、例えば「1」を男性、「0」を女性、「NULL」を不明・未定義・その他とすることができます。
ストレージの最適化という点では、主な利点は、複数のブール型カラムがある場合、ビット型を使用することで、使用するスペースを大幅に削減できることです。
USE TestDBGO DROP TABLE .GO CREATE TABLE .( NOT NULL PRIMARY KEY, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)GO
ここでは、ビットデータ型の列が8つあり、値の格納に1バイトが使用されています。
また、ブール値を受け取ったり返したりする関数やストアドプロシージャでビットデータ型を使用することで、メリットを得ることができます。 例えば、ある患者が検査を受けたかどうかをチェックする関数が必要だとします。 当然ながら、この関数はTRUEまたはFALSEを返します。
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'
PatientID=1の患者の記録がないので、その患者は検査を受けていないことがわかります。
結論
結論として、列や変数が 2 つの値 (および NULL) しか受け入れない場合、ビットデータ型は非常に便利です。 このような場合、stringやtinyintデータ型の代わりにbitデータ型を使用することで、コードをより論理的かつコンパクトにすることができます。 さらに、SQL Server はビット列の保存を最適化するため、特にテーブル内にビット データ型の列が 8 つ以上ある場合は、この型を使用することでストレージを経済的に節約することができます。
次のステップ
今回取り上げたトピックについてより詳しい情報を得るには、以下のリンクを参照してください。 2020-06-
著者について 著者について
Sergey Gigoyan は、10 年以上の経験を持つデータベースの専門家です。 データベースの設計、開発、パフォーマンスチューニング、最適化、高可用性、BIとDWの設計に重点を置いています。すべての私のTipsを見る
- More Database Developer Tips…
。