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;