I have the following query. For performance optimization reasons I have been asked to replace the logic with ISNULL function in SQL. How can I achieve this? please advice.
SELECT CASE
WHEN tb1.[tag-value] IS NOT NULL THEN tb1.[tag-value]
ELSE 'N/A'
END AS [tag-value]
FROM @AgentTableFilt ta
This should do what you need
SELECT ISNULL(ta.[tag-value], 'N/A') AS [tag-value]
FROM @AgentTableFilt ta
Or you could use the following
SELECT COALESCE(ta.[tag-value], 'N/A') AS [tag-value]
FROM @AgentTableFilt ta
I don't think that any of these suggestions are particularly improvements in performance compared to the CASE as that's already very basic.
Another possible option (although again, probably not worth the effort as it's just a short-hand way of writing your CASE) for SQL Server 2012 and above.
I'd be interested in seeing what the reaction was of the person who asked you to optimize the CASE if you implemented this as there should be absolutely no difference.
SELECT IIF(ta.[tag-value] IS NULL, 'N/A', ta.[tag-value]) AS [tag-value]
FROM @AgentTableFilt ta
I ran this through the test @Martin Smith linked to in his comment (and added my own for IIF) and it comes out roughly the same (if not slightly faster) as the other options. It's possibly slightly less readable though, so it depends on your familiarity with the logic.
Of course, you should test this on your own data, as what's optimal for your server may not be optimal for others.
DECLARE @i INT
SET @i = 1
DECLARE @CPU INT
SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME
SET @StartDate = GETDATE()
WHILE @i <= 1000000
BEGIN
IF COALESCE('abc', 'def') = 'def'
PRINT 1
SET @i = @i + 1
END
PRINT 'COALESCE, both non-null'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
DECLARE @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @i <= 1000000
BEGIN
IF ISNULL('abc', 'def') = 'def'
PRINT 1
SET @i = @i + 1
END
PRINT 'ISNULL, both non-null'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
DECLARE @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @i <= 1000000
BEGIN
IF IIF('def' IS NULL, 'abc', 'def' ) = 'abc'
PRINT 1
SET @i = @i + 1
END
PRINT 'IIF, both non-null'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))PRINT ''
GO
COALESCE, both non-null
Total CPU time: 22
Total milliseconds: 810
ISNULL, both non-null
Total CPU time: 37
Total milliseconds: 910
IIF, both non-null
Total CPU time: 21
Total milliseconds: 783
