SQL: Back to basics

Today's lesson might be common knowledge for you, but for me it was a good back to basics again. It concerns the calling of scalar functions from the WHERE clause of a SELECT statement.

One could expect the following statement to execute the function only once, but, then, you'd be wrong.

select * from sys.objects
where dbo.myfunc(1) > 1

SQL server executes the function once for every row in sys.objects, even though it is called with a constant parameter and will never return a different result. Typically this is something you would not pick up in development with a small dataset, but performance will decrease dramatically as the dataset size increases.

So, the right way to write the above SQL statement would be something like

declare @num int
set @num=dbo.myfunc(1)
select * from sys.objects
where @num > 1

Be aware, be very aware!