Adding an Index using a Function

Bobby P 221 Reputation points
2021-01-18T20:52:01.39+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,857 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,561 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.2K Reputation points
    2021-01-18T22:29:28.34+00:00

    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.
    1 person found this answer helpful.
    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2021-01-19T08:06:22.907+00:00

    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.

    0 comments No comments