using CLR that returns table
in [Programming SQL]
|
Prev: Select Query Problem
Next: Time data type
From: Jay on 29 Nov 2009 11:27 Erland, I searched Adam's blog for the multi-character delimiters, but came up empty. Could you be more specific please? http://sqlblog.com/blogs/adam_machanic/ "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CD2AD8363BAFYazorman(a)127.0.0.1... > Roy Goldhammer (roy(a)top.com) writes: >> I have procedure who runs approx 10 times in one second, getting huge >> sting with many words, >> >> I need to convert the list of these words to table where there are more >> then one delimiter like space, ',', ';', enter, NL and more. >> >> for example: "one two,three >> four >> five" should be 5 words. >> >> on sql server i need to go one by one and it cause me alot. ON clr there >> is split option which do it very fast. But as i explain it saves the >> data into temp table on tempdb, which cause me to work very slow. > > Kevin is correct. CLR table functions are efficient, because the data is > streamed into the query. There is no intermediate storage. I have however > noticed that plans with CLR functions tends to include a spool operator, > but I don't think this is the same thing. > > However, using .Split is not the best. There are people who have tried it, > and experienced scaling problems. > > Look at http://www.sommarskog.se/arrays-in-sql-2005.html#CLR where I > describe both a Split-based function and rolling your own. Also look up > Adam Machanic's blog on http://www.sqlblog.com. He has a couple of > more versatile suggestions that handle multi-character delimiters. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Erland Sommarskog on 29 Nov 2009 12:40 Jay (spam(a)nospam.org) writes: > I searched Adam's blog for the multi-character delimiters, but came up > empty. Could you be more specific please? Since when did anyone find anything on sqlblog? :-) I have the link since Adam sent it to me: http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string- splitting-part-2-even-faster-even-more-scalable.aspx His main point is not that he handles multi-character delimiters, but that the code is said to scale well. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Jay on 29 Nov 2009 16:06 I was expecting T-SQL, in part, because I've never heard of SQLCLR. BOL seems a tad vague, so could you tell me the entry point for running this script? "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CD2BE0FF7AE4Yazorman(a)127.0.0.1... > Jay (spam(a)nospam.org) writes: >> I searched Adam's blog for the multi-character delimiters, but came up >> empty. Could you be more specific please? > > Since when did anyone find anything on sqlblog? :-) > > I have the link since Adam sent it to me: > http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string- > splitting-part-2-even-faster-even-more-scalable.aspx > > His main point is not that he handles multi-character delimiters, but > that the code is said to scale well. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Erland Sommarskog on 29 Nov 2009 16:52 Jay (spam(a)nospam.org) writes: > I was expecting T-SQL, in part, because I've never heard of SQLCLR. BOL > seems a tad vague, so could you tell me the entry point for running this > script? You've never heard of SQLCLR? Gee, have you been a looong vacation for the last four years or so? OK, excuse me for the sarcasm, but SQL 2005, that was released four years ago, permits you to create objets implemented in .Net languages such as C# or VB .Net. You can create stored procedures, functions, triggers, aggregates and types in the CLR. On my web site, I can offer these introductions: http://www.sommarskog.se/dyn-search-2005.html#CLR http://www.sommarskog.se/arrays-in-sql-2005.html#CLR These are necessarily not the best introductions to write CLR code in SQL Server, but they should be enough to get the examples going. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Jay on 29 Nov 2009 18:21
Erland, When I click on the first link, my AVG Anti-Virus goes nuts. You may have been hijacked. Jay "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CD2E8B1450AYazorman(a)127.0.0.1... > Jay (spam(a)nospam.org) writes: >> I was expecting T-SQL, in part, because I've never heard of SQLCLR. BOL >> seems a tad vague, so could you tell me the entry point for running this >> script? > > You've never heard of SQLCLR? Gee, have you been a looong vacation for the > last four years or so? > > OK, excuse me for the sarcasm, but SQL 2005, that was released four years > ago, permits you to create objets implemented in .Net languages such as > C# or VB .Net. You can create stored procedures, functions, triggers, > aggregates and types in the CLR. > > On my web site, I can offer these introductions: > http://www.sommarskog.se/dyn-search-2005.html#CLR > http://www.sommarskog.se/arrays-in-sql-2005.html#CLR > These are necessarily not the best introductions to write CLR code in > SQL Server, but they should be enough to get the examples going. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |