I'm curious to know what this site uses for the Members List page:
http://www.sqlservercentral.com/Forums/Members.aspx
Currently, it says, "758,612 members, Page 1 of 37,931". Also, at the bottom of the page, you have several search options that you can configure.
So the question is, does SQLServerCentral.com use something similar to Adam's approach with CTE's? Or does it use temp tables or table variables? Or does it use dynamic SQL?