why it is doing index seek

Question

I created a sample table as below

CREATE TABLE [dbo].[StatisticsDemo](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL
   ) ON [PRIMARY]

Then I inserted below data as below:

SELECT NAME,COUNT(*) AS COUNT 
FROM StatisticsDemo 
GROUP BY NAME

NAME    COUNT
-------------
AABBCC  59999
XXYYZZ  1

Then I created below Non Clustered Index :

CREATE NONCLUSTERED INDEX [NCI_STATISTICSDEMO_NAME] ON [dbo].[StatisticsDemo]
(
    [Name] ASC
)

Now I ran the below query:

SELECT NAME FROM [dbo].[StatisticsDemo] 
WHERE NAME = 'AABBCC'

As expected it is returning 59999 rows but it is doing Index Seek on Non Clustered Index. But as per my knowledge, it should do Index Scan as 99.99% of data satisfied the filter criteria mentioned in the select query.

Can some please tell me why it is doing Index Seek instead of Index Scan?

The purpose of the entire activity was to prove(as I am about to give presentation on statistics) that SQL Server looks into statistics to identify the number of records which matches the filter criteria of query before preparing the execution plan and based on the % of records matches out of total records in table, it will either decide to do a SCAN or SEEK. If % of records matches is approximately equal to total number of records in table, it should do SCAN. But that is not happening. Same is the case when I am using AdventureWorks2016 Database and running below query:

select * from [Sales].[SalesOrderHeader] WHERE SalesOrderID >= 43659 AND 
SalesOrderID <= 73659

The above query returning 30001 records out of 31465. But it is still doing Clustered Index Seek.

I am getting terribly confused and it is shaking my concepts. :( Can some please help.

PS: I cleaned Plan cache as well but no luck. SQL Server version is 2016.

Answer 1

The purpose of the entire activity was to prove(as I am about to give presentation on statistics) that SQL Server looks into statistics to identify the number of records which matches the filter criteria of query before preparing the execution plan and based on the % of records matches out of total records in table, it will either decide to do a SCAN or SEEK. If % of records matches is approximately equal to total number of records in table, it should do SCAN.

this is incorrect so explains why you aren't seeing it. The BETWEEN 43659 AND 73659 range seek is doing a partial scan. It is just able to use the B-tree to seek into the point where to begin the scan (so avoid reading anything lower than 43659 ) and potentially exit early if there are rows with values greater than 73659.

For the rows that are in the range it just reads the pages and follows the linked list to the next leaf page in exactly the same way as an index ordered scan does.

There is no reason to want a scan here. At best it saves a handful of logical reads for navigating from the root to the leaf to find the start point but at the expense of reading additional rows outside the range seeked.

Answer 2

Your understanding is incorrect, and I believe you are confusing your case with the tipping point issue, which is an issue worth reading up on and understanding well.

With SQL Server, a seek is an operation that uses the B-tree to start or stop reading rows at specific values. Theoretically, you can have a seek read every row in the exact order a scan would.

For Oracle, and maybe other RDMS's, this operation (reading multiple rows at defined start and stop values) is called a range scan.

Here's a quick demo of a seek that reads every single row

DROP TABLE IF EXISTS dbo.SeekTest
GO

CREATE TABLE dbo.SeekTest(
ID int PRIMARY KEY CLUSTERED,
filler char(8000) --to get 1 row per page
)

INSERT dbo.SeekTest (ID)
VALUES (1),(2),(3),(4),(5)
GO

SET STATISTICS IO ON

--this one scans
SELECT *
FROM dbo.SeekTest

--this one seeks
SELECT *
FROM dbo.SeekTest
WHERE ID > 0

Remember, a seek can read multiple rows. In this demo, it reads exactly as many as the scan.