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)