Query going for table scan

Question

Query going for table scan

Vijayrules4354

SSC Enthusiast

Points: 121
More actions
June 26, 2020 at 10:48 am

#3765151

Hi Experts,
I have a table as below and I am inserting some 500 rows to it and I am creating the non clustered index on datetime column DT. Even after having a index, the query is going for table scan. Please help me understand this.
Sample code below.
CREATE TABLE #Temp
(
ID INT IDENTITY
,Dt DATETIME DEFAULT GETDATE()
)
INSERT #Temp (DT)
VALUES (DEFAULT)
go 500
CREATE INDEX IX_#TEMP ON #TEMP(Dt)
SELECT * FROM #Temp WHERE DT='2020-06-26' -- Query going for table scan.
Thanks in advance.

Regards,

Vijay

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

So, you have 500 rows. You're adding them all with GETDATE(), then your filtering based on the todays date? Right? You realize that all 500 rows are going to have exactly the same date? Of course it's scanning. You're not filtering the data in any way. The most efficient way to retrieve all the data is to scan.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Vijayrules4354 SSC Enthusiast Points: 121 More actions · Answer 2

Yes I am filtering all the data by today's date in my example. But what if I filter by datetime since the time will be different including seconds? I tested it, still, it is going for a table scan. If I add the primary key for the identity column, then it is going for index seek.

Regards,

Vijay

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Index use depends on the amount of data it's going to filter. Take a look at the estimated number of rows each time it's doing a scan. I'll bet you it's more than 1-3 or something. The generally accepted, best guess number, the query has to be only returning .1% of the rows for a seek to be more efficient than a scan. You're testing with very common data, date, on a very small data set, 500 rows. Even with seconds tossed in, how many rows do you think are inserted across 500 rows that all have the exact same number for seconds? All of them possibly. Most of them certainly.

Here's a trick. Learn how to use statistics. Run a query against sys.dm_db_stats_histogram for the date column. Get an idea of the distribution of your data. Statistics are what drives the optimizer. Knowing how many rows it's going to estimate for a given value shows you what's more likely, scans or seeks.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Vijayrules4354 SSC Enthusiast Points: 121 More actions · Answer 4

I will follow your words. Thanks a lot for sharing the knowledge.

Regards,

Vijay