There are two features that I have always yearned for in SQL Server: quick and easy paging, and string concatenation to be used as an aggregate function. For the life of me I could never figure out why these features never made it into SQL Server versions. I’ve been using SQL Server since version 7 and with each new version I was hoping it would be available in the subsequent release.

To this date there still is not function to aggregate a string when using a group by clause and no “easy” way to page. There are workarounds available but none that are as straight forward as MySQL’s LIMIT clause. As for string concatenation with SQL Server 2005, we can create custom aggregates so we can finally roll our own. I created a quick class (StringConcat.cs) you can embed in a DLL and load it into SQL Server’s CLR. To use the aggregate in SQL Server, you’ll need to load it and assign it first. Here is a good article quickly explaining how to create and deploy aggregates.

If you enjoyed this post, make sure you subscribe to our web feed!

Categories: Development ~ ~ Trackback