Using a Variable for an IN Predicate

Question

Post reply

Using a Variable for an IN Predicate

tfifield

SSCrazy Eights

Points: 9655
More actions
June 20, 2011 at 10:09 pm

#252218

Comments posted to this topic are about the item Using a Variable for an IN Predicate

Viewing 15 posts - 1 through 15 (of 47 total)

You must be logged in to reply to this topic. Login to reply

irozenberg SSC-Addicted Points: 478 More actions · Answer 1

Todd, thank you for article.

Did you try UDF that return a table?

Cheers

Sarus1984 SSC Veteran Points: 203 More actions · Answer 2

too keep it simple whith less code (but only for short in-lists)

Declare @inStr varchar(100)

Set @inStr = '1,2,4,6'

select * from testtable

where ','+@inStr+',' like '%,'+cast(column as varchar)+',%'

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

Nice article contains good examples, description.

Thanks

david.c.usher SSC Journeyman Points: 89 More actions · Answer 4

But if you are having to use a table, would you not just do a join now instead of doing an IN ?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

You should absolutely positively NOT use the CTE for this scenario unless you are guaranteed to have VERY few rows split out AND you do not have any signficant data skew. If you have either or both of those you will get screwed with a bad plan at least some of the time. Either a nested loop index seek plan with a kajillion rows or a scan/hash plan with a few rows. The optimizer can't have any idea how many rows are coming out of the split. Nor can the optimizer have statistics on the VALUES of the row(s) coming out of the split.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

dave.clark SSC Veteran Points: 238 More actions · Answer 6

Thanks Todd - great article. My company uses a similar function for splitting, but I simply have not had the need/opportunity to use it.

I have an upcoming task in which I have to modify a few stored procedures to accept a list for several of their arguments rather than a single value so this will work great. Very timely article for me! 😀

Your Name Here SSCrazy Points: 2344 More actions · Answer 7

Hey Todd

Re: splitting a string to a table variable "array" - give this a try:

DECLARE @values TABLE (value VARCHAR(10))

DECLARE @xml AS XML,@str AS VARCHAR(100),@delimiter AS VARCHAR(10)

SET @STR='A,B,C,D,E'

SET @delimiter =','

SET @xml = CAST(('<X>'+REPLACE(@str,@delimiter ,'</X><X>')+'</X>') AS XML)

INSERT INTO @values (value)

SELECT N.value('.', 'varchar(10)') AS value FROM @xml.nodes('X') as T(N)

SELECT * FROM @values

Cheers,

Ken

PS I can't claim to have come up with this - I found it somewhere on the net.

mtassin SSC-Insane Points: 23099 More actions · Answer 8

And of course those of us with SQL 2008 can pass tables as stored procedure parametes now 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Codernater SSC Veteran Points: 215 More actions · Answer 9

Good article.

I still think though using User-Defined Table Types is easier and faster

tfifield SSCrazy Eights Points: 9655 More actions · Answer 10

irozenberg (6/20/2011)
Todd, thank you for article.
Did you try UDF that return a table?
Cheers

The Delimited8KSplit is a UDF that returns a table. The last example bypasses the creation of a temp table and uses the UDF in a CTE. I could have modified the UDF to return an INT and used it directly, but I thought it would be better just to use Jeff's UDF the way it was, convert to INT and use that.

Todd Fifield

tfifield SSCrazy Eights Points: 9655 More actions · Answer 11

Sarus-127369 (6/21/2011)
too keep it simple whith less code (but only for short in-lists)
Declare @inStr varchar(100)
Set @inStr = '1,2,4,6'
select * from testtable
where ','+@inStr+',' like '%,'+cast(column as varchar)+',%'

Saurus,

I've used that type of LIKE before. Sometimes it works just fine, but it doesn't scale very well and the examples I used all had indexes on the column. The LIKE operator with '%' on the left won't use indexes.

Todd Fifield

tfifield SSCrazy Eights Points: 9655 More actions · Answer 12

TheSQLGuru (6/21/2011)
You should absolutely positively NOT use the CTE for this scenario unless you are guaranteed to have VERY few rows split out AND you do not have any signficant data skew. If you have either or both of those you will get screwed with a bad plan at least some of the time. Either a nested loop index seek plan with a kajillion rows or a scan/hash plan with a few rows. The optimizer can't have any idea how many rows are coming out of the split. Nor can the optimizer have statistics on the VALUES of the row(s) coming out of the split.

Kevin,

I thought about that before I tested the CTE version. For the small number of table elements it worked just fine. As I stated in the article, testing should be done to check performance. I realize that CTE's don't have statistics.

In a production environment where it's likely that there could be many elements I probably wouldn't even bother to bench mark the CTE version. I just brought it up as a possibility.

Thanks for your comments. I appreciate your input.

Todd Fifield

tfifield SSCrazy Eights Points: 9655 More actions · Answer 13

Your Name Here (6/21/2011)
Hey Todd
Re: splitting a string to a table variable "array" - give this a try:
DECLARE @values TABLE (value VARCHAR(10))
DECLARE @xml AS XML,@str AS VARCHAR(100),@delimiter AS VARCHAR(10)
SET @STR='A,B,C,D,E'
SET @delimiter =','
SET @xml = CAST(('<X>'+REPLACE(@str,@delimiter ,'</X><X>')+'</X>') AS XML)
INSERT INTO @values (value)
SELECT N.value('.', 'varchar(10)') AS value FROM @xml.nodes('X') as T(N)
SELECT * FROM @values
Cheers,
Ken
PS I can't claim to have come up with this - I found it somewhere on the net.

Ken,

Thanks for your input. I'll give it a try.

Have you ever bench marked it against the technique I'm using?

Todd Fifield

tfifield SSCrazy Eights Points: 9655 More actions · Answer 14

CELKO (6/21/2011)
Some version of this mis-use of SQL gets invented again every few months. It is a slow, dangerous mess. Are you familar with the long parameters list idiom? SQL is a data base language, not a tool for writing parsers.

Joe,

I'm very familiar with the fact that SQL is not a language for writing parsers. I worked on an early word processor in PDP-11 assembly language and I've written 2 text editors in C. I've also had to do parsing in COBOL. Believe me, C is the language of choice for parsing.

However, a delimited array of parameters is a very common SQL problem. The idea is to make it easy to program and still be robust both in the front end and back end. I've seen this requirement many, many times and this technique is rock solid.

This technique, in any case, is NOT a parser. All of the parsing is done at the beginning, which converts the string array into a TABLE, which is what SQL Server handles best. That's the whole point of the article - let SQL Server do what it does best. Join to tables and such.

Todd Fifield