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.