|
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
------------
Related articles:
10+ common questions about SQL Server data types
bit (Transact-SQL)
|
