SQL SERVER – SELECT 1 vs SELECT * – An Interesting Observation

February 26, 2008

Many times I have seen the issue of SELECT 1 vs SELECT * discussed in terms of performance or readability while checking for the existence of rows in the table. I ran quick 4 tests about this observed that I am getting same result when used SELECT 1 and SELECT *. I think smart readers of this blog will come up the situation when SELECT 1 and SELECT * have a different execution plan when used to find the existence of rows.

Let us see 4 tests I ran and note their result is same and their Execution Plan take the same amount of resources (50% and 50%)

You can click on the images to see larger images.

Test 1: Whole Table SELECT

USE AdventureWorks
GO
IF EXISTS(
SELECT 1
FROM Production.Product)
SELECT 'SELECT 1'
GO
IF EXISTS(
SELECT *
FROM Production.Product)
SELECT 'SELECT *'
GO

SQL SERVER - SELECT 1 vs SELECT * - An Interesting Observation select1s

Test 2: Condition WHERE on Indexed Column

USE AdventureWorks
GO
IF EXISTS(
SELECT 1
FROM Production.Product
WHERE Name = 'Adjustable Race')
SELECT 'SELECT 1'
GO
IF EXISTS(
SELECT *
FROM Production.Product
WHERE Name = 'Adjustable Race')
SELECT 'SELECT *'
GO

SQL SERVER - SELECT 1 vs SELECT * - An Interesting Observation select2s

Test 3: Using Aggregate function COUNT

USE AdventureWorks
GO
IF (
SELECT 1
FROM Production.Product
WHERE Name = 'Adjustable Race') = 1
SELECT 'SELECT 1'
GO
IF (
SELECT COUNT(*)
FROM Production.Product
WHERE Name = 'Adjustable Race') = 1
SELECT 'SELECT *'
GO

SQL SERVER - SELECT 1 vs SELECT * - An Interesting Observation select3s

Test 4: Using COUNT with search on non-indexed Column

USE AdventureWorks
GO
IF (
SELECT COUNT(1)
FROM Production.Product
WHERE SafetyStockLevel = '800') &amp;gt; 1
SELECT 'SELECT 1'
GO
IF (
SELECT COUNT(*)
FROM Production.Product
WHERE SafetyStockLevel = '800') &amp;gt; 1
SELECT 'SELECT *'
GO

SQL SERVER - SELECT 1 vs SELECT * - An Interesting Observation select4s

Note: I have used single quotes around 800, which is numeric and I am aware of that fact. Above queries are for testing purpose only.

I have been using SELECT 1 instead of SELECT * when checking existence of rows. I would like to see what my readers have an opinion about this. Please have your opinion and make your comment here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server

SQL SERVER – Index Reorganize or Index Rebuild

SQL SERVER – Dynamic Case Statement – FIX : ERROR 156 : Incorrect syntax near the keyword

51 Comments. Leave new

parasuraj
October 27, 2015 11:48 am
May i know what is the use of following query. When should we use this
Select 1%1
Reply
- Kaushik Halvadia
  June 2, 2016 5:40 pm
  It returns remainder(modulus operator in other language)
  So, if you run
  Select 10%3
  it will return 1
  Reply
neeraj prasad sharma
February 22, 2017 10:42 am
Quite an old post, but i believe people still find it difficult to understand what to use.
i have seen top 1 , * or 1 in the if exists clause and all these doesn`t matter at all (bold).
AS query optimizer knows we are not actually returning anything, it is just a check for existing of some condition, so there is always nothing (bold) you will notice in the output column in the execution plan.
To make it more enjoyable I suggest use select 1/0 in the if exists code. Usually it should throw:
( msg: 8134 divide by error msg) error but it won’t because it really doesn`t matter (bold).
Reply
neeraj prasad sharma
February 22, 2017 11:06 am
In my experience i have`t seen may people count(*) without having clause (when it really required)
if you are using count(*) as a replacement of select * then please do not use it at all even its well index as it would be just a bad practice.
Reply
- Pinal Dave
  February 22, 2017 8:37 pm
  Thanks for sharing it Neeraj.
  Reply
Gatej Alexandru
April 17, 2017 1:40 am
I believe that it doesn’t matter what you have 1 or *. It’s not evaluated when you use exists. Why? Simple try to put 1/0. Normally should give you : Divide by zero error encountered . But if you are trying if exists(select 1/0 …..) it doesn’t give you the error.
Reply
Rick Bielawski
January 2, 2019 8:17 pm
It absolutely DOES MATTER what you specify in certain circumstances. Specifically, when ownership chaining DOES NOT apply. If either 1 or * (or 1/0) is specified then security is checked on ALL columns of every table referenced by the query. IOW the added overhead of checking that the caller has access to every column in every table DOES take place. If an actual column is named then only columns actually named in the query are checked.
So while it may not matter from a query plan perspective it doesn’t matter from an overhead perspective when the query and the table have different owners.
Reply