Question: Does a SQL Server bit value equal 1 or TRUE?
Answer: A bit column stores 0, 1 or NULL. SQL Server also accepts the character strings 'true' and 'false' when converting a value to bit. That doesn’t make TRUE a T-SQL Boolean literal you should substitute for 1 in a predicate. A small experiment shows the difference.
DECLARE @myBIT bit;
SET @myBIT = 1;
SELECT @myBIT AS Val;The result is 1. Now assign the string 'true'. SQL Server converts it to the same stored bit value:
DECLARE @myBIT bit;
SET @myBIT = 'true';
SELECT @myBIT AS Val;It produces the same 1. The inverse string 'false' converts to 0. The strings are conversion inputs, not the way the value is stored.
DECLARE @myBIT bit;
SET @myBIT = 'nice';
SELECT @myBIT AS Val;This assignment fails with conversion error 245. Putting both assignments side by side makes the point:
DECLARE @myBITtrue bit, @myBITone bit;
SET @myBITtrue = 'true';
SET @myBITone = 1;
SELECT @myBITtrue AS myBITtrue, @myBITone AS myBITone;
The string '1' also converts to 1. In the interview, I would say: use 1 or 0 for ordinary T-SQL comparisons; recognize 'true' and 'false' as supported string conversions to bit. This is one of the quick questions we discuss while a Comprehensive Database Performance Health Check team is gathering.
‘true’ is not a Boolean in T-SQL, it is a string that SQL Server converts to 1.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
4 Comments. Leave new
DECLARE @myBIT BIT;
SET @myBIT = 0;
SELECT @myBIT AS myBIT;
SET @myBIT = 1;
SELECT @myBIT AS myBIT;
SET @myBIT = -888656678;
SELECT @myBIT AS myBIT;
SET @myBIT = 656678;
SELECT @myBIT AS myBIT;
All other numbers than 0 gives 1!!!!
The BIT data type has had lots of problems. At one point it was a true BIT data type, allowing only the values {1,0} . Later it was made into a numeric data type, and as such, it had to allow the values {0,1, NULL}. Older SQL Server programmers simply assumed that a BIT variable would not be NULL, so they never added a NOT NULL constraint in the DDL. Finding this when you upgraded from one release of SQL Server to the next one was an unwelcome surprise. But perhaps more fundamentally, you should not write with BIT flags in SQL; this is a declarative language and not assembler.
That is the golden statement, sir. It is declarative language and not assembler.
But in a declarative language, it is possible to have an information, that only have to different values – and this is not assembler. And declare such an information as CHAR(1), CHAR(3), SMALLINT, … is not more or less assembler than declare it as BIT. And declare it as BIT is not assembler. Why is it assembler?