InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 07:09:11 GMT20http://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspxSean/Jeff: Thank you. I read the articles AFTER I posed. :unsure: I ran some tests and I'm amazed by DelimitedSplit8K's speed. I ran tests using both methods on the following string -- just random jibberish:'1,2,4,5,6,7,8,99,10,2,4,5,6,7,8,99,10,2,4,5,6,7,8,99,10,2,4,5,6,7,8,99,10,2,4,5,6,7,8,99,10'XML : 55 secondsDS8K : 43 seconds (28% faster!)Great work!I guess the good news here is that if I need to screaming performance, use DS8K. :-DGood news for my project is that the DBAs understand the XML version; but I'm going to mention DS8K for a project that really needs to scream.Thank you for all your great work and time! (I enjoy seeing the objective analysis you two have put together on this topic. I just wish Microsoft would address this issue. :-D)(ah man, i really thought i was submitting a good nugget! i felt like i was hording it for years lol)Fri, 09 Nov 2012 12:21:42 GMTLANdango.comhttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspx[quote][b]LANdango.com (11/9/2012)[/b][hr]The anwers above are pretty good; however, there's a short cut. use XML. In just a few lines you can get what you want and the performance is really nice.[/quote]The performance of the DELETE is really nice. If you have a lot of "hits" on a proc that passes data in a CSV format, then you should probably reconsider the splitter method for performance purposes (unless you actually pass XML). XML splitters of this nature are nearly as slow as a While Loop.[img]http://www.sqlservercentral.com/Images/9264.gif[/img]The little black line near the bottom is the new DelimitedSplit8K function.Fri, 09 Nov 2012 11:42:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspx[quote][b]LANdango.com (11/9/2012)[/b][hr]The anwers above are pretty good; however, there's a short cut. use XML. In just a few lines you can get what you want and the performance is really nice. Using this approach eliminates the need for round-trips to the server. (Think how fast this would be by flagging data grid rows, collecting the IDs into a string and then calling a procedure [b]one time[/b] to delete all the selected records in just one call! Heck, you could return the table of ids from a table valued function. Have fun with it![code]declare @xml xml set @xml = N'<root><r>' + replace(replace(@CommaSeparatedListOfIds,',','</r><r>'),' ', '') + '</r></root>'Delete from MyTable where ID IN ( select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r))[/code]DONE! :w00t:Here's a working sample:[code]Create proc DeleteStories(@CommaSeparatedListOfIds NVARCHAR(MAX))ASBEGIN /*11/09/2012::Created by M.M.(www.reinid.com / www.nmtcr.com) Purpose: Deletes a records by rows Sample execution: exec DeleteStories '1,2,3,4,10,100,1000,1001,2001' */ /*Next section is not really needed if the input is clean set @CommaSeparatedListOfIds = replace(@CommaSeparatedListOfIds, ',,',',') --just in case doubles come in --Ensure that there are no trailing commas in input while(right(@CommaSeparatedListOfIds,1) = ',') begin set @CommaSeparatedListOfIds = LEFT(@CommaSeparatedListOfIds,len(@CommaSeparatedListOfIds)-1) end */ --Here's the real "magic" --Create an XML document that will be used to extract the IDs from the string input declare @xml xml set @xml = N'<root><r>' + replace(replace(@CommaSeparatedListOfIds,',','</r><r>'),' ', '') + '</r></root>' --Display the values select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r) --Or use the r.value in an IN statement /* Delete from StoriesTable where StoryId IN (select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r)) */END[/code][/quote]You should read the article referenced repeatedly. In case you missed it you can find it in my signature about splitting strings. The XML type of splitter you posted is not only discussed but performance tested in that article. It is way faster than this type of XML splitting.[quote]Using this approach eliminates the need for round-trips to the server. (Think how fast this would be by flagging data grid rows, collecting the IDs into a string and then calling a procedure [b]one time[/b] to delete all the selected records in just one call! Heck, you could return the table of ids from a table valued function.[/quote]This is EXACTLY what the Delimited Splitter does. Returns the data from a table valued function. The scenario you describe is the entire reason it was created. There is nothing inherently wrong with the XML splitter but the other version will beat on performance. :-)Fri, 09 Nov 2012 09:50:21 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspxThe anwers above are pretty good; however, there's a short cut. use XML. In just a few lines you can get what you want and the performance is really nice. Using this approach eliminates the need for round-trips to the server. (Think how fast this would be by flagging data grid rows, collecting the IDs into a string and then calling a procedure [b]one time[/b] to delete all the selected records in just one call! Heck, you could return the table of ids from a table valued function. Have fun with it![code]declare @xml xml set @xml = N'<root><r>' + replace(replace(@CommaSeparatedListOfIds,',','</r><r>'),' ', '') + '</r></root>'Delete from MyTable where ID IN ( select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r))[/code]DONE! :w00t:Here's a working sample:[code]Create proc DeleteStories(@CommaSeparatedListOfIds NVARCHAR(MAX))ASBEGIN /*11/09/2012::Created by M.M.(www.reinid.com / www.nmtcr.com) Purpose: Deletes a records by rows Sample execution: exec DeleteStories '1,2,3,4,10,100,1000,1001,2001' */ /*Next section is not really needed if the input is clean set @CommaSeparatedListOfIds = replace(@CommaSeparatedListOfIds, ',,',',') --just in case doubles come in --Ensure that there are no trailing commas in input while(right(@CommaSeparatedListOfIds,1) = ',') begin set @CommaSeparatedListOfIds = LEFT(@CommaSeparatedListOfIds,len(@CommaSeparatedListOfIds)-1) end */ --Here's the real "magic" --Create an XML document that will be used to extract the IDs from the string input declare @xml xml set @xml = N'<root><r>' + replace(replace(@CommaSeparatedListOfIds,',','</r><r>'),' ', '') + '</r></root>' --Display the values select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r) --Or use the r.value in an IN statement /* Delete from StoriesTable where StoryId IN (select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r)) */END[/code]Fri, 09 Nov 2012 08:26:17 GMTLANdango.comhttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspx[quote][b]Sean Lange (9/11/2012)[/b][hr][quote][b]CELKO (9/11/2012)[/b][hr]I have a two articles on this topic at Simple Talk. There is no need to throw away performance, maintainabilty and portability to do this. http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/[/quote]If portability to another DBMS is a concern then the methods Joe discusses are a good solution. On the other hand, if you don't see yourself switching to another DBMS in the near future and you want to keep your maintenance a lot simpler then you might be better off following the ideas that Craig has suggested. For me, I am a sql server guy and I don't really worry about portability. I also don't want to have to alter my procedure and redeploy my data access tier every time I need to make the list longer.[/quote]+1000^1000!Use the capabilities of the system you are using. How often do you see companies changing database systems? It isn't something done lightly or on a frequent basis.Tue, 11 Sep 2012 08:11:41 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspx[quote][b]CELKO (9/11/2012)[/b][hr]I have a two articles on this topic at Simple Talk. There is no need to throw away performance, maintainabilty and portability to do this. http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/[/quote]If portability to another DBMS is a concern then the methods Joe discusses are a good solution. On the other hand, if you don't see yourself switching to another DBMS in the near future and you want to keep your maintenance a lot simpler then you might be better off following the ideas that Craig has suggested. For me, I am a sql server guy and I don't really worry about portability. I also don't want to have to alter my procedure and redeploy my data access tier every time I need to make the list longer.Tue, 11 Sep 2012 08:08:57 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspx[quote][b]CELKO (9/11/2012)[/b][hr]I have a two articles on this topic at Simple Talk. There is no need to throw away performance, maintainabilty and portability to do this. http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/[/quote]You do know that if you put the IFCode shortcuts [ url ] and [ /url ] (no spaces inside the square brackets) around your urls above that people could actually click on them and go directly to those articles without having to waste their time doing a cut and paste.Tue, 11 Sep 2012 08:08:20 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspxI have a two articles on this topic at Simple Talk. There is no need to throw away performance, maintainabilty and portability to do this. http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/Tue, 11 Sep 2012 07:28:57 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspx[quote][b]anonymous2009 (9/9/2012)[/b][hr]Thanks.Got the below Table-Valued Function logic from:http://www.sommarskog.se/arrays-in-sql-2005.html#CSV (Click on Erland's CLR version under CLR) via the link you provided http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx[b][u]Table-Valued Function that unpacks the string into a table:[/u][/b][quote]CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX)) RETURNS @tbl TABLE (number int NOT NULL) ASBEGIN DECLARE @pos int, @nextpos int, @valuelen int SELECT @pos = 0, @nextpos = 1 WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex(',', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1 INSERT @tbl (number) VALUES (convert(int, substring(@list, @pos + 1, @valuelen))) SELECT @pos = @nextpos END RETURNEND[/quote]The function iterates over the string looking for commas, and extracts the values one by one. The code is straightforward, and makes use of some of the string functions in <small>T-SQL</small>. The most complex part is the <small>CASE</small> expression which exists to handle the last value in the string correctly. This example shows how you could use this function:[quote]CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS SELECT P.ProductName, P.ProductID FROM Northwind..Products P JOIN iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.numbergoEXEC get_product_names_iter '9, 12, 27, 37'[/quote][b]The article does mention that the above function above is not extremely speedy.[/b]Though my database is in SQL SERVER 2008, I used the above Table-Values Function approach over Table-valued Parameter logic mentioned in http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL since in my usecase there wont be more than 30 to 35 items in comma separated list. Moreover, with the above non-TVP approach, I dont have to create a new TYPE object.Please let me know if the choice is inefficent for my usecase.Thanks![/quote]Consider NOT using a WHILE loop and scalar UDF for doing splits because both make code unnecessarily slow. Please see the article at the link that "Evil Kraig F" provided for a much more effecient splitter.Sun, 09 Sep 2012 22:08:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspxThanks.Got the below Table-Valued Function logic from:http://www.sommarskog.se/arrays-in-sql-2005.html#CSV (Click on Erland's CLR version under CLR) via the link you provided http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx[b][u]Table-Valued Function that unpacks the string into a table:[/u][/b][quote]CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX)) RETURNS @tbl TABLE (number int NOT NULL) ASBEGIN DECLARE @pos int, @nextpos int, @valuelen int SELECT @pos = 0, @nextpos = 1 WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex(',', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1 INSERT @tbl (number) VALUES (convert(int, substring(@list, @pos + 1, @valuelen))) SELECT @pos = @nextpos END RETURNEND[/quote]The function iterates over the string looking for commas, and extracts the values one by one. The code is straightforward, and makes use of some of the string functions in <small>T-SQL</small>. The most complex part is the <small>CASE</small> expression which exists to handle the last value in the string correctly. This example shows how you could use this function:[quote]CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS SELECT P.ProductName, P.ProductID FROM Northwind..Products P JOIN iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.numbergoEXEC get_product_names_iter '9, 12, 27, 37'[/quote][b]The article does mention that the above function above is not extremely speedy.[/b]Though my database is in SQL SERVER 2008, I used the above Table-Values Function approach over Table-valued Parameter logic mentioned in http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL since in my usecase there wont be more than 30 to 35 items in comma separated list. Moreover, with the above non-TVP approach, I dont have to create a new TYPE object.Please let me know if the choice is inefficent for my usecase.Thanks!Sun, 09 Sep 2012 18:25:22 GMTanonymous2009http://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspxAlright, finally home and can spend a little time on this. First, get your hands on a copy of the function DelimitedSplit8k from here: [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]Read the article as well, please. You'll have to get acquainted with the Tally Table as well. These can be some more advanced topics but the short version is if you copy/paste the code and feed it a string with comma delimited values it'll hand you back a 'table' of those values as a single column. My usual disclaimer here: If you don't understand the code you're putting in production, DON'T.Anyways, that said, once you get a handle on it, you'll usually have a proc that looks a little like this before you start and it won't work because IN won't take your variable:[code="sql"]CREATE PROC GetData @SplitmeString VARCHAR(8000)ASSELECT st.*FROM SomeTable AS stWHERE st.ID IN (@SplitmeString)GO[/code]Call would be something like:EXEC GetData @SplitmeString = '1,2,4,8,16,32,64'So, with our new handy-dandy function, now our proc will look like this:[code="sql"]CREATE PROC GetData @SplitmeString VARCHAR(8000)ASSELECT st.*FROM SomeTable AS st JOIN DelimitedSplit8K( @SplitmeString, ',') AS dsk ON st.ID = dsk.ItemGO[/code]So, what have we really done? Basic SQL: An INNER JOIN restricts row data coming from both sides via the ON clause. Because your list (even if it only had one element) is now restricting to only the IDs you want, the JOIN takes the place of the WHERE. The function returns a table from your string, and thus will allow you to use it that way.This gets around the entire problem of trying to dynamically construct code to allow your delimited string to be included in the query.Tue, 04 Sep 2012 20:08:14 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspxThanks!Can you please give an example of how to do it?I'm quite new to sql server and would really appreciate the help.ThanksTue, 04 Sep 2012 17:22:10 GMTanonymous2009http://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspxI would recommend approaching this differently. IN ( @variable ) is notoriously a pita to get to work.Instead, use a string splitter (delimited8k, SQLCLR, whatever you like) and turn your string ('10,11,12,18,42') into a joined iTVF for the procedure. It'll take from 1 through x parameters that way and you'll avoid a lot of the headaches.That's the incredibly short version of the task, and it'll get a lot more detailed if you're not familiar with that. Let us know if that doesn't get you on the right track and either I or someone else will spell it out when we get more time. If you need more assistance if you can post the source Proc that will help us integrate what you'll need.Tue, 04 Sep 2012 17:07:19 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1354247-391-1.aspxHello,Following are the contents of script Delete_employees.sql.[quote]USE CompanyDBBEGIN TRANDELETE FROM dbo.employeeWHERE empid IN ([b]<Comma separated values passed from SQLCMD>[/b])COMMIT TRAN;[/quote][b][u]Question:[/u][/b]Will I be able to pass comma separate values from SQLCMD for the IN Clause in DELETE statement?I tried running the above but it does seem to work:[quote]sqlcmd -S localhost\testEXPRESS -i Delete_employees.sql -v empid=100[/quote]When I change the IN clause to = in delete sql statement within Delete_employees.sql script, the above SQLCMD works.But I want to be able to pass comma separate employee ids (which is a INT) for the IN clause in DELETE statement.Can somebody help me figure out a way to do that?Thanks!Tue, 04 Sep 2012 16:46:43 GMTanonymous2009