Less Than Dot - Blog - The Ten Most Asked SQL Server Questions And Their Answers
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1

Getting all rows from one table and only the latest from the child table

1) Selecting all the values from a table for a particular date 2) Search all columns in all the tables in a database for a specific value 3) Splitting string values 4) Select all rows from one table that don't exist in another table 5) Getting all rows from one table and only the latest from the child table 6) Getting all characters until a specific character 7) Return all rows with NULL values in a column 8) Row values to column (PIVOT) 9) Pad or remove leading zeroes from numbers 10) Concatenate Values From Multiple Rows Into One Column

The paging and ranking functions introduced in 2005 are old news by now, but the typical ROW_NUMBER OVER() implementation only solves part of the problem. Nearly every application that uses paging gives some indication of how many pages (or total records) are in the total result set. The challenge is to query the total number of rows, and return only the desired records with a minimum of overhead? The holy grail solution would allow you to return one page of the results and the total number of rows with no additional I/O overhead. In this article, we're going to explore four approaches to this problem and discuss their relative strengths and weaknesses. For the purposes of comparison, we'll be using I/O as a relative benchmark.