How can Index be used when applying function to columns?-CodePudding

Say if I have an index on a DateTime column (named [Timestamp]), and then I use the column in the following query,

SELECT [Id], [Timestamp]
FROM [dbo].[MyTable]
WHERE FORMAT([Timestamp], 'yyyy/MM/dd HH') = '2022/12/14 01'
ORDER BY [Id]

Will the index on the [Timestamp] column be used when executing this query? Otherwise, what'll be a good strategy to go for improving the performance of the query?

CodePudding user response：

No, because you are calling a function on your column, so the index cannot know in advance which values meet your criteria. You need to avoid manipulating the column values in your WHERE clause at all costs. The best approach for comparing datetimes is usually a window compare e.g.

DECLARE @StartDate datetime2(0) = '2022/12/14 01:00:00'
    , @EndDate datetime2(0) = '2022/12/14 02:00:00';

SELECT [Id], [Timestamp]
FROM [dbo].[MyTable]
WHERE [Timestamp] >= @StartDate AND [Timestamp] < @EndDate
ORDER BY [Id];

Note: You can check whether the index is used by inspecting the execution plan.