SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using a Variable for an IN Predicate


Using a Variable for an IN Predicate

Author
Message
tfifield
tfifield
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4123 Visits: 2890
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
tfifield
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4123 Visits: 2890
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
tfifield
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4123 Visits: 2890
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
tfifield
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4123 Visits: 2890
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
tfifield
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4123 Visits: 2890
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
Brett Phipps
Brett Phipps
Mr or Mrs. 500
Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)

Group: General Forum Members
Points: 599 Visits: 230
Why bother with the temp table? Why not just imbed the select into your where clause?


SELECT
SO.SalesPersonID, C.FirstName + ' ' + C.LastName AS SalesPerson
, COUNT(*) AS Orders
, SUM(SO.SubTotal) AS TotalAmount
FROM Sales.SalesOrderHeader SO
INNER JOIN HumanResources.Employee E ON
SO.SalesPersonID = E.EmployeeID
INNER JOIN Person.Contact C ON
E.ContactID = C.ContactID
WHERE SO.OrderDate BETWEEN @StartDate And @EndDate
-- The dynamic IN predicate.
AND SO.SalesPersonID IN (SELECT CONVERT(INT, Item) AS SalesPersonID
FROM dbo.DelimitedSplit8K(@SalesPeople, ','))
GROUP BY
SO.SalesPersonID, C.FirstName, C.LastName


Brett Phipps
Brett Phipps
Mr or Mrs. 500
Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)

Group: General Forum Members
Points: 599 Visits: 230
Celko
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.


Don't you just love people who like to shoot stuff down without providing an alternative to the problem at hand.

If it's such a misuse what is the solution? Write reports that only let the users select one item at a time? My users would consider that a "non-solution".
tfifield
tfifield
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4123 Visits: 2890
bphipps-931675 (6/21/2011)
Celko
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.


Don't you just love people who like to shoot stuff down without providing an alternative to the problem at hand.

If it's such a misuse what is the solution? Write reports that only let the users select one item at a time? My users would consider that a "non-solution".

Thanks for backing me up on this.
Todd Fifield
tfifield
tfifield
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4123 Visits: 2890
bphipps-931675 (6/21/2011)
Why bother with the temp table? Why not just imbed the select into your where clause?


SELECT
SO.SalesPersonID, C.FirstName + ' ' + C.LastName AS SalesPerson
, COUNT(*) AS Orders
, SUM(SO.SubTotal) AS TotalAmount
FROM Sales.SalesOrderHeader SO
INNER JOIN HumanResources.Employee E ON
SO.SalesPersonID = E.EmployeeID
INNER JOIN Person.Contact C ON
E.ContactID = C.ContactID
WHERE SO.OrderDate BETWEEN @StartDate And @EndDate
-- The dynamic IN predicate.
AND SO.SalesPersonID IN (SELECT CONVERT(INT, Item) AS SalesPersonID
FROM dbo.DelimitedSplit8K(@SalesPeople, ','))
GROUP BY
SO.SalesPersonID, C.FirstName, C.LastName


It very well could have been done that way. The main reason I'm using a temp table, aside from it being easy to read and people are used to them, was that temp tables have statistics. I also wanted to demonstrate the the various ways it could be done easily and thought going to a temp table would be easier to read and understand in the various scenarios.

Kevin pointed out that the CTE version would probably not scale very well since CTE's don't have statistics. I'm not sure that a table valued function is really any better than a CTE on creating statistics. I simply don't know. Maybe some guru like Paul White could comment on this.

At the very least, I do know that temp tables have statistics and that a long array that would produce many records in the temp table would scale upwards properly. In fact if it got huge, it could be indexed if it came to that. I don't normally index temp tables unless I get more back in performance than I lose in the indexing phase, however.
Todd Fifield
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search