SQL Server Index on smalldatetime column

ct2axkht 于 2023-05-05 发布在其他

关注(0)|答案(1)|浏览(165)

I have a table with the following columns

[ID] [int] IDENTITY(1,1) NOT NULL,
[Timestamp] [datetime] NOT NULL,
[Datestamp] [smalldatetime] NOT NULL,
[Message] [nvarchar](max) NULL

There is clustered index and PK on ID column.
There is also non clustered index on Datestamp.

Datastamp column contains date in format like 2021-10-04 00:00:00

What I read is that index on Datestamp column will optimize filtering on date. Indeed this is the query plan when selecting ID filtered by date.

select ID
from [Log]
where [Datestamp] > '2020-01-01' and [Datestamp] < '2021-09-01'
order by datestamp

However when I select Message and filter on Datestamp I can see query plan now involve Sort operator that would make this query non performant. Also the non clustered index on Datestamp is no longer used.

select Message
from [Log]
where [Datestamp] > '2020-01-01' and [Datestamp] < '2021-09-01'
order by datestamp

Any idea why this is happening?
Why is the non clustered index being used when selecting the Message column?

sql-server

来源：https://stackoverflow.com/questions/69429326/index-on-smalldatetime-column

1条答案

按热度按时间

idfiyjo81#

When you select the ID column, the non-clustered index on DateStamp contains:

the copy of DateStamp column,
the reference to the primary key, which is clustered index on ID column.

So, when using query:

select ID
from [Log]
where [Datestamp] > '2020-01-01' and [Datestamp] < '2021-09-01'
order by datestamp

the IX_Log_DateStamp index covers the whole query.

But when you select the Message column in a query:

select Message
from [Log]
where [Datestamp] > '2020-01-01' and [Datestamp] < '2021-09-01'
order by datestamp

the IX_Log_DateStamp index cannot cover that column.

So there is generated a query plan that uses the fastest option here (for your data) - searching the primary key (clustered index) for filtering by DateStamp column.

The query optimizer does not choose to use the non-clustered index here because there is no reference to the Message column directly. It should use joining the clustered index and searching for the Message column, but it uses the fastest way here - scanning and sorting the clustered index.

If you want to achieve the execution plan:

you should use INCLUDE in index creating:

DROP INDEX [IX_Log_DateStamp] ON [dbo].[Log];
GO

CREATE NONCLUSTERED INDEX [IX_Log_DateStamp]
ON [dbo].[Log] ([DateStamp] ASC)
INCLUDE([Message])
GO

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server Index on smalldatetime column

1条答案

相关问题

热门标签

最新问答