Sharpen Your Skills: Routines and Clauses

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 [email protected]. 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:

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.