Tally OH! An Improved SQL 8K “CSV Splitter” Function

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • gary.rumble (5/3/2011)


    It is thus:

    Outstanding. Time for some more tests at the Moden house. Thanks, Gary.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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!

  • 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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • 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.

  • 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:

    http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

    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

  • 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

  • mark hutchinson (5/4/2011)


    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

    :w00t: > love this question.

    can't wait to hear the answer !

  • mark hutchinson (5/4/2011)


    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

    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

  • 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