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

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

Solarwinds

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') > 1
SELECT 'SELECT 1'
GO
IF (
SELECT COUNT(*)
FROM Production.Product
WHERE SafetyStockLevel = '800') > 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)

Solarwinds
,
Previous Post
SQL SERVER – Index Reorganize or Index Rebuild
Next Post
SQL SERVER – Dynamic Case Statement – FIX : ERROR 156 : Incorrect syntax near the keyword

Related Posts

51 Comments. Leave new

  • Great Post…….

    Reply
  • Yes Sir for both the query’s it is obvious to get same performance execution plan as
    both query combination, at any scenario index or non index will going to perform table scan whole or indexed based on where clause and then next step would be to count(*) row of the table or just return 1. May be I am right or near to right Please Comment me sir
    Yes I find it fun and great observation which a Sql Developer or DBA does care of.
    I am greatest fan of urs, Ma Shaa Allah! keep it up the great work.

    Reply
  • 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
  • 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

Leave a Reply

Menu