SQL Server - boolean literal?
Most databases will accept this:
select * from SomeTable where true
However some databases (eg SQL Server, Oracle) do not have a boolean type. In these cases you may use:
select * from SomeTable where 1=1
BTW, if building up an sql where clause by hand, this is the basis for simplifying your code because you can avoid having to know if the condition you're about to add to a where clause is the first one (which should be preceded by "WHERE"
), or a subsequent one (which should be preceded by "AND"
). By always starting with "WHERE 1=1"
, all conditions (if any) added to the where clause are preceded by "AND"
.
select * from SomeTable where 1=1
SQL Server doesn't have a boolean data type. As @Mikael has indicated, the closest approximation is the bit. But that is a numeric type, not a boolean type. In addition, it only supports 2 values - 0
or 1
(and one non-value, NULL
).
SQL (standard SQL, as well as T-SQL dialect) describes a Three valued logic. The boolean type for SQL should support 3 values - TRUE
, FALSE
and UNKNOWN
(and also, the non-value NULL
). So bit
isn't actually a good match here.
Given that SQL Server has no support for the data type, we should not expect to be able to write literals of that "type".
This isn't mentioned in any of the other answers. If you want a value that orms (should) hydrate as boolean you can use
CONVERT(bit, 0) -- false CONVERT(bit, 1) -- true
This gives you a bit which is not a boolean. You cannot use that value in an if statement for example:
IF CONVERT(bit, 0)
BEGIN
print 'Yay'
END
woudl not parse. You would still need to write
IF CONVERT(bit, 0) = 0
So its not terribly useful.