May 3, 2011 at 5:19 pm
gary.rumble (5/3/2011)
Jeff Moden (5/2/2011)
gary.rumble (5/2/2011)
Well, so far I got:...
I think your code is too much for my server. 😉
Yowch. The code is even split into batches. What you may have to do is run the code a section at a time up to where the test loop begins and the let the test loop rip.
Thanks for trying, Gary. If for some reason, your server just won't take it, let me know and we'll test your code for you.
I set up a new SS instance on my laptop and got the tests to run. Looks like my code tracks your results fairly closely, but of course it doesn't create the numbers table on the fly so it probably has an advantage there.
I emailed you the results. I couldn't generate your pretty graphs, though.
Thanks for the article.
No problem, Gary. You obviously had to convert your code to a function to run it through my test harness. Could you post your function, please. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2011 at 5:22 pm
ChrisM@home (5/3/2011)
Jeff, please please please write a book, without changing your style. Give us all an opportunity to repay you for what you've done for us.
Gosh, with a wonderful request like that, I'd feel like a heel (or a poet and don't know it :-P) if I didn't at least reconsider it. Thanks, Chris.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2011 at 5:28 pm
Nadrek (5/3/2011)
WayneS (5/3/2011)
Why don't you take your functions, integrate them into the test script included in the References section of the article, run the tests, and post the results for all of us to enjoy?All right, but I'm not much with the fancy graphs, so here's the output exported as a csv (I don't know what the forum would do to tabs)
Good thing I didn't do that for the article, huh? You could have put it in a spreadsheet as the comments in the code suggested and then attached the spreadsheet. :Whistling:
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2011 at 5:31 pm
Goldie Lesser (5/3/2011)
Thanks for the great article Jeff!Eagerly awaiting the varchar(max) versions so I can incorporate them into my databases. 🙂
Does anyone have a theory as to why the CTE beats the permanent table?
Thanks for the feedback, Goldie. We're still looking at the CTE vs. permanent table thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2011 at 5:34 pm
Jeff Moden (5/3/2011)
gary.rumble (5/3/2011)
Jeff Moden (5/2/2011)
gary.rumble (5/2/2011)
Well, so far I got:...
I think your code is too much for my server. 😉
Yowch. The code is even split into batches. What you may have to do is run the code a section at a time up to where the test loop begins and the let the test loop rip.
Thanks for trying, Gary. If for some reason, your server just won't take it, let me know and we'll test your code for you.
I set up a new SS instance on my laptop and got the tests to run. Looks like my code tracks your results fairly closely, but of course it doesn't create the numbers table on the fly so it probably has an advantage there.
I emailed you the results. I couldn't generate your pretty graphs, though.
Thanks for the article.
No problem, Gary. You obviously had to convert your code to a function to run it through my test harness. Could you post your function, please. Thanks.
It is thus:
IF OBJECT_ID('dbo.GRDelimitedSplit') IS NOT NULL
DROP FUNCTION dbo.GRDelimitedSplit;
GO
CREATE FUNCTION [dbo].[GRDelimitedSplit]
(@text VARCHAR(max), @delimiter CHAR(1))
RETURNS @Return TABLE (ItemNumber SMALLINT, Item VARCHAR(max))
WITH SCHEMABINDING AS
begin
declare @len int
set @len = len(@text) + 1
;with cte1 as (
select 0 as number, 0 as row
union
select number, row_number() over (order by number) as row from dbo.Numbers
where number <= @len and substring(@text, number, 1) = @delimiter
)
insert into @Return
select
ROW_NUMBER() OVER(ORDER BY c1.row),
substring(@text, c1.number + 1, coalesce(c2.number - 1, @len) - c1.number) from cte1 c1
left join cte1 c2 on c1.row = c2.row - 1
order by c1.row
return;
end
go
14090 SW TENNESSEE LN
May 3, 2011 at 5:38 pm
WayneS (5/3/2011)
Oh Jeff... looks like the Perm0Based is beating your newest, hottest code! (Man, I love it when the community comes together like this!)
Very cool and absolutely correct. I love new toys.
Heh... Peso sent me one that looks like a winner and Gary's code (according to the spreadsheet he sent me) does a good job, as well. Of course, you know me... I'll set them all up and learn more than I taught in the article.
What I find really strange is why do people wait until I write an article to bring these wonders forward? 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2011 at 5:40 pm
gary.rumble (5/3/2011)
It is thus:
Outstanding. Time for some more tests at the Moden house. Thanks, Gary.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2011 at 5:59 pm
Jeff Moden (5/3/2011)
What I find really strange is why do people wait until I write an article to bring these wonders forward? 😛
Maybe it is because they are applying what they have learned from your article!
May 3, 2011 at 7:32 pm
Nadrek (5/3/2011)
Also, an easy way to get SQL Server to output SELECT results to a special character delimited VARCHAR() table, and directly to a text file would be very worthwhile for the article. The main uses I see are getting data to/from arbitrary SELECT results into a string with one or two delimiters/dimensions, and the same thing to/from a text file.
Hey there. Just FYI (especially since Jeff also mentioned being interested in this function in an earlier post here), this is already available for free in the SQL# library (http://www.SQLsharp.com/). It is the DB_BulkExport Proc. There are details on how to use it in the manual and I also wrote an article with an example of it here:
http://www.sqlservercentral.com/articles/SQL+Server+2005/63300/
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 3, 2011 at 10:01 pm
Jeff,
That was one of the best articles I read recently. Terrific job!
BTW, is there a way to contact you privately somehow?
Thanks again.
May 4, 2011 at 3:58 am
This is the CLR code I wrote for Jeff. It's not flash, but it is simple, fast, and handles strings of any length, Unicode or not.
There are three implementations below. Splitter 'B' is the one referred to above, and works on all versions of SQL Server from 2005 RTM onward.
Splitter 'A' features simpler code, but fell victim to a SQL Server bug introduced in 2008 RTM. I have tested Splitter A on 2005 and 2008 SP2 with no problems.
Splitter 'C' is a .NET 3.5 implementation provided for interest - it requires at least SQL Server 2008.
All versions are very fast - 2-3 times faster than the best TSQL solutions. I suspect Splitters A & B may be slighty faster than C, but I have never bothered to test. I only write basic C#as a hobby; no doubt better implementations are possible.
If you need a splitter that handles mutliple-character delimiters, try Adam Machanic's code here:
TSQL deployment code first, source code second.
-- ====================================
-- Splitter A : NET2 yield (2K8 RTM bug)
-- Splitter B : NET2 explicit enumeration (Fine on all versions)
-- Splitter C : LINQ NET3.5 (not 2K5 compatible)
-- ====================================
-- ====================================
-- Drop the CLR functions if they exist
-- ====================================
IF EXISTS
(
SELECT 1
FROM sys.objects
WHERE
[object_id] = OBJECT_ID(N'dbo.SplitterA')
AND type_desc = N'CLR_TABLE_VALUED_FUNCTION'
)
BEGIN
DROP FUNCTION dbo.SplitterA;
END;
IF EXISTS
(
SELECT 1
FROM sys.objects
WHERE
[object_id] = OBJECT_ID(N'dbo.SplitterB')
AND type_desc = N'CLR_TABLE_VALUED_FUNCTION'
)
BEGIN
DROP FUNCTION dbo.SplitterB;
END;
IF EXISTS
(
SELECT 1
FROM sys.objects
WHERE
[object_id] = OBJECT_ID(N'dbo.SplitterC')
AND type_desc = N'CLR_TABLE_VALUED_FUNCTION'
)
BEGIN
DROP FUNCTION dbo.SplitterC;
END;
GO
-- ====================================
-- Drop the assemblies if they exist
-- ====================================
IF EXISTS
(
SELECT 1
FROM sys.assemblies
WHERE
name = N'SplitterA'
)
BEGIN
DROP ASSEMBLY SplitterA;
END;
IF EXISTS
(
SELECT 1
FROM sys.assemblies
WHERE
name = N'SplitterB'
)
BEGIN
DROP ASSEMBLY SplitterB;
END;
IF EXISTS
(
SELECT 1
FROM sys.assemblies
WHERE
name = N'SplitterC'
)
BEGIN
DROP ASSEMBLY SplitterC;
END;
GO
-- ====================================
-- Create assemblies
-- ====================================
CREATE ASSEMBLY SplitterA
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300460BC14D0000000000000000E00002210B0108000012000000060000000000004E300000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000003000004B000000004000008003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000054100000002000000012000000020000000000000000000000000000200000602E7273726300000080030000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000003030000000000000480000000200050070220000900D00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133005007501000001000011027B040000040A06450300000005000000C900000053010000385501000002157D04000004027B050000046F1500000A3A3E010000027C07000004FE150300000202167D0800000402027B050000046F1600000A697D0900000402027B050000046F1700000A7D0A00000402167D0B000004388E000000027B0A000004027B0B00000493027B06000004336B027C0700000425280400000617582805000006027C07000004027B0A000004027B08000004027B0B000004027B0800000459731800000A280700000602027B070000048C030000027D0300000402177D04000004172A02157D0400000402027B0B00000417587D0800000402257B0B00000417587D0B000004027B0B000004027B090000043F61FFFFFF027C0700000425280400000617582805000006027C07000004027B0A000004027B08000004027B09000004027B0800000459731800000A280700000602027B070000048C030000027D0300000402187D04000004172A02157D04000004162A1E027B030000042A1A731A00000A7A062A1E027B030000042A3A02281B00000A02037D040000042A00000013300200170000000200001116730D0000060A06027D0500000406037D06000004062A00133002001A0000000300001102A5030000020A0312002804000006540412002806000006512A1E02281B00000A2A1E027B010000042A2202037D010000042A1E027B020000042A2202037D020000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000068040000237E0000D4040000A005000023537472696E677300000000740A000008000000235553007C0A00001000000023475549440000008C0A00000403000023426C6F6200000000000000020000015717A20B0902000000FA253300160000010000001B000000040000000B0000000D00000008000000030000001D0000001800000003000000020000000400000006000000050000000100000001000000020000000200000000000A0001000000000006004A00430006005100430006006E005B000A009B0086000A0047012C0106008F0170010600B901A7010600D001A7010600ED01A70106000C02A70106002502A70106003E02A70106005902A70106007402A70106008D0270010600A102A7010600DA02BA020600FA02BA020A0018032C01060058033D0306006603430006001A05430006003405210506004C05430006006205700106007805700106008305BA02000000000100000000000100010001001000180000000500010001000B0110002D000000090001000400030110002D0300000500030008000100E800310001000201340001007B034E02010024043100060026015A02060059015E0206005A04610206006704310006007304310006008004650206008C043100FC21000000009600A4000A0001002022000000009600AE00120003004622000000008618B6001B0006004E22000000008608BC001F0006005622000000008608C900230006005F22000000008608D600280007006722000000008608DF002C000700502000000000E10172034A020800D12100000000E109880351020800D92100000000E101D6031B000800E02100000000E10101041B000800E22100000000E1092F0451020800EA21000000008618B60023000800000001002601000002005901000001006301020002006701020003009C0100000100A10100000100A1010000010024040400060004000D00040055002900B6001B003100B6001B003900B6002C004100B6002C004900B6002C005100B6002C005900B6002C006100B6002C006900B6002C007100B6002C007900B600A3008100B6002C008900B60023009100B6001B009900B6001B00190072034A020C00CA0355021900FB031B00A9001C041B001900CA0351022100F9044A02210004056D0221000F057102B100B6007602B900B6001B00C100B6001B000900B6001B00C900B6009102D900B6001B0020007B00A8002100EB00820224000B003F002E007300E5022E001B0097022E002300A6022E002B00A6022E003300AC022E003B0097022E004300BB022E004B00A6022E005B00A6022E006B00DC024100EB00820244000B0071008000EB0082028300EB008202A000EB008202C000EB008202E000EB0082022001CB0082024001CB0082028001CB008202A001CB0082027E0287028C020300010004000300000018013700000021013B000000940469020000D204690202000400030001000500030002000600050001000700050002000900070002000C000900040010002100040012002300040014002500040016002700040018002900440204800000010000002D108B95000000000000A400000002000000000000000000000001003A000000000002000000000000000000000001007A000000000003000200040002000000003C4D6F64756C653E0053706C6974746572412E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F7264006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974746572410046696C6C526F77002E63746F72006765745F53657175656E6365007365745F53657175656E6365006765745F4974656D007365745F4974656D003C53657175656E63653E6B5F5F4261636B696E674669656C64003C4974656D3E6B5F5F4261636B696E674669656C640053657175656E6365004974656D00496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0073657175656E63650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465006974656D0076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E417474726962757465003C53706C6974746572413E645F5F300053797374656D2E436F6C6C656374696F6E732E47656E657269630049456E756D657261746F7260310049446973706F7361626C65004D6F76654E657874003C3E325F5F63757272656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E6765745F43757272656E74006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E52657365740052657365740053797374656D2E49446973706F7361626C652E446973706F736500446973706F7365003C3E315F5F73746174650053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74003C7265636F72643E355F5F31003C73746172743E355F5F32003C6C656E6774683E355F5F33003C696E7075743E355F5F34003C693E355F5F350053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E74006765745F49734E756C6C006765745F4C656E677468006765745F42756666657200537472696E670053797374656D2E446961676E6F737469637300446562756767657248696464656E417474726962757465004E6F74537570706F72746564457863657074696F6E005374727563744C61796F7574417474726962757465004C61796F75744B696E6400436F6D70696C657247656E6572617465644174747269627574650000000003200000000000A8A35E50BA24BD4B94FAC57F423D8BC20008B77A5C561934E089070002120D121103080003011C1008100E032000010320000804200101080320000E042001010E02060802060E032800080328000E31010003005408074D617853697A65FFFFFFFF54020D497346697865644C656E6774680054020A49734E756C6C61626C650031010003005408074D617853697A650100000054020D497346697865644C656E6774680154020A49734E756C6C61626C65000420010102819A010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E2573657175656E636520494E54454745522C206974656D204E5641524348415228343030302905151251011C0320000202061C0320001C0420001300030612110206030306110C03061D030328001C0320000A0420001D03072003011D0308080307010804010000000407011210040701110C0520010111690E01000953706C69747465724100000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773012830000000000000000000003E300000002000000000000000000000000000000000000000000000303000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000280300000000000000000000280334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001008B952D10000001008B952D103F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00488020000010053007400720069006E006700460069006C00650049006E0066006F00000064020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003C000A000100460069006C0065004400650073006300720069007000740069006F006E0000000000530070006C00690074007400650072004100000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003100340031002E0033003800320038003300000000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000530070006C006900740074006500720041002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310031000000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C006900740074006500720041002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D00650000000000530070006C00690074007400650072004100000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003100340031002E00330038003200380033000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003100340031002E0033003800320038003300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000503000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE ASSEMBLY SplitterB
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103003B0DC14D0000000000000000E00002210B010800000E00000006000000000000AE2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000582D000053000000004000008003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40D000000200000000E000000020000000000000000000000000000200000602E7273726300000080030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000902D0000000000004800000002000500E0210000780B00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A2026F1300000A2D0E026F1400000A0373040000062B0C168D140000011673040000068C030000022A000000133002001A0000000100001102A5040000020A031200280800000654041200280A000006512A1E02281500000A2AC202037D0100000402047D0300000402027B010000048E697D02000004027C05000004FE150400000202167D040000042A0013300500C200000002000011027B04000004027B020000043302162A027B040000040A2B56027B010000040693027B030000043342027C0500000425280800000617582809000006027C05000004027B01000004027B0400000406027B0400000459731700000A280B000006020617587D04000004172A0617580A06027B0200000432A1027C0500000425280800000617582809000006027C05000004027B01000004027B04000004027B02000004027B0400000459731700000A280B00000602027B020000047D04000004172A32027B050000048C040000022A1A731800000A7A1E027B060000042A2202037D060000042A1E027B070000042A2202037D070000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000CC030000237E0000380400008C04000023537472696E677300000000C40800000800000023555300CC080000100000002347554944000000DC0800009C02000023426C6F6200000000000000020000015717A2030902000000FA253300160000010000001900000004000000070000000B00000009000000010000001900000012000000020000000200000003000000050000000300000001000000020000000200000000000A00010000000000060056004F0006005D004F0006007A0067000A00A70092000A0031021602060079025A020600A30291020600BA0291020600D70291020600F602910206000F03910206002803910206004303910206005E039102060077035A0206008B0391020600C403A4030600E403A4030A000204160206002C044F00060031045A02060047045A02060052044F00060059044F0006007104A403000000000100000000000100010001001000180000000500010001000B0110002D0000000900010004000B0110003D00000009000600080021005B012E002100610132002100680135000100720132000100780138000100D20132000100EC0152005020000000009600B0000A0001007C20000000009600BA0012000300A220000000008618C2001B000600AA20000000008318C2001F000600DC2000000000E101C80026000800AA2100000000E109F9002A000800B72100000000E10130011B000800BE21000000008308A60140000800C621000000008308B30144000800CF21000000008308C00149000900D721000000008308C9014D000900000001001002000002004302000001004D02020002005102020003008602000001001002000002004302000001008B02000001008B0203000D001900F0002600190024012A00190055011B002900C2001B003100C2001B003900C2004D004100C2004D004900C2004D005100C2004D005900C2004D006100C2004D006900C2004D007100C2004D007900C20070008100C2004D008900C20044009100C2001B009900C2001B00210017042600210022040D020900C2001B00A900C2001702B900C2001D02C100C2001B00C900C2001B00200093007500240023005D002E0033002E022E0043003D022E008B007C022E004B0043022E0053002E022E0073003D022E003B003D022E00830073022E005B0052022E0063003D02C100CB002902E100CB0029020001CB0029022001CB0029024001CB0029026001CB00290212022502030001000400020000007F013C0000000202550000000B02590002000600030001000900050002000800050002000A00070001000B00070003000A00030003000C00050003000E00070004800000010000002D108596000000000000B000000002000000000000000000000001004600000000000200000000000000000000000100860000000000030002000400020000000000003C4D6F64756C653E0053706C6974746572422E646C6C0055736572446566696E656446756E6374696F6E730053706C6974456E756D657261746F720053706C6974526F77006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974746572420046696C6C526F77002E63746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E4D6F76654E657874004D6F76654E6578740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E526573657400526573657400696E707574006C656E6774680064656C696D69746572007374617274007265636F72640053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E74006765745F53657175656E6365007365745F53657175656E6365006765745F4974656D007365745F4974656D003C53657175656E63653E6B5F5F4261636B696E674669656C64003C4974656D3E6B5F5F4261636B696E674669656C640053657175656E6365004974656D00496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0073657175656E63650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465006974656D0076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E67004E6F74496D706C656D656E746564457863657074696F6E00436F6D70696C657247656E657261746564417474726962757465000003200000000000E6C339A53C7D1F4CA3A2AE4AC04EF7A60008B77A5C561934E089070002120D121103080003011C1008100E03200001062002011D0303032000020320001C03061D03020608020603030611100328001C0320000804200101080320000E042001010E02060E032800080328000E12010001005408074D617853697A65FFFFFFFF04200101028196010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E2173657175656E636520494E542C206974656D204E564152434841522834303030290420001D030407011110052001011159072003011D0308080307010804010000000E01000953706C69747465724200000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100802D000000000000000000009E2D0000002000000000000000000000000000000000000000000000902D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000280300000000000000000000280334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE000001000000010085962D100000010085962D103F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00488020000010053007400720069006E006700460069006C00650049006E0066006F00000064020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003C000A000100460069006C0065004400650073006300720069007000740069006F006E0000000000530070006C00690074007400650072004200000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003100340031002E0033003800350033003300000000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000530070006C006900740074006500720042002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310031000000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C006900740074006500720042002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D00650000000000530070006C00690074007400650072004200000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003100340031002E00330038003500330033000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003100340031002E0033003800350033003300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE ASSEMBLY SplitterC
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300630DC14D0000000000000000E00002210B010800001400000006000000000000BE3300000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000006433000057000000004000008003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C4130000002000000014000000020000000000000000000000000000200000602E7273726300000080030000004000000004000000160000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001A00000000000000000000000000004000004200000000000000000000000000000000A0330000000000004800000002000500082300005C10000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001E02281000000A2A13300400220000000100001103027B050000042E07027B040000042A02257B040000041758250A7D04000004062A3A02032C03172B0116280100002B2A0000001B3005004E01000002000011027B070000040B07450300000005000000280100000E010000382301000002157D070000040273090000067D0D000004027B0D000004027B090000047D05000004027B080000046F1800000A3AF0000000027C0A000004FE1503000002027B0D000004177D0400000402027B080000046F1900000A027B0D000004FE060A000006731A00000A280200002B7E010000042D1114FE0604000006731C00000A80010000047E01000004280300002B7D0B00000402027B0B0000046F1E00000A7D0E00000402177D070000042B6202027B0E0000046F1F00000A7D0C000004027C0A00000425280500000617582806000006027C0A000004027B0C000004280400002B732100000A280800000602027B0A0000048C030000027D0600000402187D07000004170ADE2502177D07000004027B0E0000046F1300000A2D91022811000006160ADE0702280E000006DC062A0000411C00000400000000000000450100004501000007000000000000001E027B060000042A1A732300000A7A001B3002002200000001000011027B070000040A061759450200000001000000010000002ADE07022811000006DC2A00000110000002001800021A0007000000001E027B060000042A3A02281000000A02037D070000042A6E02157D07000004027B0E0000042C0B027B0E0000046F1600000A2A001330020017000000030000111673100000060A06027D0800000406037D09000004062A00133002001A0000000400001102A5030000020A0312002805000006540412002807000006512A1E02281000000A2A1E027B020000042A2202037D020000042A1E027B030000042A2202037D030000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000064050000237E0000D00500008406000023537472696E677300000000540C000008000000235553005C0C00001000000023475549440000006C0C0000F003000023426C6F6200000000000000020000015717A20B090A000000FA2533001600000100000020000000050000000E000000110000000B00000003000000240000001B0000000400000002000000040000000600000005000000050000000100000003000000030000000400000000000A0001000000000006004A00430006005100430006006E005B000A009B0086000A0047012C0106008F0170010600B901A7010600D001A7010600ED01A70106000C02A70106002502A70106003E02A70106005902A70106007402A70106008D0270010600A102A7010600DA02BA020600FA02BA020A0018032C010600730358030E0099038D030E00BB0343000600E903BA020E0004048D030600240458030600320443000E00EE05430006001A06430006003406210606004C064300060062067001060078067001000000000100000000000100010001001000180000000500010001000B0110002D000000090002000500030110002D0300000500040009000301100014040000050006000B001100C2035B020100E80031000100020134000600400331000600590144020100470495020100F004310006002601A10206005901440206002605A50206003305A90206004005B40206004B05BB0206005B05BF029422000000009600A4000A000100B822000000009600AE0012000300DE22000000008618B6001B0006008620000000009100A5034C020600E622000000008608BC001F000800EE22000000008608C90023000800F722000000008608D60028000900FF22000000008608DF002C0009005020000000008618B6001B000A005820000000008600460347020A00982000000000E1013E0491020B00102200000000E109540498020B00182200000000E101A2041B000B00202200000000E101CD041B000B00602200000000E109FB0498020B006822000000008618B60023000B00772200000000810066051B000C00000001002601000002005901000001006301020002006701020003009C0100000100560300000200B50300000100A10100000100A10100000100560300000100F0040500060005000D00050069002900B6001B003100B6001B003900B6002C004100B6002C004900B6002C005100B6002C005900B6002C006100B6002C006900B6002C007100B6002C007900B600A3008100B6002C008900B60023009100B6001B009900B6001B000900B6001B00B900B6001B00C1000F04760219003E0491020C0096049C021900C7041B00D100E8041B001900960498022100D90591022100E405CE021400B600DA02C100F505E0021C00B600DA02C100FD0514032400040645032C0096049C02C10012065803E100B6006503E900B6001B00F100B6001B00F900B6007A0320007B00A80021008B006D0224000B003F002E007300CF032E001B0081032E00230090032E002B0090032E00330096032E003B0081032E004300A5032E004B0090032E005B0090032E006B00C60341008B006D0244000B00710061008B006D0280008B006D0283008B006D02A0008B006D02A3008B006D02C0008B006D02E0008B006D0200018B006D02800113016D02A00113016D02E00113016D02000213016D0272026B03700375030300010005000300000018013700000021013B0000007405CA020000B205CA0202000500030001000600030002000700050001000800050002000C00070002000F00090005001600270005001800290005001A002B0005001C002D0005001E002F008B02D30203033B034E0304800000010000002D109996000000000000A400000002000000000000000000000001003A000000000002000000000000000000000001007A00000000000300050000000000000000000100810300000000030002000400020005000200250087023700FE023B002D034100870200000000003C4D6F64756C653E0053706C6974746572432E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F7264006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974746572430046696C6C526F77002E63746F72006765745F53657175656E6365007365745F53657175656E6365006765745F4974656D007365745F4974656D003C53657175656E63653E6B5F5F4261636B696E674669656C64003C4974656D3E6B5F5F4261636B696E674669656C640053657175656E6365004974656D00496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0073657175656E63650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465006974656D0076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E417474726962757465003C3E635F5F446973706C6179436C617373330067726F7570003C53706C6974746572433E625F5F3000630053797374656D2E436F6C6C656374696F6E732E47656E657269630049456E756D657261626C6560310053797374656D2E436F72650053797374656D2E4C696E71004947726F7570696E676032003C53706C6974746572433E625F5F3100696E6465780046756E636033004353243C3E395F5F436163686564416E6F6E796D6F75734D6574686F6444656C65676174653200436F6D70696C657247656E65726174656441747472696275746500456E756D657261626C6500536B6970003C53706C6974746572433E645F5F350049456E756D657261746F7260310049446973706F7361626C65004D6F76654E657874003C3E325F5F63757272656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E6765745F43757272656E74006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E52657365740052657365740053797374656D2E49446973706F7361626C652E446973706F736500446973706F7365003C3E315F5F73746174650053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74003C7265636F72643E355F5F36003C67726F7570733E355F5F37003C6974656D3E355F5F38004353243C3E385F5F6C6F63616C7334003C3E375F5F7772617039003C3E6D5F5F46696E616C6C79610053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E74006765745F49734E756C6C006765745F56616C75650046756E6360320047726F757042790053656C65637400476574456E756D657261746F7200546F417272617900537472696E670053797374656D2E446961676E6F737469637300446562756767657248696464656E417474726962757465004E6F74537570706F72746564457863657074696F6E005374727563744C61796F7574417474726962757465004C61796F75744B696E64000000032000000000008D2FC5028929D240853BD2CFF0FFF2FE0008B77A5C561934E089070002120D121103080003011C1008100E032000010320000804200101080320000E042001010E02060802060E032800080328000E31010003005408074D617853697A65FFFFFFFF54020D497346697865644C656E6774680054020A49734E756C6C61626C650031010003005408074D617853697A650100000054020D497346697865644C656E6774680154020A49734E756C6C61626C65000420010102819A01000600540E1146696C6C526F774D6574686F644E616D650746696C6C526F775455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E0F5461626C65446566696E6974696F6E2573657175656E636520494E54454745522C206974656D204E5641524348415228343030302902060304200108030E000215125101031512550208030811061512590315125502080308151251010304010000000307010810100102151251011E00151251011E0008030A010305151265011C0320000202061C0320001C0420001300030612110306110C0A0615125101151251010306061512510103030612100A061512650115125101030328001C0420001D030615126D020308052002011C181D10020215125101151255021E011E00151251011E0015126D021E001E01040A020308101512590315125502080308151251010318100202151251011E01151251011E00151259031E00081E010D0A02151255020803151251010309151251011512510103082000151265011300091512650115125101030C1001011D1E00151251011E00052001011D0304070202080407011214040701110C062001011180810E01000953706C69747465724300000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100008C3300000000000000000000AE330000002000000000000000000000000000000000000000000000A03300000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000280300000000000000000000280334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE000001000000010099962D100000010099962D103F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00488020000010053007400720069006E006700460069006C00650049006E0066006F00000064020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003C000A000100460069006C0065004400650073006300720069007000740069006F006E0000000000530070006C00690074007400650072004300000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003100340031002E0033003800350035003300000000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000530070006C006900740074006500720043002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310031000000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C006900740074006500720043002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D00650000000000530070006C00690074007400650072004300000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003100340031002E00330038003500350033000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003100340031002E0033003800350035003300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000C03300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
-- ====================================
-- Create the functions
-- ====================================
CREATE FUNCTION dbo.SplitterA
(
@Input NVARCHAR(MAX),
@Delimiter NCHAR(1)
)
RETURNS TABLE
(
sequence INTEGER NULL,
item NVARCHAR(4000) NULL
)
AS EXTERNAL NAME SplitterA.UserDefinedFunctions.SplitterA;
GO
CREATE FUNCTION dbo.SplitterB
(
@Input NVARCHAR(MAX),
@Delimiter NCHAR(1)
)
RETURNS TABLE
(
sequence INTEGER NULL,
item NVARCHAR(4000) NULL
)
AS EXTERNAL NAME SplitterB.UserDefinedFunctions.SplitterB;
GO
CREATE FUNCTION dbo.SplitterC
(
@Input NVARCHAR(MAX),
@Delimiter NCHAR(1)
)
RETURNS TABLE
(
sequence INTEGER NULL,
item NVARCHAR(4000) NULL
)
AS EXTERNAL NAME SplitterC.UserDefinedFunctions.SplitterC;
GO
-- ====================================
-- Enable CLR if necessary
-- ====================================
IF NOT EXISTS
(
SELECT 1
FROM sys.configurations
WHERE
name = N'clr enabled'
AND value_in_use = 1
)
BEGIN
EXECUTE sys.sp_configure
@configname = N'clr enabled',
@configvalue = 1;
RECONFIGURE;
END;
GO
-- ====================================
-- Test the functions
-- ====================================
SELECT
sequence,
item
FROM dbo.SplitterA(N'A,B,C', N',');
GO
SELECT
sequence,
item
FROM dbo.SplitterB(N'A,B,C', N',');
GO
SELECT
sequence,
item
FROM dbo.SplitterC(N'A,B,C', N',');
GO
Source code for Splitter A:
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// A utility structure used to pass rows around
/// </summary>
private struct OutputRecord
{
public int Sequence { get; set; }
public string Item { get; set; }
}
/// <summary>
/// Returns an enumeration of OutputRecord strcutures.
/// Each object returned by the enumerator is passed by
/// SQL Server to the FillRow method.
/// </summary>
/// <param name="Input">The string to split</param>
/// <param name="Delimiter">The character to split at</param>
/// <returns>An enumeration of OutputRecord structures</returns>
[SqlFunction
(
DataAccess = DataAccessKind.None, // No user data access by this function
SystemDataAccess = SystemDataAccessKind.None, // No system data access by this function
IsDeterministic = true, // This function is deterministic
IsPrecise = true, // This function is precise
FillRowMethodName = "FillRow", // The method called by SQL Server to obtain column values
TableDefinition = "sequence INTEGER, item NVARCHAR(4000)"
)
]
public static IEnumerator SplitterA
(
[SqlFacet(MaxSize = -1, IsFixedLength = false, IsNullable = false)] SqlChars Input,
[SqlFacet(MaxSize = +1, IsFixedLength = true, IsNullable = false)] char Delimiter
)
{
// Don't do anything if the input string is NULL
if (Input.IsNull) { yield break; }
// Create the OutputRecord structure once here and reuse it for each element later
var record = new OutputRecord();
// Start position of the current split element
var start = 0;
// The length of the string to split
var length = (int)Input.Length;
// The array of characters contained in the SqlChars input parameter
var input = Input.Buffer;
// Iterate through the characters in the input string
for (int i = 0; i < length; i++)
{
// Found a delimiter?
if (input == Delimiter)
{
// Increment the OutputRecord sequence number
record.Sequence++;
// Break out the string element
record.Item = new string(input, start, i - start);
// Return the structure
yield return record;
// Set the start point of the next element
start = i + 1;
}
}
// Process the last element
record.Sequence++;
record.Item = new string(input, start, length - start);
yield return record;
}
/// <summary>
/// Called by SQL Server for each row
/// </summary>
/// <param name="obj">An OutputRecord structure</param>
/// <param name="sequence">Out: sequence number of this split element</param>
/// <param name="item">Out: The split element</param>
public static void FillRow(object obj, out int sequence, out string item)
{
// Unpack the record and return it to SQL Server using this method's output parameters
OutputRecord r = (OutputRecord)obj;
sequence = r.Sequence;
item = r.Item;
}
};
Source code for Splitter B:
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/**
* How SQL Server SQLCLR table-valued functions work:
*
* 1. SQL Server passes the input parameters in to the function and receives an enumeration object in return
* 2. SQL Server calls the MoveNext() method on the enumeration object
* 3. If the MoveNext() call returns true, SQL Server calls the Current() method to get an object for the current row
* 4. SQL Server calls the FillRow method to obtain column values for the current row
* 5. Repeat from step 2, until MoveNext() returns false
*
* */
[SqlFunction
(
DataAccess = DataAccessKind.None, // No user data access by this function
SystemDataAccess = SystemDataAccessKind.None, // No system data access by this function
IsDeterministic = true, // This function is deterministic
IsPrecise = true, // This function is precise
FillRowMethodName = "FillRow", // The method called by SQL Server to obtain the next row
TableDefinition =
"sequence INT, item NVARCHAR(4000)" // Returned table definition
)
]
// 1. SQL Server passes input parameters and receives an enumration object
public static IEnumerator SplitterB
(
[SqlFacet(MaxSize = -1)] SqlChars Input,
char Delimiter
)
{
return Input.IsNull ?
new SplitEnumerator(new char[0], char.MinValue) :
new SplitEnumerator(Input.Value, Delimiter);
}
// The enumeration object
struct SplitEnumerator : IEnumerator
{
// Constructor (called once when the object is created)
internal SplitEnumerator(char[] Input, char Delimiter)
{
// Save references
input = Input;
delimiter = Delimiter;
// Remember the length of the character array
length = input.Length;
// Structure holding split rows
record = new SplitRow();
// Starting at the first character
start = 0;
}
// Enumerator implementation
#region IEnumerator Methods
// 2. SQL Server calls the MoveNext() method on the enumeration object
bool IEnumerator.MoveNext()
{
// No more rows?
if (start == length) { return false; }
// Find the next delimiter
for (int i = start; i < length; i++)
{
if (input == delimiter)
{
// Increment the sequence number
record.Sequence++;
// Save the split element
record.Item = new string(input, start, i - start);
// Set the next element search start point
start = i + 1;
return true;
}
}
// Last item
record.Sequence++;
record.Item = new string(input, start, length - start);
start = length;
return true;
}
// 3. SQL Server calls the Current() method to get an object for the current row
// (We pack the current row data in an OutputRecord structure)
object IEnumerator.Current
{
get { return record; }
}
// Required by the IEnumerator interface, but not needed for this implementation
void IEnumerator.Reset()
{
throw new System.NotImplementedException();
}
#endregion
readonly char[] input; // Reference to the string to be split
readonly int length; // Length of the input string
readonly char delimiter; // The delimiter character
int start; // Current search start position
SplitRow record; // Each row to be returned
}
// 4. SQL Server calls the FillRow method to obtain column values for the current row
public static void FillRow(object obj, out int sequence, out string item)
{
// The passed-in object is an OutputRecord
var r = (SplitRow)obj;
// Set the output parameter values
sequence = r.Sequence;
item = r.Item;
}
// Structure used to hold each row
struct SplitRow
{
internal int Sequence { get; set; } // Sequence of the element
internal string Item { get; set; } // The element
}
};
Source code for Splitter C:
using System.Collections;
using System.Data.SqlTypes;
using System.Linq;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// A utility structure used to pass split elements around
/// </summary>
private struct OutputRecord
{
/// <summary>
/// Sequence number of the split row
/// </summary>
public int Sequence { get; set; }
/// <summary>
/// The split string element
/// </summary>
public string Item { get; set; }
}
/// <summary>
/// Splits a string and returns a row for each split.
/// SQL Server calls this method to obtain an enumerator.
/// Each object returned by the enumerator is then passed
/// to the FillRow method to obtain values for each row.
/// </summary>
/// <param name="Input">The string to split</param>
/// <param name="Delimiter">The character to split at</param>
/// <returns>An enumeration of OutputRecord structures</returns>
[SqlFunction
(
FillRowMethodName = "FillRow",
DataAccess = DataAccessKind.None, // No user data access by this function
SystemDataAccess = SystemDataAccessKind.None, // No system data access by this function
IsDeterministic = true, // Is deterministic
IsPrecise = true, // Is precise
TableDefinition = "sequence INTEGER, item NVARCHAR(4000)"
)
]
public static IEnumerator SplitterC
(
[SqlFacet(MaxSize = -1, IsFixedLength = false, IsNullable = false)] SqlChars Input,
[SqlFacet(MaxSize = +1, IsFixedLength = true, IsNullable = false)] char Delimiter
)
{
// Don't do anything if the input string is NULL
if (Input.IsNull) { yield break; }
// The enumerator returns an OutputRecord structure
// Create it once here and reuse it for each element later
var record = new OutputRecord();
// This is the first group
var group = 1;
// Lazily iterate through the characters in the input, incrementing
// the group number when a delimiter character is encountered.
// After the first group, skip the preceding delimiter.
var groups = Input.Value
.GroupBy(c => c == Delimiter ? ++group : group)
.Select((c, index) => c.Skip(index == 0 ? 0 : 1));
// Each 'group' is IEnumerable<char>
// Iterate through each group, populating an OutputRecord
// structure with the sequence number and split string
foreach (var item in groups)
{
// Increment the sequence
record.Sequence++;
// Store the split element
record.Item = new string(item.ToArray());
// Return the OutputRecord structure
yield return record;
}
}
/// <summary>
/// Called by SQL Server for each row to be streamed from the function
/// </summary>
/// <param name="obj">An OutputRecord structure</param>
/// <param name="sequence">Out: Sequence number of this split element</param>
/// <param name="item">Out: The split element</param>
public static void FillRow(object obj, out int sequence, out string item)
{
// Unpack the record and return it to SQL Server using this method's output parameters
OutputRecord r = (OutputRecord)obj;
sequence = r.Sequence;
item = r.Item;
}
};
Paul White
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 4, 2011 at 5:37 am
SQLkiwi
Is there a reason you didn't use the Split() method for C# strings?
http://msdn.microsoft.com/en-us/library/ms228388(v=vs.80).aspx
May 4, 2011 at 5:47 am
mark hutchinson (5/4/2011)
SQLkiwiIs there a reason you didn't use the Split() method for C# strings?
http://msdn.microsoft.com/en-us/library/ms228388(v=vs.80).aspx
:w00t: > love this question.
can't wait to hear the answer !
May 4, 2011 at 5:57 am
mark hutchinson (5/4/2011)
SQLkiwiIs there a reason you didn't use the Split() method for C# strings?
http://msdn.microsoft.com/en-us/library/ms228388(v=vs.80).aspx
Yes: memory usage, and speed.
Split is relatively slow because it creates a whole bunch of new string objects (strings are immutable).
It consumes a lot of memory because the entire input (which may be large, given that this function handles NVARCHAR(MAX) input) is required all at once - and then it makes a copy (array of split strings).
The functions presented are written to stream the input data in only as required and to stream output, again as required, back to the caller. I think Adam discusses the whole issue of memory usage and the benefits of streaming CLR TVFs in the SQLblog article I linked to.
Hope that makes sense, and answers your question.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 4, 2011 at 5:58 am
Ninja's
Glad you liked it.
If any C# readers are looking for a versatile Split() function, look at the one in the Microsoft.VisualBasic namespace.
http://msdn.microsoft.com/en-us/library/6x627e5f(v=vs.80).aspx
Viewing 15 posts - 121 through 135 (of 990 total)
You must be logged in to reply to this topic. Login to reply