SQL Server often provides several ways to
accomplish a task. To choose the best
technique for your situation, it helps to brush up
on the differences between various options. In this
series, I compare and contrast some similar SQL
Server features that I'm often asked about. This
month, I compare user-defined functions (UDFs)
with stored procedures and HAVING clauses with
WHERE clauses. Enjoy the article and please
send questions or topics you'd like me to cover to
pinal@SQLAuthority.com. I look forward to your
feedback!
UDFs vs. Stored Procedures
UDFs and stored procedures are both SQL Server
objects that store one or more T-SQL statements
in a single named, executable routine. Although
you can often implement the same or similar
functionality using either a UDF or a stored procedure,
the code will look significantly different
depending on which technique you choose. Here
are the main differences between UDFs and stored
procedures:
- A UDF must return a value-a single result set.
A stored procedure can return a value-or even
multiple result sets-but doesn't have to.
- You can use a UDF directly in a SELECT statement
as well as in ORDER BY, WHERE, and
FROM clauses, but you can't use a stored procedure
in a SELECT statement.
- A UDF can't use a nondeterministic function such
as GETDATE(), NEWID(), or RAND(), whereas
a stored procedure can use such functions. A
nondeterministic function is one that can return a
different result given the same input parameters.
- A UDF can't change server environment variables;
a stored procedure can.
- A UDF always stops execution of T-SQL code
when an error occurs, whereas a stored procedure continues to the next instruction if you've used
proper error handling code.
Both UDFs and stored procedures can perform well,
depending on how you write the code. To determine
whether a UDF or a stored procedure would yield
the best performance in a particular implementation,
you should do performance testing.
HAVING vs. WHERE
You typically use the T-SQL HAVING clause
along with the GROUP BY clause to search or sort
based on a certain condition. But when you don't
use GROUP BY, the HAVING clause acts like a
WHERE clause to filter the results that a query
should return.
You can use the WHERE clause in SELECT,
DELETE, and UPDATE statements, but you can
use HAVING only in a SELECT statement. However,
HAVING can contain an aggregate function,
such as COUNT(), whereas WHERE can't.
The following two queries illustrate the WHERE
and HAVING clauses:
USE AdventureWorks
GO
--Return records that have an
--OrderQty greater than 20.
SELECT SalesOrderID, OrderQty
FROM Sales.SalesOrderDetail
WHERE OrderQty > 20
GO
--Return records that have an
--OrderQty greater than 20 and a
--total SalesOrderID greater
--than 9.
SELECT COUNT(SalesOrderID)
TotalSalesOrderID, OrderQty
FROM Sales.SalesOrderDetail
WHERE OrderQty > 20
GROUP BY OrderQty
HAVING COUNT(SalesOrderID)
> 9
GO
The first query uses the WHERE clause to return
all records that have an OrderQty greater than 20.
The second query then uses the HAVING clause
with the COUNT() function to further filter those
results, returning only records that also have a total
SalesOrderID greater than 9.