T-SQL: Use “Having” or “Where”

When writing T-SQL query in SQL Server, one has two options to limit the returned data. You can either use “HAVING” or “WHERE”. The question is when and how to use them?

For example:
Query1

Select tblEmployees.EmployeeID, SUM( tblEmployees.intHourThisWeek) as TotalHoursWorked
FROM tblEmployees
WHERE tblEmployees.Active=True
AND tblEmployees.DateofWork < '1/1/2008'
AND tblEmployees.DateofWork >= '1/1/2007'
GROUP BY tblEmployees.EmployeeID

and query2

Select tblEmployees.EmployeeID, SUM( tblEmployees.intHourThisWeek) as TotalHoursWorked
FROM tblEmployees
WHERE Active=True
GROUP BY tblEmployees.EmployeeID
HAVING tblEmployees.DateofWork < '1/1/2008'
AND tblEmployees.DateofWork >= '1/1/2007'

will return the same result. It summarize the total hours of each employee worked in year 2007.

But the difference is that the “WHERE” search condition is applied before the grouping operation occurs and the “HAVING” search condition is applied after the grouping operation occurs.

So if the search condition is limiting the search result into a small subset of the original table(s), it is better to apply the search condition before the grouping operation because this let the database engine to calculate the result set on a smaller subset of data.

Applying this principle to some of the queries while I am fine-tuning the database performance, it is easy to see a 60% to 75% CPU time improvement.

But you can't always move HAVING clause into WHERE clause. Because only HAVING can contain aggregate functions. HAVING clauses can reference any of the items that appear in the select list.

For example:
One company has a promotion that any salesperson who sold more than five refrigerators of one new model in the promotion date period, will qualify for a special bonus.

SELECT intSalespersonID, sum(intUnits) as totalSold
FROM tblSales
WHERE txtProduct = 'Refrigerator ABC'
--AND dateSold between @Date1 and @Date2
GROUP BY txtProduct, intSalespersonID
HAVING sum(intUnits) > 5

If you tried to move the HAVING clause to WHERE clause, you will see this error:
“An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.”

"semmap90.dll is installed" for SQL2000 DTS Mail Object

The environment at my work has both SQL server 2000 and 2005.

After I upgraded the Enterprise Manager of SQL2000 to Management Studio from SQL2005, I can no longer view or modify the MailObjects in SQL2000 DTS packages.
(DTS stands for Data transformation system)

The error is "Please make sure the semmap90.dll is installed."

I installed Enterprise Manager again and tried to open the DTS package MailObject, it gave me the same error message.

Based on a document from microsoft Technet, the solutiion is:
On the computer where you installed the desktop engine to run management studio, go to

..\Microsoft SQL Server\80\Tools\binn\

Then rename semmap.dll to semmap90.dll

Or you can make a copy of the dll and renamed the new one as semmap90.dll. In this case, you have two dlls so both Enterprise Manager and Management Studio can work.

No need to reboot or restart Management Studio, it immediately fixed the error.