Tips and Tricks for:  SQL Server, ADO.Net, ADO

The Problem:   Constructing a WHERE clause that doesn't retrieve any rows.

Sometimes you must construct a SELECT statement with a WHERE clause but you don't want to retrieve any rows.

Solution: Use WHERE 1=2  if you don't want rows.

Lets say that you're building a SELECT statement for an ADO Recordset or ADO.Net DataSet/DataAdapter.  You provide a SELECT statement and let the ADO/ADO.Net code create any INSERT Or UPDATE statement.  If all you want to do is add new rows, then you don't really want to retrieve any rows from the database.  Adding the clause WHERE 1=2 to the SELECT statement prevents if from retrieving any rows.  Information about the datastructure is returned and that's what you really wanted in the first place. 

Of course, that's sort of a lazy way out.  If you want to avoid the SELECT in the first place, use a stored procedure to add new rows.  Yes, it's more code to write but it is more efficient.

One spot where WHERE 1=2 is not the lazy way out but the only way out is in SQL Server replication.  If you want a table structure to be replicated but no rows to be included in the replica, WHERE 1=2 is the row filter clause that you want to use.

WHERE 1=1 is another lazy way out.  If you're building a WHERE clause on the fly, and you don't know if there are any more expressions in the WHERE clause, then starting with 1=1 insures that you'll create a valid WHERE clause and the SELECT won't blow up.  I don't recommend it but it works and it's quick.