I have used SQL CLR code in a specific case after a lot of testing of alternatives coded in VB6/ADO, VB.NET/ADO.NET, and TSQL. The objective in each case was to return a 1000 (or so) row recordset where the contents of each record were determined by a metadata table that specified the table, view, utf, or sproc and the column of the data. The metadata table is dynamic and can change frequently.

As each solution was developed it was optimized for performance within its own technology. All solutions returned the same recordset. The CLR coded solution was roughly 1000 times faster than the worst case (VB6/ADO) and 100 times faster than TSQL. Each solution was tested over 100 times on each of three databases and the results combined in order to determine the results.

I put a lot of work into the development, optimization, and testing of the solutions as it was very important to providing good performance to my customers. The CLR solution is used tens of thousands of times a day by hundreds of users.

Like any tool, SLQ CLR solutions can provide the best solution in the appropriate situation. They can be misused as can any tool.

Ross