30 March 2007

Arrays in Sql 2005

Have you ever wanted to do something like:

SET @InventoryArray = "1,2,3";
SELECT * FROM Invetory WHERE InventoryID in (@InventoryArray);

or what about:

SET @BarList = "1, 2, 3";
INSERT INTO Foo (Bar) SELECT * FROM @BarList;

I know I have. It always seems to come up in power searches and migration tools. Unforunately, Sql Server 2005 doesn't have anything close to arrays. Oracle does, but not SQLServer.

Erland Sommarskog, SQL Server MVP has written a great paper titled Arrays and Lists in Sql Server 2005.

This paper explores the various techiniques that can be used to coerce sql server into using arrays and the effective performance of each strategy. It is a lengthy article, so if you just want to skip to the results check out the Data from Performance Tests Appendix