This is how I like to do paging with T-SQL. I've seen quite alot of ways to do paging with SQL Server, both with and without temporary tables and this is definetly my favorite as I find it both straight forward and easy to read.
A colleague just asked me if I knew a way to get the first day in a week in SQL Server. While I'm sure that there are many ways to get the first day (date) in a week here is a very simple way that in my opinion is quite elegant.
Almost all stored procedures that I write whose job it is to fetch data has to sort the data according to one or several columns. More often than not the selection of those columns, and the sort direction, is determined by the web application and therefore has to be based on parameters.