UDFs vs. stored procedures and HAVING vs. WHERE
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.
