InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 21:09:08 GMT20http://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Bruce W Cassidy (3/2/2013)[/b][hr]All I can say to that is that it's a good thing that SQL isn't intended to be a parser, and thus we don't have to worry about handling CSV or XML data in the database... :-P[/quote]Bruce, I wish you would be right BUT it's like a joke about THEORY and PRACTICE: they should be the same (IN Theory), but in practice they are different. Please note that original meaning of CSV was CHARACTER-SEPARATED VALUES (more generic case than COMMA-SEPARATED values). Now, back to PRACTICE - the most craziest thing in legacy system was "a feature" when multiple characters had been used in a single CSV (for instance both , and ; had been used in a single string). Anybody remembers definition of shortcut? Yes, the best one - the longest path between 2 points!In REAL life we have to deal with uncertainties, one of them - is LACK of data integrity in legacy systems. Always expect unexpected, but be grateful to previous developers - they ultimately created a JOB for you!Sun, 03 Mar 2013 06:17:16 GMTIrozenberg 1347http://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxAll I can say to that is that it's a good thing that SQL isn't intended to be a parser, and thus we don't have to worry about handling CSV or XML data in the database... :-PSat, 02 Mar 2013 13:22:49 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Irozenberg 1347 (3/1/2013)[/b][hr]Thank you Jeff!IMHO CSV data in database is a pure hack - some developers just cutting corners to speed up first cut and leave before it causing HUGE maintenance problem, especially with reporting. Last year have to deal with data migration project when salesforce.com legacy system had been implemented by storing comma separate values inside of table cells. I used CSV split function based on XML in order to tranform a column that contained comma-separated strings into a new table with ID and atomic items. Pure job creation scheme![/quote]You're welcome.Just to expose my opinion a bit...There are two types of CSV...Comma Separated Values - This is the "true" CSV and will always have quotes around aything that is character based. It's extremely useful but has the same problem as XML... if a quote is embedded in the actual data, it needs to be "escaped" in some fashion. Most people and programs don't format this type of file correctly including EXCEL.Comedy Separated Values - This is what most people think of when you say CSV. The only rules are that commas separate values and commas cannot be embedded in the values. It's a real PITA unless those rules are followed explicitly. Otherwise, it's pretty easy to make and use.Both types of CSV are easy for humans to understand and read which is why they both gained favor in the past.If it were me, I'd go back to the old ways and use things like ASCII characters 28 through 31 as they were meant to be along with the CrLf characters to help make it a bit easier to troubleshoot.Shifting gears, I think XML is probably the worst hack of them all. I just can't imagine why anyone would have written such a dirty, bloated, PITA to use data transmission format even if we didn't have to move the data to databases but especially since databases and spreadsheets DO make the world go'round. Consider the following.1. The tags cause extreme bloat. If communications were slower than what they are, no one in their right might would transmit so much useless repetative data as that contained in the tags.2. The tags don't actually do what they were advertised to do in the very beginning. There are things like OPENXML in SQL Server that will correctly shred even the most complex (even if not perfectly "well formed") of XML hierarchical data into an "edge" table but nothing to tell the human what the structure is especially when it comes to nested levels (think indented list of tags). The human has to figure that out and then write code to shred it correctly.3. The data format doesn't follow just one standard. It follows at least 2. One for entity data and one for element data both of which are just row data at different levels.4. XML data sucks for relational databases because multiple entity data (tables) are nested in a hierarchy. Ask most relational database programmers what their least favorite subject is and chances are good that they'll say "hierarchies".5. Unless you build a separate style sheet, XML doesn't do much to tell you what the datatype of each "cell" (whether it be entity or element data) is. That's another short coming, IMHO.6. All of that was done for the sake of supposed human readability and, I've got to tell you, if you have XML with more than about 3 levels (entities), a human is going to have some good difficulty with shredding it in the mind well enough to actually write queries against it.7. The data violates every rule of normalization there is if you store it in a database in an unshredded format. Holy shades of old IMS systems!One of my favorite ways to transmit data in the old days included a combination of the ASCII characters 28-31 with CrLf just for readability and the old DBase III table format. Each file had a header that named the columns and identified the datatype much like a CREATE TABLE statement. It was database friendly because each file formed an entity (table). It was incredibly compact because there were no tags and the separators weren't something that most humans could type via a keyboard which meant you didn't have to worry about things like embedded quotes or separators.The only good thing about XML, IMHO, is that it is so bloated and so resource intensive to shred that people HAD to make communications and CPUs faster just to handle XML and it's equally bloated father, HTML. Who in their right mind would use a 4 character starting tag and a 5 character ending tag just to identify a "cell" in a table for a single digit? Bleah!!! :sick: What the hell were they thinking?Sat, 02 Mar 2013 10:20:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Sean Lange (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]Sean Lange (3/1/2013)[/b][hr][quote][b]Irozenberg 1347 (3/1/2013)[/b][hr]Elementary sub-query or EXISTS clause would Radically improve performance.If you like fancy coding you could pass XML as parameter and use XQUERY. [/quote]You should read the article about the splitter. It will blow the doors off an XML parser. You can find the article by following the link in my signature about splitting strings. The article is great but the ensuing discussion (although incredibly lengthy) is even better.[/quote] Be careful now. He's not talking about passing a CSV and using an XML parser to split it. He's talking about passing actual XML and parsing it, which is actually very fast.[/quote]Ahh I guess I misread that. :blush:It is fast on the sql side of things but I have never really been convinced that applications gain much benefit because of all the extra data that has to be passed.[/quote]Thank you Jeff!IMHO CSV data in database is a pure hack - some developers just cutting corners to speed up first cut and leave before it causing HUGE maintenance problem, especially with reporting. Last year have to deal with data migration project when salesforce.com legacy system had been implemented by storing comma separate values inside of table cells. I used CSV split function based on XML in order to tranform a column that contained comma-separated strings into a new table with ID and atomic items. Pure job creation scheme!Fri, 01 Mar 2013 17:04:02 GMTIrozenberg 1347http://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]rd8202 (3/1/2013)[/b][hr]Instead of the CTE, can't you just put the CTE's SELECT statement inside the WHERE clause?[/quote]You can do it that way; it's really just a syntactical change. There's a slight difference in the query plan (as the article shows -- it does cover both approaches), but SQL Server ends up doing much the same work. You're exchanging a filter (the IN clause) for a join (the CTE), and both are pretty optimal operations.Where there's a difference is where you decide to use a temporary table. I'll do that for two reasons: either I'm expecting more than an handful of values or I'm reusing the list of values in subsequent queries. For the first, I can create a temporary table with a primary key so SQL Server can do an index merge for the join, and that seems to work pretty well.Fri, 01 Mar 2013 13:52:34 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]jimmyzimms (3/1/2013)[/b][hr]I'd actually suggest you in general try to keep to the xml array as the "native" input parameter and have an SSRS wrapper function that deals with the delimited string coercion. This is a fairly common implementation of an anti-corruption layer at work here.[/quote]The delimited strings are assembled directly by SSRS; I don't see the value in an "anti-corruption layer" sorry. If SSRS is not building parameters correctly, that means that a significant part of the .Net layer must be corrupt, and no XML parsing is going to fix that (especially since the thing you're most likely to do to build the XML is use the .Net functionality to turn the array of parameters into XML).Instead I plug the stored procedure directly into SSRS as a data source, and handle any parameters within the stored procedure (which I would also have to do if it were XML). Whereas if I were passing parameters as XML, I would be doing adding additional work, reducing the abstraction (as you have to go through preparation steps prior to calling a stored procedure, which means you have to know about parameter formats, which is a step down in abstraction), and gaining... what, exactly?Fri, 01 Mar 2013 13:47:10 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxInstead of the CTE, can't you just put the CTE's SELECT statement inside the WHERE clause?Fri, 01 Mar 2013 13:16:16 GMTrd8202http://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxI agree with the UDF approach. We use 2 UDFs, one for splitting character data, one for splitting int. Comes in very handy and only done once.Fri, 01 Mar 2013 11:48:58 GMTbmayhewhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Bruce W Cassidy (3/1/2013)[/b][hr][quote][b]jimmyzimms (3/1/2013)[/b][hr]A standardized xml type containing the set (think something installed in your schemas collection) would be far easier to use and debug plus SQL server is a stud at using XML.[/quote]Reporting Services will only automatically generate a comma-delimited string for multi-value parameters; it won't generate XML. So I would have to write a parser in Reporting Services to express the parameters in XML before passing them to T-SQL where I then have to break the XML down again.Neither of those is actually difficult, but I don't see the value in spending the extra time [b]every time I call a stored procedure from SQL Server Reporting Services[/b] to ensure that any muti-value parameters are encoded in XML.In this case, it would make the interface between reporting services and SQL Server [b]harder[/b] to use or debug.[/quote]Wow that sucks (about SSRS). The point is still valid and I'd actually suggest you in general try to keep to the xml array as the "native" input parameter and have an SSRS wrapper function that deals with the delimited string coercion. This is a fairly common implementation of an anti-corruption layer at work here.That's a bit of good trivia there Bruce. thanks!Fri, 01 Mar 2013 11:13:43 GMTjimmyzimmshttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]jimmyzimms (3/1/2013)[/b][hr]A standardized xml type containing the set (think something installed in your schemas collection) would be far easier to use and debug plus SQL server is a stud at using XML.[/quote]Reporting Services will only automatically generate a comma-delimited string for multi-value parameters; it won't generate XML. So I would have to write a parser in Reporting Services to express the parameters in XML before passing them to T-SQL where I then have to break the XML down again.Neither of those is actually difficult, but I don't see the value in spending the extra time [b]every time I call a stored procedure from SQL Server Reporting Services[/b] to ensure that any muti-value parameters are encoded in XML.In this case, it would make the interface between reporting services and SQL Server [b]harder[/b] to use or debug.Fri, 01 Mar 2013 10:37:34 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxPassing XML as the parameter is actually another good use-case for a similar technique, and I've done this where I was dealing with an object-level interface. In that way, the parameter can be passed an entire object worth of data, or for that matter, a set of objects, which can have parent-child relationships encoded within the XML (I used this to pass sets of invoices around between the data layer in the middle tier and the database).However, that's not where I have used the technique of comma-delimited strings as parameters; that's more about working with reporting services. I've scratched my head a bit, but I can't think of another case where I've passed sets of values as a delimited string. I have [b]seen[/b] it used to express lists of values in a "parameter" table, but I think that's just silliness and [b]not[/b] a valid reason to use this technique (yes, we are allowed to have tables with relationships!)I will admit that once I am in T-SQL, if I have to pass sets around as parameters, I will do it either within a table or as a table-variable. If I only ever lived in SQL Server (or Oracle), I wouldn't need to be passing sets as delimited strings.I wonder what TRON would have looked like if it were based on an RDBMS rather than a game?My other thought was that SQL as a language is based around working with sets of data. It shouldn't matter too much about how that data is encoded; whether it's XML, a delimited string, a file, a spread sheet or a table within the database, I still want to be able to apply the same capabilities of SQL to it. From that point of view, taking a comma-delimited string and expressing it as a table is perfectly valid.Fri, 01 Mar 2013 10:31:48 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxA standardized xml type containing the set (think something installed in your schemas collection) would be far easier to use and debug plus SQL server is a stud at using XML. Unless you're running huge inputs regularly for your IN clause, you really don't need to probably micro-optimizeFri, 01 Mar 2013 10:18:43 GMTjimmyzimmshttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Sean Lange (3/1/2013)[/b][hr]It is fast on the sql side of things but I have never really been convinced that applications gain much benefit because of all the extra data that has to be passed.[/quote]+1000 to that!Shredding an XML parameter takes a bit fewer resources than parsing a CSV with DelimitedSplit8K and is even a bit faster. An XML parameter also allows you to easily break the 8k barrier. Heh... of course, with XML bloat, you almost have to. :-DThe other thing that people fail to consider is all the other resources involved. Like the "pipe". It's much more expensive on the pipe to pass XML than it is CSV or TSV. It's also more expensive on the I/O of the server. Being I/O bound is much more difficult to fix than optimizing some code.Fri, 01 Mar 2013 08:29:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]Sean Lange (3/1/2013)[/b][hr][quote][b]Irozenberg 1347 (3/1/2013)[/b][hr]Elementary sub-query or EXISTS clause would Radically improve performance.If you like fancy coding you could pass XML as parameter and use XQUERY. [/quote]You should read the article about the splitter. It will blow the doors off an XML parser. You can find the article by following the link in my signature about splitting strings. The article is great but the ensuing discussion (although incredibly lengthy) is even better.[/quote] Be careful now. He's not talking about passing a CSV and using an XML parser to split it. He's talking about passing actual XML and parsing it, which is actually very fast.[/quote]Ahh I guess I misread that. :blush:It is fast on the sql side of things but I have never really been convinced that applications gain much benefit because of all the extra data that has to be passed.Fri, 01 Mar 2013 08:17:54 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Sean Lange (3/1/2013)[/b][hr][quote][b]Irozenberg 1347 (3/1/2013)[/b][hr]Elementary sub-query or EXISTS clause would Radically improve performance.If you like fancy coding you could pass XML as parameter and use XQUERY. [/quote]You should read the article about the splitter. It will blow the doors off an XML parser. You can find the article by following the link in my signature about splitting strings. The article is great but the ensuing discussion (although incredibly lengthy) is even better.[/quote] Be careful now. He's not talking about passing a CSV and using an XML parser to split it. He's talking about passing actual XML and parsing it, which is actually very fast.Fri, 01 Mar 2013 08:02:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Irozenberg 1347 (3/1/2013)[/b][hr]Elementary sub-query or EXISTS clause would Radically improve performance.If you like fancy coding you could pass XML as parameter and use XQUERY. [/quote]Since you made a claim of performance, let's see your "elementary sub-query or EXISTS" clause.I do agree that passing (ugh!) XML would also be fast but it has no respect for the "pipe".Fri, 01 Mar 2013 07:59:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Irozenberg 1347 (3/1/2013)[/b][hr]Elementary sub-query or EXISTS clause would Radically improve performance.If you like fancy coding you could pass XML as parameter and use XQUERY. [/quote]You should read the article about the splitter. It will blow the doors off an XML parser. You can find the article by following the link in my signature about splitting strings. The article is great but the ensuing discussion (although incredibly lengthy) is even better.Fri, 01 Mar 2013 07:18:08 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx@tfifieldI apologize for not replying to your question. I was in the middle of rolling out our production SAP implementation at corporate when I posted and, well, let's just say I was a bit distracted.I haven't benchmarked the performance but I'll defend my laziness by saying I put the code I write into one of two buckets: a "I run this occasionally to get DBA-related info or for troubleshooting" and "This is executed hundreds/thousands of times a day by end users". This code sort of falls into the first bucket. I've used this XML parsing stunt quite a bit (I truly wish I could remember where I saw it first and give thanks to the original [strike]author[/strike] genius that wrote it) and for small sets of parsed data it works famously.Fri, 01 Mar 2013 07:16:09 GMTYour Name Herehttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxThanks for article. Good reminder.Fri, 01 Mar 2013 07:12:03 GMTBruce Dowhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxNice article, thank you.So we can make a dynamic in clause for a standard query. But we can't for the pivot clause.Why Microsoft, why?Fri, 01 Mar 2013 06:50:04 GMTgary.strange-sqlconsumerhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Brett Phipps (6/21/2011)[/b][hr][quote][b]Celko[/b]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. [/quote]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".[/quote]SOP for Mr. Celko. I recommend using a DelimitedSplit routine for this type of thing on a regular basis, I just include it directly in the IN clause, not a separate CTE.Fri, 01 Mar 2013 06:34:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxOne thing I found with TVPs is that they cannot be passed over a linked server :(. It was the only time I needed it and I too had to go with a split.I have a split function that allows you to pass in # of columns too. So, '1,First Item,2,Second Item' becomes...1 First Item2 Second ItemI might try to apply some concepts here to enhance that function.Nice work!Fri, 01 Mar 2013 06:19:08 GMTjcunninghamhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxIf the JDBC driver supports tables as parameters to procedures, let me know :-)- heh, oracle supports it !!After doing a search on the best way to pass a list of values (from java) to a stored proc, microsoft directed me to use XML instead of a delimited list.This article gives a good run down of this approach...https://www.simple-talk.com/blogs/2012/01/05/using-xml-to-pass-lists-as-parameters-in-sql-server/Fri, 01 Mar 2013 04:43:05 GMTsqlchanhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxElementary sub-query or EXISTS clause would Radically improve performance.If you like fancy coding you could pass XML as parameter and use XQUERY. Fri, 01 Mar 2013 03:28:31 GMTIrozenberg 1347http://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxThe most common reason I use much the same approach (even to the point of using Jeff's code too) is in passing multi-value parameters from SQL Server reporting services into stored procedures. I could I suppose include some preparatory code that creates a table variable, and then passes that as a parameter, but then I can't just use the reporting services "call a stored procedure" functionality, and it breaks the abstraction level of procedural logic too.If you're writing inline queries in reporting services, you [b]can[/b] do the "IN (@list)" thing... but I've found that embedding anything more than a simple query inside reporting services reports to be a poor practice. So that's not practical solution either.A very real use case, and a thus a very real reason why the same "poor practice" (to paraphrase Joe Celko) gets reinvented constantly. While I appreciate Joe's dedication to SQL as a language, some of us actually have to use the thing, and purism aside, it becomes a case of "what are my business requirements; now how do I meet them?"If Joe has an alternative that meets this use case, I'm all ears.Thu, 28 Feb 2013 23:44:38 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxYes, the optimiser assumes only one row in a table variable.Thu, 23 Jun 2011 11:30:12 GMThallidaydhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxThank you for the article. It's building nicely on the methods of splitting strings by Jeff Moden and others. I am actually very curious about this sentence: [quote]I've found that table variables used in a JOIN can be slow since the optimizer considers that there is only 1 row and the table has no statistics.[/quote]Can somebody else confirm or deny this? Recently I was tuning one very large stored procedure and one of the methods I tried for optimizing it was to replace table variables with temp tables (they [u]are [/u] used in JOINs), however I haven't noticed appreciable difference in performance. This would, of course, be very specific to each individual scenario, so I am just posting this general question to see if anybody else has had any similar experiences.Wed, 22 Jun 2011 22:39:03 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]vanslly (6/21/2011)[/b][hr]There is an alternative. Read: http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL[/quote]When I originally read this article (on SQLServerCentral) I thought it may discuss the topic of the article you have linked to (table valued parameters).I think that TVPs are great, but you can't always get the data in a table format from your calling application. The article linked above even states that.And the linked article even shows what to do if you can't - which seems to be about the same as described in this article on SSC - receive a list - split it up - join to it, etc. (this is described at this part of the linked article: http://www.sommarskog.se/arrays-in-sql-2005.html#iter_intlist_to_tbl)It is nice to know there are many methods.Wed, 22 Jun 2011 05:11:32 GMTdave.clarkhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxThere is an alternative. Read: http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQLTue, 21 Jun 2011 22:17:51 GMTvansllyhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]bphipps-931675 (6/21/2011)[/b][hr]Why bother with the temp table? Why not just imbed the select into your where clause?[code="sql"]SELECT SO.SalesPersonID, C.FirstName + ' ' + C.LastName AS SalesPerson, COUNT(*) AS Orders, SUM(SO.SubTotal) AS TotalAmountFROM Sales.SalesOrderHeader SOINNER JOIN HumanResources.Employee E ON SO.SalesPersonID = E.EmployeeIDINNER JOIN Person.Contact C ON E.ContactID = C.ContactIDWHERE 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[/code][/quote]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 FifieldTue, 21 Jun 2011 16:32:52 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]bphipps-931675 (6/21/2011)[/b][hr][quote][b]Celko[/b]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. [/quote]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".[/quote]Thanks for backing me up on this.Todd FifieldTue, 21 Jun 2011 16:22:21 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Celko[/b]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. [/quote]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".Tue, 21 Jun 2011 12:44:01 GMTBrett Phippshttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxWhy bother with the temp table? Why not just imbed the select into your where clause?[code="sql"]SELECT SO.SalesPersonID, C.FirstName + ' ' + C.LastName AS SalesPerson, COUNT(*) AS Orders, SUM(SO.SubTotal) AS TotalAmountFROM Sales.SalesOrderHeader SOINNER JOIN HumanResources.Employee E ON SO.SalesPersonID = E.EmployeeIDINNER JOIN Person.Contact C ON E.ContactID = C.ContactIDWHERE 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[/code]Tue, 21 Jun 2011 12:31:40 GMTBrett Phippshttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]CELKO (6/21/2011)[/b][hr]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.[/quote]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 FifieldTue, 21 Jun 2011 11:27:42 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Your Name Here (6/21/2011)[/b][hr]Hey ToddRe: 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,KenPS I can't claim to have come up with this - I found it somewhere on the net.[/quote]Ken,Thanks for your input. I'll give it a try.Have you ever bench marked it against the technique I'm using?Todd FifieldTue, 21 Jun 2011 11:13:24 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]TheSQLGuru (6/21/2011)[/b][hr]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.[/quote]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 FifieldTue, 21 Jun 2011 11:11:16 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]Sarus-127369 (6/21/2011)[/b][hr]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 [b]','+@inStr+',' like '%,'+cast(column as varchar)+',%'[/b][/quote]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 FifieldTue, 21 Jun 2011 11:07:24 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspx[quote][b]irozenberg (6/20/2011)[/b][hr]Todd, thank you for article.Did you try UDF that return a table? Cheers[/quote]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 FifieldTue, 21 Jun 2011 11:05:03 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxSome 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.Tue, 21 Jun 2011 08:23:51 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1128721-2740-1.aspxGood article. I still think though using User-Defined Table Types is easier and fasterTue, 21 Jun 2011 07:34:18 GMTCodernater