set statistics time on
create FUNCTION [dbo].[Test](@parm int)
RETURNS int AS
BEGIN
declare @var int
set @var = 2
return @parm * 2
END
select dbo.Test(a.column_id)
from
sys.all_columns a
join sys.all_columns b on a.column_id = b.column_id
select a.column_id * 2
from
sys.all_columns a
join sys.all_columns b on a.column_id = b.column_id
SQL Server parse and compile time:
CPU time = 32 ms, elapsed time = 32 ms.
(4311158 rows affected)
SQL Server Execution Times:
CPU time = 7781 ms, elapsed time = 9804 ms.
(4311158 rows affected)
SQL Server Execution Times:
CPU time = 625 ms, elapsed time = 9262 ms.
Scalar functions do have some inherent overhead. Therefore, be sure to make them as efficient as possible (no need to declare a local variable). Whenever possible, use an in-line-table-value function instead of scalar, because they perform vastly better.
CREATE FUNCTION [dbo].[Test]
(
@parm int
)
RETURNS int AS
BEGIN
RETURN (
SELECT @parm * 2
)
END
Now THAT I agree with. However, it doesn't mean that the code has to be slow and it doesn't mean that I can't use high performance techniques just because someone else doesn't know them. I DO comment the code correctly when I believe that I've used something that the common developer may not grasp.
True but irrelevant here. Elapsed run time matters. If I have a query that runs for 2 hours (I do. Very large tables.) and I can make it run in 10 min by eliminating a function call at the expense of some non-DRY code, I'll do it.
I agree with @gbritton above... CPU cycles aren't actually cheap. They appear to be so when you're working with a single little thing, like your function example, but you have to remember that there will be thousands of such things operating billions of times possibly every hour.
Any way, getting back to your original post, what is the overall purpose of your post. Are you trying to prove that there's not much difference between using a well encapsulated function, which supports both readability of the code that uses it and provides standardization of methods so that Developers don't need to recreate the proverbial wheel, possibly doing it incorrectly? If so, then I completely agree but your code in your original post is actually testing for such a thing incorrectly. Please see the following article for a discussion and code that tries to drive the point that I think you're trying to make home. It'll also explain my comment on why you shouldn't use functions that contain the word "BEGIN".
Heh... first, the problem in the article is actually identical to the original problem that you posed so I'm not sure where you're coming from now. And what exception are you talking about? I've found that the principles express both in what we're saying and what are in the article I cited solve a lot of the supposed problems that people regularly cite about using functions or not. There doesn't appear to be an "exception case" here, so you need to explain what you mean by "You are stating the exception case as the rule".
Most servers aren't running at 50+% CPU for 8 hours per day.
Readable code is more important. IF something is a performance problem, you can cut and paste the code and add a comment. EDIT: you'd probably rewrite the query, but you understand. Readable first.
YOUR brain cycles are more important. The human brain stores a finite # of short-term memory items. Push a few out to functions and you'll perform better.
How do you know that? Our CPUs are frequently above 50% 24 hours a day. if they were not, our management would rightly ask why we don't go for cheaper servers. Anyway, the problems I see with functions concern elapsed time and sub-optimal query plans. Jeff's comment about ISFs is an obvious way around that: preserve encapsulation without a penalty!
Like most things, "it depends". Though I certainly agree with avoiding premature optimization and generously commenting code when a particular, perhaps arcane, optimization is made.