I have worked with SQL Server bit columns for years, yet even so, to my surprise I just learned that you can not only use 1 and 0, you can also use the literals 'true' and 'false'. Note that they are not explicitly bit data types already, but like 1 and 0 implicitly convert to bit with no problem:

DECLARE @Doors TABLE (
    DoorID int NOT NULL identity(1,1),
    IsOpen bit NOT NULL
);

INSERT @Doors (IsOpen) VALUES ('True');
INSERT @Doors (IsOpen) VALUES (0);
INSERT @Doors (IsOpen) VALUES ('False');
INSERT @Doors (IsOpen) VALUES (1);

SELECT * FROM @Doors;
SELECT * FROM @Doors WHERE DoorID = 3 OR IsOpen = 'True';

Examination of execution plans will show that using 'true' and 'false' is slightly different from 1 and 0 since a "convert implicit" operation is excluded in some cases with them.

Published under: SQL Server Tips ·  ·  ·  ·