is there any benefit to using a created FUNCTION over declaring a variable?

Question

I use the below to return last weeks fiscal week number

SELECT (
    SELECT
        CA.FISCALWEEK
    FROM 
        EUUKSQL01.DASHBOARD.DBO.CALENDAR CA
    WHERE 
        CONVERT(DATE,GETDATE()) = CONVERT(DATE,CA.FULLDATE)   ) - 1

If i use it a lot within a query I DECLARE & SET it as @LW etc.

I also created it as FUNCTION,

Is there any benefit to using one method over the other ?

Answer 1

The UDF (user defined function) can have a negative impact on your query-speed. If you haven't set your MAXDOP (max degree of parallelism) to 1 the query with your UDF will prevent parallelism.

You can see it in your explain-plan and try what solution is the faster one.

On the other hand the re-use of the code and the easier debugging is nice in the UDF.

If you want to have both, you could write your own CLR wich contains the logic of the UDF.

The Test I made:

CREATE FUNCTION [dbo].[GetLW]()
RETURNS datetime 
AS 
BEGIN
RETURN CONVERT(DATE,GETDATE())
END
GO

SELECT * 
FROM [TGR].[dbo].[LargeTable]
WHERE BookingDate = [dbo].[GetLW]()
GO

DECLARE @LW datetime = CONVERT(DATE,GETDATE())

SELECT * 
FROM [TGR].[dbo].[LargeTable]
WHERE BookingDate = @LW
GO

Explain Plan:

So if you are in a DWH/BI enviroment, maybe the variable is the better solution.

Regards Tgr