Comprehensive Resource for Using Arrays and Lists in SQL Server
My fellow MVP, Erland Sommarskog, is one of the few people I know who is frighteningly smart. He goes beyond mere SQL Server guru-hood; his knowledge of SQL Server dwarfs any mere human's. When he writes about a topic, he doesn't just create a blog entry; he writes a book length article and puts it on his Web site here.
One of the most valuable things he has done has been to compile all the information you could ever want or need to know about using array and list-type structures in SQL Server into a set of articles collectively called Arrays and Lists in SQL Server. This is a comprehensive--over the top comprehensive--description of using just about every possible technique of using data structures in Transact SQL, as well as testing how each technique performs so that you can decide the best way to do something.
I have referenced this work often, including when writing the AppDev SQL Server 2005 and 2008 courseware.
Erland just released an updated version of the articles, which brings them up to date through SQL Server 2008, including one of my favorite new features, table-valued parameters. There are now three articles and two appendices (the latter of which cover the performance data). One article is for SQL Server 2000 and before, another for SQL Server 2005, and another for SQL Server 2008. The titles are a little misleading, since you really need to read the latter two article to get the full, current information. The 2008 article only covers table-valued parameters; the other techniques are still valid as well.
Even if you've read the articles before, they are worth a re-read for SQL Server 2005 and the new one for 2008. And buy him a drink the next time you see him at a conference!