CODEWITHSUNDEEP
sql-serversql-server-2005
Oliver
Oliver

Reputation: 11617

When is it ok to call functions in SELECTs?

I have some stored procedures that have a WHERE clause like this:

WHERE
    dbo.Users.DatePaidUpTo > GETDATE()

Is this ok? Would it be more efficient to this:

DECLARE @currentDate DateTime
SET @currentDate = GETDATE()

    ....

WHERE
    dbo.Users.DatePaidUpTo > @currentDate

If that is more efficient, how can I do something similar for views? As this syntax:

ALTER VIEW [dbo].[vw_Recommendations]
AS

DECLARE @currentDate DateTime
SET @currentDate = GETDATE()

SELECT
    ....

Is invalid.

Upvotes: 1

Views: 57

Answers (2)

Russell
Russell

Reputation: 17739

If you can call a function outside of a query, it will minimise the risk that the function will prevent any pre-compilation that may occur, such as in views, because the function will be evaluated for every record.

IF the function is complex or time consuming, running it once beforehand may improve performance.

In your example, you would have the view without the function call, then evaluate the function in the SQL using the view, such as a stored procedure.

Upvotes: 2

Matt Seymour
Matt Seymour

Reputation: 9415

You can call them where you like, if it is a field which will always return the same value such as GETDATE() then put it in a variable and limit the number of database calls.

Obviously if the result from the function was variable depending on the input into the function then keep this inside the select / where.

At the end of the day it will come down to execution time, if you are iterating over a set of 100 records then the performance difference between either method will be not worth worrying out. If it was over 100 million records then optimization would be a consideration.

Upvotes: 1

Related Questions