SQL Server: Select Top 0?

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 se开发者_StackOverflow中文版lect top 0 rows from a table? Is this some sort of SQL hack or trick I am not familiar with?

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

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.

By doing this, you have an empty resultset with all columns instead of no result. If the program using the stored procedure expects certain columns, it would crash otherwise.

You could use this to grab the column names.

Use this to create a temporary table where the collation of your DB and TEMPDB may differ.

SELECT TOP(0) column INTO #temp FROM [mytable]

Produces a temp table with same collation as my table. This then means

SELECT * FROM #temp T INNER JOIN [mytable] M ON M.column=T.column

Does not fail due to a collation error.

Let me provide an additional use:

If you want a message printed in the output rather than in messages, you could use

select top 0 0 'Your message here'

instead of

print 'Your message here'

You can use this to check if your SQL syntax is correct without loading any data.

I have mostly used it when creating temp tables from an existing DB table - without any data. The following is more reliable though than using top.

SELECT * FROM <table_name> LIMIT 0;