Top T-SQL Commands

Here are some SQL commands that can be a real life-saver that are beyond your basic CRUD statements. Also take note, for .NET developers LINQ should be used whenever possible, but when you've got something complicated, I always seem to turn to one of the commands below. Most of these features were introduced in SQL Server 2005 and 2008.

1.) Pivot and Unpivot - Flip your data around on it's head.
2.) Row_Number - sequential numbers and the ability to restart numbering under any circumstance.
3.) Aggregate Functions (CLR) - If a particular aggregate function hasn't been written yet, you can make one yourself. For example, the aggregate function, "median" or "1st Quartile" is not a built-in function to SQL Server.
4.) Table Value Functions - Incredibly useful in cases where you need to pass in an arguement, but still want the power of a view and stored procedure.
5.) Dynamic SQL - Painful, but extremely useful to build the most optimized and reusable logic.
6.) CLR - Used wisely (ie. NOT for everyday SQL), but if you wanted to do something outside the box. For example, Geo-Code your data or #3's Aggregate Function lets you really customize the database.
7.) For XML Path - Easy way to transform a set of rows into text or xml. Real world example: Use to send custom logs via msdb.dbo.send_dbmail.
8.) Database Triggers - Since Check-in/Check-out is nearly impossible in a database, a database trigger added to a database can track changes and who made the changes.