SELECT 1 FROM TABLE

Question: I'm working on an Enterprise application upgrade and have hit some problems. As part of the debugging process I'm inspecting the sql queries executing. The program is currently running SELECT 1 FROM TABLE. (replace TABLE with actual table name).

What is the purpose of SELECT 1 FROM TABLE ?

Answer: The SELECT 1 FROM TABLE technique is often used in certain products.   The first question is what does this  sql statement actually do?

When you execute SELECT 1 FROM TABLE , there will be a Scan , because the purpose of SELECT 1 FROM TABLE is to return a row for every row in the table. In terms of the information it returns - it's a pointless recordset , because it returns "1" for every row, and if you were interested in getting the COUNT() then it's quicker to grab the results from SELECT COUNT() .

In reality - the SELECT 1 is normally part of wider sequence of code - for example -  EXISTS construct .    If you compared a SELECT * from a SELECT 1 in an EXISTS construct - at least using SELECT 1   would avoid any extra  metadata checking , as you've avoided expanding the table columns

If you're curious , check out  the SQL Excecution plans - and compare SELECT *  FROM TABLE and SELECT 1 FROM TABLE. Depending on the indexes and the table definition you may get some slight variation .

SQL Performance tuning - Asking the right question (SQL Server DBA)

Performance Tuning by Committee - SQL Antipattern #006