There are many more possible implementations that are not mentioned in the article. The most complete discussion I found in famous Erland Sommarskog article "Arrays and Lists in SQL Server". However the last implementation from my article is not mentioned there and I found it the most efficient in case of lists of values from the small enough domain.

Madhivanan, your implementation is interesting. I guess your intention was to use where clause against orderStatuses table and then to join it with orders table. If so, that is clever idea. I don't think I'll have time to test it today and compare with solutions from the article. However, testing script is attached, you can download it and modify to include your solution and test it yourself.

Hi Frank, I know about that article. The last implementation from my article was not there last time I checked. If Madhivanan's implemention is there I managed to miss it somehow. I believe it is very comparable with my solution, possibly faster, but without overhead of maintaining additional list members table.

As I said, I just discovered your article today and the first thing that came to my mind was Erland's article. I can't say right now, that one article is "better" than the other, but it's always good to have several different people writing about one topic, because nobody's perfect. And different people tackle problems in different ways, so that's usually an excellent source to sucking knowledge from someone else. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />And thanks for providing the test script.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Erland's article is great, no doubt about that. However, when I tested solution from there, I've found udf based solution not efficient enough for my needs. I also wanted to avoid dynamic sql because we didn't want to give read rights to application login. I did that research preparing solution for former employer. I applied the last implementation mentioned in the article.<br /><br />About test script, that was your suggestion from "Running totals" article discussion and I found it to be a very good idea. [<img src='/community/emoticons/emotion-1.gif' alt='' />]

Madhivanan, I couldn't resist, so I already tested your solution. As I expected the performance was similar to the last implementation from the article, even better, for most test cases it was slightly faster. Difference was small, but having in mind your solution doesn't require hassle with list members table, from now on I will recommend your solution as the best for the problem when relatively small lookup table is available. Here is the code I applied for testing:

I've just finished testing with fixed sp version. Now, its speed is comparable with one of dynamic sql solution. However, I haven't checked if optimal execution plan is used. I plan to force it using the code bellow and test it again:

Forcing execution plan didn't affect teting results significantly. So the conclusion is that implementation proposed by Madhivanan offers similar performance to dynamic sql, but it has advantage that no read rights are necessary to run it. It is slower then permanent list members table when lookup table is small. However, if lookup table contains 100s of rows I guess this solution might be better. I was not aware of this implementation when I wrote the article. If I was I would certainly include it there.