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.

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