DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to use the Boolean / bit data type?

A Microsoft SQL Server bit (Boolean, logical true/false) data type column can store 0, 1 or NULL values. 0 by convention means false, 1 means true. The following T-SQL scripts demonstrate usage.

-- Indicate if a row is deleted/inactive (marked "deleted")

USE tempdb;

SELECT *, IsDeleted = CONVERT(bit,0) INTO Product

FROM AdventureWorks2008.Production.Product

GO

-- Simulate delete action

UPDATE TOP(25) Product SET IsDeleted = 1

GO

-- Check "deleted" records

SELECT ProductNumber FROM Product

WHERE IsDeleted = 1

GO

/* ProductNumber

AR-5381

BA-8327

BE-2349

BE-2908

..... */

-- Cleanup

DROP TABLE tempdb.dbo.Product

GO
------------

 

------------

-- Using TRUE (1) and FALSE (0) for Boolean table column population

------------

USE tempdb;

GO

CREATE TABLE Boolean (

      ID INT IDENTITY(1,1) PRIMARY KEY,

      Boolean bit,

      ModifiedDate date default (getdate())

);

 

INSERT Boolean(Boolean) VALUES (1);

INSERT Boolean(Boolean) VALUES (0);

INSERT Boolean(Boolean) VALUES ('TRUE');

INSERT Boolean(Boolean) VALUES ('FALSE');

 

SELECT * FROM Boolean

GO

/*    ID Boolean     ModifiedDate

      1     1           2010-10-03

      2     0           2010-10-03

      3     1           2010-10-03

      4     0           2010-10-03   */

     

 

DROP TABLE tempdb.dbo.Boolean

------------

 

------------

-- Logical bitwise operations on bit/Boolean data type

------------

-- Logical bitwise AND operations

SELECT CONVERT(bit,1) & CONVERT(bit,1)    -- 1

SELECT CONVERT(bit,1) & CONVERT(bit,0)    -- 0

SELECT CONVERT(bit,0) & CONVERT(bit,1)    -- 0

SELECT CONVERT(bit,0) & CONVERT(bit,0)    -- 0

 

-- Logical bitwise OR operations

SELECT CONVERT(bit,1) | CONVERT(bit,1)    -- 1

SELECT CONVERT(bit,1) | CONVERT(bit,0)    -- 1

SELECT CONVERT(bit,0) | CONVERT(bit,1)    -- 1

SELECT CONVERT(bit,0) | CONVERT(bit,0)    -- 0

------------

 

-- Logical bitwise NOT operation

DECLARE @Boolean bit = 0

SET @Boolean = ~ @Boolean

SELECT @Boolean   -- 1

------------

 

-- 64 bit pattern

DECLARE @BitPattern BIGINT = 0;

SELECT CONVERT(binary(8), @BitPattern); --0x0000000000000000

 

-- Turn on bit 50

SELECT @BitPattern += POWER (convert(bigint,2), 50);

SELECT CONVERT(binary(8), @BitPattern);  -- 0x0004000000000000

 

-- Test bit 50 & 51 & 49

SELECT IIF(@BitPattern & POWER (convert(bigint,2), 50) > 0, 'ON', 'OFF');  -- ON

 

SELECT IIF(@BitPattern & POWER (convert(bigint,2), 51) > 0, 'ON', 'OFF');  -- OFF

 

SELECT IIF(@BitPattern & POWER (convert(bigint,2), 49) > 0, 'ON', 'OFF');  -- OFF

 

-- Turn off bit 50

SELECT @BitPattern -= POWER (convert(bigint,2), 50);

 

SELECT IIF(@BitPattern & POWER (convert(bigint,2), 50) > 0, 'ON', 'OFF');  -- OFF

 

------------ 

 

Related articles:

10+ common questions about SQL Server data types

bit (Transact-SQL)

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE