The Wayward Programmer

You can use the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values on multiple rows. For instance: SELECT Name   , MIN ( Rate ) OVER ( PARTITION BY edh . DepartmentID ) AS MinSalary   , MAX ( Rate ) OVER ( PARTITION BY edh . DepartmentID ) AS MaxSalary   , AVG ( Rate ) OVER ( PARTITION BY edh . DepartmentID ) AS AvgSalary   , COUNT ( edh . BusinessEntityID ) OVER ( PARTITION BY edh . DepartmentID ) AS EmployeesPerDept FROM EmployeePayHistory AS eph       JOIN EmployeeDepartmentHistory AS edh             ON eph . BusinessEntityID = edh . BusinessEntityID       JOIN Department AS d             ON d . DepartmentID = edh . DepartmentID http://msdn.microsoft.com/en-us/library/ms187751.aspx

What is TRY… CATCH? The TRY... Catch construct was designed to improve the functionality in processing errors. With it comes a cleaner and more readable syntax that is familiar to programmers. In addition, this construct can return the transaction state of your procedures, and allow the developer to either log the details of the error, or return this information to the calling procedure. The new functions available with TRY... CATCH include XACT_STATE, ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE. @@ERROR vs. TRY... CATCH Error handling in SQL Server 2000 and earlier consisted of the @@ERROR function. SQL Server 2005 introduced the TRY... Catch construct. These two error handling styles have varying ways of dealing with the errors produced. • @@ERROR is cleared and reset on each statement executed. This requires developers to test the error, or save it to a variable, after each TSQL statement. This greatly increases the lines of co...

The APPLY operator allows you to return values from an outer table much like JOIN, however I find it's easier to read, and allows joining two tables that I would've had to do some fancy foot work on to join. And since I'm always looking for the simpliest solution, this one fit the bill on my lastest project. There are two forms of APPLY: CROSS APPLY and OUTER APPLY.  CROSS APPLY returns only rows from the outer table that produce a result. It works similiar to INNER JOIN in that it won't return values that are null. The OUTER APPLY returns either values, or nulls if no values exist. The value of using the APPLY operator is that it can yield better performance. Check out http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/  to read more.     Information on how the Apply operator works   Here's a link to Technet's article on the APPLY operator: https://technet.microsoft.com/en-us/library/ms175156(SQL.90).aspx   Another artic...