How Many Different Values Can be Stored in Bit Data Type - SQL Server Tutorial

Bit is integer data type that can store 0,1 or Null. Bit take One byte ( 8 Bits) in memory to store these values for us. We often use this data type for the columns where we want to show if the record is active or In- Active. You can call those type of columns as status columns/ Flag Columns etc.

As we know that bit can store only three possible values but when it comes to inserting that values it is Bit confusing. If you will insert 'TRUE' , It will take that as 1. If you will insert 'False', SQL Server will translate that to 0. There are more confusing part to it;) I suggest to watch below video to get more details.

Let's see if we try to insert some values in BIT data type column what SQL Server will do to them.

USE TestDB
go

  Create table dbo.CustomerAddress ( 
  FName VARCHAR(100),
  LName VARCHAR(100),
  HouseNumber Integer,
  StreetName VARCHAR(100),
  City VARCHAR(100),State CHAR(2),
  IsActive BIT)
  go

  insert into dbo.CustomerAddress 
  values ('Aamir','Shahzad',123,'Test Street','Charlotte','NC',1)
  go
  insert into dbo.CustomerAddress 
  values ('John','Smith',Null,'ELM Street','Charlotte','NC',0)
  go
    insert into dbo.CustomerAddress 
  values ('Mike','Rivers',Null,'East West Street','Charlotte','NC',NULL)
  go
  insert into dbo.CustomerAddress 
  values ('Kathy','Robert',Null,'King Street','Charlotte','NC','     ')
  go
  insert into dbo.CustomerAddress 
  values ('Raza','Ali',Null,'King Street','Charlotte','NC','True')
  go
  insert into dbo.CustomerAddress 
  values ('Julie','Jason',Null,'King Street','Charlotte','NC','False')
  go
    insert into dbo.CustomerAddress 
  values ('Julie','Jason',Null,'King Street','Charlotte','NC','1900000')
  go
    insert into dbo.CustomerAddress 
  values ('Julie','Jason',Null,'King Street','Charlotte','NC','-1')
  go 
 

 The last row did not insert but all rest completed successfully. We got error as shown below when try to insert '-1' in Bit Data type column.

(1 row(s) affected)
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '-1' to data type bit.


Let's run Select Statement and see how SQL Translated our values in BIT data type column.

  Select * from dbo.CustomerAddress
GO 
Introduction to BIT data Type in SQL Server


  




Video Link: Learn about BIT Data Type in SQL Server