CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
InquisitiveLad
InquisitiveLad

Reputation: 371

Alternative for Function in Where Clause

I have read that having a function in the WHERE clause slows things down (Not SARGABLE). I am now facing this situation with a query like this ...

In the query below, Fn is a built-in CRM Dynamics DateTime function to convert UTCTime to LocalTime

SELECT 
    Column1,
    Column2,
    Column3,
    Column4,
    Fn(UTCTimeColumn) AS LocalTimeColumn
FROM
    SomeTable
WHERE
    Fn(UTCTimeColumn) between '2 Jan 2015 00:00:00.000' and '8 Jan 2015 23:59:59.000'

Also tried this ...

SELECT 
    * 
FROM(
    SELECT 
        Column1,
        Column2,
        Column3,
        Column4,
        Fn(UTCTimeColumn) AS LocalTimeColumn
    FROM
    SomeTable) T    
WHERE
    T.LocalTimeColumn between '2 Jan 2015 00:00:00.000' and '8 Jan 2015 23:59:59.000'

Having a function like this in the WHERE clause makes things crawl.

Upvotes: 1

Views: 1537

Answers (1)

abatishchev
abatishchev

Reputation: 100278

You can add a persisted column which will call the function once:

add column LocalTimeColumn as Fn(UTCTimeColumn) persisted

Upvotes: 2

Related Questions