tSQL: Where 1=0

Posted By: SlackmasterK | May 12th @ 1:56 PM
page 1 of 1
Comments: 3 | Views: 1000
#May 12th @ 1:56 PM
SlackmasterK
SlackmasterK
I just came across this little gem while perusing the code of someone who recently left the company:

SELECT TOP 1 * from tblSomething WHERE 1=0

Can anyone tell me what would be the motivation here? Am I missing something? Seems to me they're looking for column names... which I would expect to look more like:

SELECT TOP 0 * from tblSomething

Wouldn't that do exactly the same thing, but with less load on the SQL server?


Edit: More did you say?

with mySqlCommand
 .CommandText = "... do stuff..."
 .Connection = mySqlConnection
 .ExecuteNonQuery()
end with

with mySqlCommand  'The same one!
 .CommandText = "... do something else..."
 .Connection = mySqlConnection
 .ExecuteNonQuery()
end with

It can't be me... It has to be the rest of the world... Bah, it's time to go rant on the blog...
#May 12th @ 2:28 PM
figuerres
figuerres
*IS* that what they are doing ??

it looks like with sql 2005 both return a null set with the column names....

but in other sql systems it might be different....

access, sybase etc.... might each handle that
top n  .... where 1=0

differently.
#May 12th @ 7:11 PM
lothan
lothan
This used to be a very common "pattern" when adding a new record to the database with VB6/ADO:

recordset.Open "SELECT * FROM tblSomething WHERE 1 = 0", ...
recordset.AddNew
...
recordset.Update

If I remember correctly, providing either TOP 0 or WHERE 1 = 0 should be just about the same thing... neither attempt to find any data in the table (e.g. both result in a constant scan that returns the table schema).

I have no idea where the TOP 1 comes from unless someone is thinking it allegedly provides some safety from pulling the entire database just in case 1 equals 0 in some form of future math.

#May 13th @ 10:00 AM
SwamiYogurt
SwamiYogurt
TOP n is not supported in Oracle, so you have to use WHERE 1 = 0. Personally, I use WHERE 0 = 1, but that's just my preference. It comes in handy when you want to clone a table without the data.

CREATE TABLE CLETUS.EMP AS
SELECT *
FROM SCOTT.EMP
WHERE 0 = 1;
page 1 of 1
Comments: 3 | Views: 1000