When it comes to checking for the existence of rows in a table, there has been a long-standing debate about whether to use SELECT 1 or SELECT *. Many discussions have revolved around performance and readability. However, in this article, we will explore a different aspect of this topic.
Recently, I ran a series of tests to compare the results and execution plans of using SELECT 1 versus SELECT * to check for the existence of rows. Surprisingly, I found that the results were the same in all the tests, and the execution plans consumed the same amount of resources.
Let’s take a look at the four tests I conducted:
Test 1: Whole Table SELECT
USE AdventureWorks;
IF EXISTS(
SELECT 1
FROM Production.Product)
SELECT 'SELECT 1';
IF EXISTS(
SELECT *
FROM Production.Product)
SELECT 'SELECT *';
Test 2: Condition WHERE on Indexed Column
USE AdventureWorks;
IF EXISTS(
SELECT 1
FROM Production.Product
WHERE Name = 'Adjustable Race')
SELECT 'SELECT 1';
IF EXISTS(
SELECT *
FROM Production.Product
WHERE Name = 'Adjustable Race')
SELECT 'SELECT *';
Test 3: Using Aggregate function COUNT
USE AdventureWorks;
IF (
SELECT 1
FROM Production.Product
WHERE Name = 'Adjustable Race') = 1
SELECT 'SELECT 1';
IF (
SELECT COUNT(*)
FROM Production.Product
WHERE Name = 'Adjustable Race') = 1
SELECT 'SELECT *';
Test 4: Using COUNT with search on non-indexed Column
USE AdventureWorks;
IF (
SELECT COUNT(1)
FROM Production.Product
WHERE SafetyStockLevel = '800') > 1
SELECT 'SELECT 1';
IF (
SELECT COUNT(*)
FROM Production.Product
WHERE SafetyStockLevel = '800') > 1
SELECT 'SELECT *';
Note: The queries above are for testing purposes only, and I have used single quotes around numeric values for simplicity.
Based on these tests, I have been using SELECT 1 instead of SELECT * when checking for the existence of rows. However, I would like to hear from my readers. What is your opinion on this matter? Please leave your comments below.