Using arrays in SQL?

This is an excellent method of using arrays of integers in SQL queries. The basic idea is to convert your integer array to a byte array (the semantic equivalent of varbinary(MAX)), then to write a CLR table-valued function to convert the varbinary(MAX) to a table of integer values.

That's just cool.

I picked this up in a comment to Ayende Rahien's post about performance issues with his technique for working around the 2100 item limit imposed by SQL Server for an IN clause in T-SQL. This could show up in a number of situations where a linked server query wouldn't work. Say, for example, you were calling an API for the most recent searches on XYZ.com and wanted to match those keywords against recent searches on your site. You'd have, basically, an array of search terms that you wanted to find in your database. There are a number of solutions to this, but from what I've seen the best performance dynamics (assuming a somewhat large dataset) are given by creating some sort of temporary table and running your query against that.

The method of using integer arrays above is the most imaginative I've seen. Though, other alternatives exist, such as BULK INSERTing data, running your query, and then rolling back the transaction, as suggested in another of the comments.