SQL Server - boolean literal?

Question

How to write literal boolean value in SQL Server? See sample use:

select * from SomeTable where PSEUDO_TRUE

another sample:

if PSEUDO_TRUE
begin
  select 'Hello, SQL!'
end 

Note: The query above has nothing to do with how I'm going to use it. It is just to test the literal boolean.

Answer 1

Most databases will accept this:

select * from SomeTable where true

If not, they all will accept

select * from SomeTable where 1=1

When building up an sql where clause by hand, this is a cheap way to avoid having to figure if your predicate is the first, in which case it needs a WHERE or a following one, in which case it needs a AND. Using the above, everything is preceded by an AND

Answer 2

I question the value of using a Boolean in TSQL. Every time I've started wishing for Booleans & For loops I realised I was approaching the problem like a C programmer & not a SQL programmer. The problem became trivial when I switched gears.

In SQL you are manipulating SETs of data. "WHERE BOOLEAN" is ineffective, as does not change the set you are working with. You need to compare each row with something for the filter clause to be effective. The Table/Resultset is an iEnumerable, the SELECT statement is a FOREACH loop.

Yes, "WHERE IsAdmin = True" is nicer to read than "WHERE IsAdmin = 1"

Yes, "WHERE True" would be nicer than "WHERE 1=1, ..." when dynamically generating TSQL.

and maybe, passing a Boolean to a stored proc may make an if statement more readable.

But mostly, the more IF's, WHILE's & Temp Tables you have in your TSQL, the more likely you should refactor it.

Answer 3

select * from SomeTable where 1=1

Answer 4

You can use the values 'TRUE' and 'FALSE'. From https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql:

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Answer 5

select * from SomeTable where null is null

or

select * from SomeTable where null is not null

maybe this is the best performance?

Answer 6

I hope this answers the intent of the question. Although there are no Booleans in SQL Server, if you have a database that had Boolean types that was translated from Access, the phrase which works in Access was "...WHERE Foo" (Foo is the Boolean column name). It can be replaced by "...WHERE Foo<>0" ... and this works. Good luck!