Adding an Index using a Function

Question

We have a query that uses the following Date filtering...

AND
(
CONVERT (DATE, [Common].[dbo].[UTCtoEastern] ([Enrollment].[CreateUTC])) >= @rayn _StartDateIn
AND CONVERT (DATE, [Common].[dbo].[UTCtoEastern] ([Enrollment].[CreateUTC])) < @rayn _EndDateIn
)
OR
(
CONVERT (DATE, [Common].[dbo].[UTCtoEastern] ([Enrollment].[UpdateUTC])) >= @rayn _StartDateIn
AND CONVERT (DATE, [Common].[dbo].[UTCtoEastern] ([Enrollment].[UpdateUTC])) < @rayn _EndDateIn
)

In order to fully define and use an Index for this Function [Common].[dbo].[UTCEastern] what is the syntax?

CREATE NONCLUSTERED INDEX [IX_Enrollment_CreateUTC]
ON [Enrollment]
(
CONVERT (DATE, [Common].[dbo].[UTCtoEastern] ([Enrollment].[CreateUTC])) ASC
)
INCLUDE
(
[PatientID]
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90
)
ON [PRIMARY]
;
GO

Doesn't seem to like that Syntax...And using that Function because of Microsoft SQL Server 2014...so cannot use AT TIME ZONE

Thanks for your review and am hopeful for a reply.

Answer 1

You cannot create an index on an expression. But you can create an index on a computed column:

ALTER TABLE tbl ADD col AS dbo.UtcToEastern(CreatecUTC)
go
CREATE INDEX col_ix ON tbl(col)

The optimizer may then match that index even if you use the expression and not the column in the query. Note may.

However, note that are some restrictions:

• The function cannot reside in another database.
• The function must be created WITH SCHEMABINDING.
• The function must be precise and deterministic. That is, it must always return the same result for the same input.

Answer 2

Hi @Bobby P ,

Welcome to Microsoft Q&A!

You could not create an index based on a function in SQL Server. You can however create an index on a computed column (with restrictions, such as the computation has to use only deterministic functions).

In your example, you could firstly try with below:

ALTER TABLE dbo.[Enrollment]  
ADD   
    CreateEastern AS   
        TRY_CONVERT (DATE, [Common].[dbo].[UTCtoEastern] ([Enrollment].[CreateUTC]),101),  
    UpdateEastern AS   
        TRY_CONVERT (DATE, [Common].[dbo].[UTCtoEastern] ([Enrollment].[UpdateUTC]),101);   
  
CREATE INDEX ix_CreateEastern  
ON dbo.[Enrollment](CreateEastern);  
  
CREATE INDEX ix_UpdateEastern  
ON dbo.[Enrollment](UpdateEastern);  

If you face any error like 'Column 'CreateEastern' in table 'dbo.Enrollment' cannot be used in an index or statistics or as a partition key because it is non-deterministic.', you could refer Deterministic and Nondeterministic Functions and check whether your function is deterministic or not.

If your function is non-deterministic (e.g. when you call it with the same input parameters, there's no guarantee you'll get the same results every time), it cannot be indexed.

The only way around this would be to make it a regular column (not based on a function), and just update the value stored in that column on a regular basis.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.