I created a scalar valued function that pauses for as many seconds as passed in. This function won't be used in production. I am trying to test how SQL server evaluates and creates plans if I add a UDF to a query. I am very puzzled by the difference in how it evaluates the last of my 3 queries. First here is the code to create the test function:
CREATE FUNCTION dbo.[sleep](@seconds int)
RETURNS datetime
as
BEGIN
DECLARE @sleepUntil datetime
DECLARE @dummy int
SET @sleepUntil = DATEADD(s, @seconds, getdate())
WHILE getdate() < @sleepUntil
SET @dummy = 0
RETURN getdate()
END
Now to create a table to test queries
CREATE TABLE #table (num int)
and to insert values
INSERT INTO #table
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)
The first query takes 2 seconds to run - which makes sense assuming that SQL server stores the value returned from the scalar function.
SELECT num
FROM #table t
WHERE dbo.sleep(2)=0
The second one still takes 2 seconds - so my assumption is that it does the same.
SELECT num
FROM #table t
WHERE EXISTS (select TOP 1 1
FROM #table t1 )
OR dbo.sleep(2)=0
The third one is puzzling - it takes 20 seconds to run, why would sql server not store the value here instead it runs it for each row? An interesting point I noticed was that when I clicked to see live query statistics it took 20 seconds to start showing the plan.
SELECT num
FROM #table t
WHERE EXISTS (select TOP 1 1
FROM #table t1
WHERE t.num = t1.num) dbo.sleep(2)=0