Single Column Single Row and TABLE SCAN – SQL in Sixty Seconds #111

Can a single column and single row result do an entire table scan? I recently got this question in my  Comprehensive Database Performance Health Check. I think it is a fair question when we retrieve very few rows from a gigantic table, we do expect that we will read only a few rows and not a complete table.

However, the reality is very interesting. I have built this video where I am reading a single column and single row from a table and it is reading the entire table and also does a table scan. Remember table scan is not necessary is a bad thing and index seek is not necessarily a good thing. With that said, when we are retrieving a little data, we do expect that we will retrieve a little data and not the whole data from the table.

In this video, we will explore this possibility and its impact on SQL Server Performance.

Here is the script which I have used in this blog post:

USE WideWorldImporters
GO
SET STATISTICS IO ON
SELECT BillToCustomerID
FROM Sales.Invoices
WHERE InvoiceDate = '2013-12-09' 
		AND DeliveryInstructions = 'Shop 191, 435 Vladu Crescent'
OPTION (MAXDOP 1) 
GO
SELECT *
FROM Sales.Invoices
GO

The script above is written on the sample database WideWorldImporters and you can learn how to install from this link: SQL SERVER 2016 – How to Import New Sample Database WideWorldImporters. If you want to enable an execution plan, you can learn about that from this blog post: SQL SERVER – 3 Different Ways to Explore Actual Execution Plans.

If you would like to see similar videos, you can subscribe on YouTube.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.