SQL Server: Select Top 0?

Question

We just came across this in an old production stored proc (there is a lot more going on in here, but this is in one leg of its logic). Why would someone ever select top 0 rows from a table? Is this some sort of SQL hack or trick I am not familiar with?

Answer 1

Its a way of getting an empty set; for example to create a new empty table with the same columns as an existing one;

SELECT TOP 0 * INTO new_table FROM old_table 

Or to act a as source for column names
Or as a way to return column details but no data to a client layer
Or as a query to check connectivity

Its the same as;

SELECT * FROM table WHERE 0=1 

Answer 2

To name columns in a UNION ALL

Be sure to read Alex K.'s answer as well. He has a lot of reasons that I have used as well. This was just the most obvious one.