Running the query out of the functions to look at the query plans, sample data and other tests + possible rewrite can be found below
DECLARE @objectid uniqueidentifier
select top(1) @objectid = GUIDS
from #TEMP;
-- function 1 ,omitted the return 0
IF (SELECT TOP(1) 1 FROM StateChanges
WHERE
LinkedObjectId=@objectId AND
(ChangeType = 1 OR ChangeType = 2 ) )
IS NOT NULL
BEGIN
SELECT 1
END
-- Function 2 changed the returns
DECLARE @result BIT
SET @result = 0
SELECT TOP(1) @result=1 FROM StateChanges
WHERE LinkedObjectId=@objectId AND
(ChangeType = 1 OR ChangeType = 2 )
SELECT @result;
Here it gets more clear what extra operations are happening on one side
Function V1:
Nested loop left outer join to facilitate the IF check
While the second function , V2 has none of that
A nested loop left join was added because of the IF check, if we execute the query without the IF statement:
DECLARE @objectid uniqueidentifier
select top(1) @objectid = GUIDS
from #TEMP;
SELECT TOP(1) 1 FROM StateChanges
WHERE
LinkedObjectId=@objectId AND
(ChangeType = 1 OR ChangeType = 2 );
The same result as V2 is seen
Which should explain the increased worker time, since adding the nested loop operators to check for the IS NULL
will increase CPU usage.
To answer your question, yes the IF statement should increase your CPU usage, as a result of the added NESTED LOOPS
operator
Testing with the previous defined tables, indexes, functions and adding some data
CREATE TABLE #TEMP(GUIDS uniqueidentifier)
DECLARE @GUID uniqueidentifier
declare @i int =1
WHILE @i <= 1000
BEGIN
SET @GUID = NEWID()
INSERT INTO #temp(GUIDS)
VALUES(@GUID)
SET @I += 1
END
SET NOCOUNT OFF;
INSERT INTO [dbo].[StateChanges]([RecordId],[LinkedObjectId], [ChangeType],[ChangeTime], SomeColumnsHere )
SELECT GUIDS, GUIDS , 5, getdate(), 'Blabla'
from #TEMP
Testing the functions
SELECT [dbo].[ObjectHasInterestingChanges_v1](GUIDS)
FROM #temp;
SQL Server Execution Times: CPU time = 125 ms, elapsed time = 125
ms.
SELECT [dbo].ufn_ObjectHasInterestingChanges_v2(GUIDS)
FROM #temp ;
SQL Server Execution Times: CPU time = 125 ms, elapsed time = 132
ms.
they appear to get the same CPU time with a low dataset and no matches (nothing matches because Changetype is not 1 or 2 in my data).
On another note, you could change the function to outer apply
SELECT ISNULL(a.bla,0)
FROM #TEMP
OUTER APPLY
(SELECT TOP(1) 1 as bla FROM StateChanges
WHERE
LinkedObjectId=GUIDS AND
(ChangeType = 1 OR ChangeType = 2 ) ) as a
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms.
Test 2, adding some mathing data
DECLARE @GUID uniqueidentifier
declare @i int =1
WHILE @i <= 1000
BEGIN
SET @GUID = NEWID()
INSERT INTO #temp(GUIDS)
VALUES(@GUID)
SET @I += 1
END
SET NOCOUNT OFF;
INSERT INTO [dbo].[StateChanges]([RecordId],[LinkedObjectId], [ChangeType],[ChangeTime], SomeColumnsHere )
SELECT GUIDS, GUIDS ,2, getdate(), 'Blabla'
from #TEMP;
Testing the functions and the rewrite again
SELECT ISNULL(a.bla,0)
FROM #TEMP
OUTER APPLY
(SELECT TOP(1) 1 as bla FROM StateChanges
WHERE
LinkedObjectId=GUIDS AND
(ChangeType = 1 OR ChangeType = 2 ) ) as a;
SQL Server Execution Times: CPU time = 31 ms, elapsed time = 21
ms.
SELECT [dbo].[ObjectHasInterestingChanges_v1](GUIDS)
FROM #temp;
SQL Server Execution Times: CPU time = 375 ms, elapsed time = 378
ms.
SELECT [dbo].ufn_ObjectHasInterestingChanges_v2(GUIDS)
FROM #temp ;
SQL Server Execution Times: CPU time = 344 ms, elapsed time = 352
ms.
A difference between function 1 and 2 appears as the dataset + matches increases!