I think you're assuming that a simpler execution plan is faster. I just ran this on a load table which as 126K records. seq is a unique key.

select seq from [qhd]

where ',22,99,1031,2050,99999,84000,9900,12333,2,' like '%,'+ cast(seq as varchar(8))+ ',%'

select seq from [qhd]

where seq in (select val from dbo.fListToValues( "22,99,1031,2050,99999,84000,9900,12333,2"))

yes the execution plan for the second query is more complex, but if you look at it closely, the data is retreived using an index seek of [qhd]. the first plan uses an index scan so at least 99999 items are considered.

here are the timings:

1> set statistics time on

2> go

1> select seq from [qhd]

2> where ',22,99,1031,2050,99999,84000,9900,12333,2,' like '%,'+ cast(seq as var

char(8))+ ',%'

5> select seq from [qhd]

6> where seq in (select val from dbo.fListToValues( "22,99,1031,2050,99999,84000

,9900,12333,2"))

8> go

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

seq

-----------

2

22

99

1031

2050

9900

12333

84000

99999

(9 rows affected)

SQL Server Execution Times:

CPU time = 1422 ms, elapsed time = 1422 ms.

seq

-----------

2

22

99

1031

2050

9900

12333

84000

99999

(9 rows affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 2 ms.

1422ms for the like vs 2ms for the UDF. For small tables, the overhead of the function may cause option #2 to be slower but that difference will be negligible since both queries will run near instantaneously. However, once you progress to decent sized tables (10000 entries or more), option #2 is significantly faster. Also, the UDF can be re-used for any integer key and enhanced to support string keys. And UDF makes the SQL clearer and easier to understand. So, in my book option #2 is far superior.