Sql Read 'on' as True or 1

By:   |   Updated: 2020-06-29   |   Comments (1)   |   Related: More than > Information Types

Problem

In database development, there are some special cases when it is necessary to work with the Boolean data type. This is a data type that allows just two possible values "Truthful" or "False". Definitely, if a variable or column can only have two possible values, it will be easier and logical to set its type as Boolean. Thus, does SQL Server have Boolean or a similar data blazon? In SQL Server, there is no information blazon called Boolean. However, in that location is a information type chosen scrap that can be used to store Boolean values. In this article, nosotros volition introduce and explore SQL Server bit data type.

Solution

SQL Server chip data blazon is an integer data blazon that can accept only one of these values: 0, ane, Naught. With regard to the storage, if there are less than nine columns of the bit information in the table, they are stored as 1 byte. If there are 9 to 16 such columns, they consume 2 bytes and and so on. Thus, SQL Server optimizes the storage of columns of the flake data type. Additionally, cord values TRUE and Fake can be converted to 1 and 0 corresponding to chip values.

Features

Now, let's explore the features and specifications of the bit data type. First, as it is mentioned in a higher place, 'TRUE' and 'FALSE' strings are converted to 1 and 0. This beliefs is illustrated in the following example:

DECLARE @isUsed Chip DECLARE @isUsedStr NCHAR(v)   SELECT @isUsed AS BitType, @isUsedStr Every bit Cord   --True is converted to ane Gear up @isUsedStr='True'   Fix @isUsed=@isUsedStr   SELECT @isUsed Equally BitType, @isUsedStr Every bit Cord   --FALSE is converted to 0 Set up @isUsedStr='Fake'   SET @isUsed=@isUsedStr   SELECT @isUsed Every bit BitType, @isUsedStr Equally String   --Assigning whatsoever other cord value to a scrap variable causes an error Fix @isUsedStr='YES'   SET @isUsed=@isUsedStr   SELECT @isUsed As BitType, @isUsedStr AS String          

In the code above, we declared to variables – one of the scrap and other of the string data type. And then, we assign the cord to a variable of the bit data type. As nosotros can come across, the values 'True' and 'False' are successfully converted to the respective values (i and 0) of the scrap data type:

Assigning any other string to a bit variable, nonetheless, causes an error. In our case, we assigned 'YES' to a flake variable and received an error:

Secondly, it is important to mention that assigning any non zero value to a scrap information type converts it to 1. In the next example, nosotros assign positive and negative integers to a variable of the fleck datatype:

DECLARE @isUsed Fleck   SELECT @isUsed Every bit BitType   --Assigning whatsoever nonzero value converts it to 1 Set up @isUsed=9   SELECT @isUsed AS BitType   --Assigning any nonzero value converts it to 1 SET @isUsed=-100   SELECT @isUsed AS BitType          

The consequence shows that in both cases, the value of the bit variable is 1:

SQL Server Fleck Data Type Advantages

As it is mentioned in a higher place, if nosotros accept less than nine columns of the bit information type in our table, they are stored equally 1 byte. Therefore, the question might ascend why apply the bit instead of char(1) or tinynint if we have only i column in the tabular array accepting only Boolean values? Well, let's create a test surround and come across the following example:

USE master GO   CREATE DATABASE TestDB GO   Use TestDB GO   CREATE Table [dbo].[PatientTestResults] (    [PatientID] [int] NOT NULL PRIMARY KEY,    [Gender] [char](i) NULL,    [Test1Result] [tinyint] Cypher,   ) GO          

We have created the TestDB database and sample table storing patients' medical exam results. Information technology is causeless, that gender can be only 'Male', 'Female', 'Unknown', 'Undefined' or 'other'. For the beginning two cases, we will employ 'Chiliad' and 'F' correspondingly and for the last three cases, nosotros will utilise 'NULL'. To make our example easier, it is also assumed that test results tin can be either positive or negative (NULL will be considered as unknown). The data types (char(1) and tinyint) called in the table definition, however, are not plenty to implement this logic. Nosotros need to ensure that the mentioned columns cannot have other values. Therefore, nosotros demand to create check constraints:

Apply [TestDB] GO    --Adding check constraints Change Table [dbo].[PatientTestResults]  WITH CHECK ADD  CONSTRAINT [CK_PatientTestResults_Gender] Bank check  (([Gender]='Chiliad' OR [Gender]='F')) Become   ALTER TABLE [dbo].[PatientTestResults] Check CONSTRAINT [CK_PatientTestResults_Gender] Become   ALTER TABLE [dbo].[PatientTestResults]  WITH Bank check Add  CONSTRAINT [CK_PatientTestResults_Test1Results] Bank check  (([Test1Result]=(ane) OR [Test1Result]=(0))) Get          

In contrast, if we apply the bit datatype for the Gender and Test1Result columns, we do not need to create check constraints:

Utilize TestDB Go   DROP Table [dbo].[PatientTestResults] Become   CREATE TABLE [dbo].[PatientTestResults] (    [PatientID] [int] NOT NULL PRIMARY KEY,    [Gender] [bit] NULL,    [Test1Result] [bit] NULL ) GO          

For the Gender column, we can consider 1 equally a male, for example, 0 as a female and NULL every bit unknown, undefined or other. With regard to the Test1Result, we can consider 1 every bit a positive and 0 as a negative.

In terms of storage optimization, the main advantage is that if we have several Boolean columns, using bit type we can significantly reduce the space used. As a simple instance, we tin can use a tabular array that stores the patients' several medical test results (which are either positive or negative):

Use TestDB GO   Driblet Tabular array [dbo].[PatientTestResults] GO   CREATE Table [dbo].[PatientTestResults] (    [PatientID] [int] Not Nada Primary KEY,    [Gender] [scrap] NULL,    [Test1Result] [bit] NULL,    [Test2Result] [bit] Cypher,    [Test3Result] [bit] Null,    [Test4Result] [bit] Cipher,    [Test5Result] [bit] Zero,    [Test6Result] [bit] NULL,    [Test7esult] [fleck] Zilch ) Go          

Hither we have eight columns of the bit data type and, therefore, 1 byte is used to store the values. If we used the tinyint or char(1) instead, 1 byte would be used for each of them.

We can also derive benefits from using the scrap data type in functions or stored procedures that accept or return Boolean values. Let's assume we need a function that will check whether a patient is tested or non. Obviously, the role volition render Truthful or Simulated. Thus, nosotros tin can utilise the scrap data type every bit a return value type:

USE TestDB GO   CREATE FUNCTION isPatientTested (    @PatientID INT ) RETURNS flake AS Begin        IF EXISTS (SELECT PatientID FROM PatientTestResults WHERE PatientID=@PatientID )       Render one      Return 0   Finish GO Now, nosotros tin hands call this boolean part: Utilise TestDB Get   IF (dbo.isPatientTested(1)=1)    Impress 'The patient is tested' ELSE     PRINT 'The patient is not tested'          

We can come across that the patient is not tested as there isn't a record for the patient with PatientID=1:

Conclusion

In determination, the fleck data type tin can exist quite useful if columns or variables accept merely two values (plus Cipher). In these cases, using the bit type instead of the string or tinyint information types can make the code more logical and compact. Additionally, as SQL Server optimizes the storage of chip columns, the usage of this type can economically save storage, especially if nosotros have more than 8 columns of flake information blazon in the table.

Next Steps

To find more information most the discussed topic, delight follow the links below:

• https://docs.microsoft.com/en-us/sql/t-sql/information-types/chip-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

Related Articles

Pop Articles

Nearly the author

Sergey Gigoyan is a database professional with more than than 10 years of feel, with a focus on database design, development, operation tuning, optimization, high availability, BI and DW blueprint.

View all my tips

Article Last Updated: 2020-06-29

bowersansters.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/6447/sql-server-bit-data-type/

Share this post