SELECT 1 FROM TABLE

08 May,2018 by Jack Vamvas

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

 

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SELECT 1 FROM TABLE


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer