mburbea/Create_funcs.sql

## Create_funcs.sql
/* This script is just the creation of the objects. Useful if you just want to add the functions
 * to an existing database.
 * You don't need to take all of them.
 * Hybrid is depedent on HybridInternal
 * HybridNV depends on HybridNVInternal
 * SplitVarchar,SplitVarcharA depend on SplitVarbinary
 * SplitNVarchar depends on SplitNVarcharInternal
 * the baseline functions are for diagnostic purposes only. They are not splitters!
 * They return 1 less then their third parameter number of chunks of the first parameter.
*/
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.CLRNVBaseline')
	)
	drop function dbo.CLRNVBaseline
GO
IF  EXISTS
    (
    SELECT 1
    FROM sys.objects
    WHERE
        [object_id] = OBJECT_ID(N'dbo.SplitNvarchar')
    )
    DROP FUNCTION dbo.splitNVarchar
GO
IF  EXISTS
    (
    SELECT 1
    FROM sys.objects
    WHERE
        [object_id] = OBJECT_ID(N'dbo.SplitNvarcharInternal')
    )
    DROP FUNCTION dbo.splitNVarcharInternal
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.hybrid')
	)
	drop function dbo.hybrid
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.hybridinternal')
	)
	drop function dbo.hybridinternal
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.SplitVarchar')
	)
	drop function dbo.SplitVarchar
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.CLRBaseLine')
	)
	drop function dbo.CLRBaseLine
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.SplitVarcharA')
	)
	drop function dbo.SplitVarcharA
GO

if exists(
	select 1 from sys.objects where object_id = object_id(N'dbo.splitVarbinary')
)
Drop function dbo.splitVarbinary;
Go
if exists (select 1 from sys.objects where object_id = object_id(N'HybridNV'))
drop function dbo.hybridNV
GO
if exists (select 1 from sys.objects where object_id = object_id(N'HybridNVInternal'))
drop function dbo.hybridNVInternal
GO

IF  EXISTS
    (
    SELECT 1
    FROM sys.assemblies
    WHERE
        name = N'SplitTest'
    )
    DROP ASSEMBLY SplitTest;
GO


CREATE ASSEMBLY [SplitTest] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008D9B84530000000000000000E00022200B0130000022000000060000000000000E41000000200000006000000000001000200000000200000400000000000000040000000000000000A000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000BA4000004F000000006000007803000000000000000000000000000000000000008000000C0000001C4000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000014210000002000000022000000020000000000000000000000000000200000602E7273726300000078030000006000000004000000240000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000002800000000000000000000000000004000004200000000000000000000000000000000EE400000000000004800000002000500542A0000C81500000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002004200000000000000026F1200000A2C0C168D06000002281300000A2A042D0D026F1400000A03730D0000062A0417330D026F1400000A0373110000062A026F1400000A0473090000062A00001330020026000000010000110274060000020A03067B10000004281600000A811600000104067B11000004731800000A512A0000133002003900000002000011026F1900000A2D220418320F026F1A00000A0473160000060A062A026F1A00000A03731A0000060A062A168D1D00000116731A0000060A062A000000133002001D000000030000110274090000020A03066F1E0000065404066F20000006731B00000A512A86026F1900000A2C0C168D0A000002281300000A2A026F1A00000A0373240000062A86026F1200000A2C0C168D0A000002281300000A2A026F1400000A0373280000062A00000013300200200000000400001102740A0000020A03067B1D0000045404067B1E0000045405067B1F000004542A1E02281500000A2AB60273150000067D0500000402281500000A02037D01000004020417597D0200000402038E69045B7D040000042A1E027B050000042A0000133005007900000000000000027B020000042D02162A027B05000004257B1000000417587D10000004027B05000004027B040000048D1C0000017D11000004027B01000004027B03000004027B050000047B1100000416027B04000004281700000A02027B03000004027B04000004587D0300000402027B0200000417597D02000004172A1A731C00000A7A820273150000067D0800000402281500000A02037D0600000402047D070000042A00000013300600D400000005000011027B09000004153302162A027B08000004257B1000000417587D10000004027B090000040A2B5C027B060000040691027B070000043348027B0800000406027B09000004598D1C0000017D11000004027B06000004027B09000004027B080000047B110000041606027B0900000459281700000A020617587D09000004172A0617580A06027B060000048E693299027B0800000406027B09000004598D1C0000017D11000004027B06000004027B09000004027B080000047B110000041606027B0900000459281700000A02157D09000004172A1E027B080000042A1330050068000000060000110273150000067D0D00000402281500000A02037D0A00000402038E697D0F00000402027B0F0000041D581E5B8D1A0000017D0B0000040316027B0B00000416027B0F000004281700000A046E1E62046E580A061F106206580A06251F2062600A02067D0C0000042A13300600A601000007000011027B0E000004027B0F0000043202162A027B0E0000040A027B0B0000040B027B0C0000040C027B0D0000040D09257B1000000417587D100000042B5E06027B0F0000042E13027B0A00000406916E0820FF0000006A5F333E0906027B0E000004598D1C0000017D11000004027B0A000004027B0E000004097B110000041606027B0E00000459281700000A020617587D0E000004172A0617580A061D5F2D9D061E5B130438AB0000000711049613051105086113051105210101010101010101591105665F130611062180808080808080805F2C7911041E5A0A110620808080806E5F2D0B061A580A11061F20641306110620808000006A5F2D0B0618580A11061F10641306110620800000006A5F2D040617580A0906027B0E000004598D1C0000017D1100000407027B0E000004097B110000041606027B0E00000459281700000A020617587D0E000004172A1104175813041104078E693F4BFFFFFF09027B0F000004027B0E000004598D1C0000017D1100000407027B0E000004097B1100000416027B0F000004027B0E00000459281700000A02027B0A0000048E697D0E000004172A1E027B0D0000042ABE0273220000067D1600000402281500000A02037D12000004020417597D130000040218038E69045B5A7D150000042A1E027B160000042A0000133005007D00000005000011027B130000042D02162A027B16000004256F1E0000060A0617586F1F000006027B16000004027B15000004185B8D1D0000016F21000006027B12000004027B14000004027B160000046F2000000616027B15000004281700000A02027B14000004027B15000004587D1400000402027B1300000417597D13000004172A820273220000067D1A00000402281500000A02037D1700000402047D180000042A000013300600DC00000008000011027B19000004153302162A027B1A000004256F1E0000060B0717586F1F000006027B190000040A2B60027B170000040693027B18000004334C027B1A00000406027B19000004598D1D0000016F21000006027B17000004027B19000004185A027B1A0000046F200000061606027B1900000459185A281700000A020617587D19000004172A0617580A06027B170000048E693295027B1A00000406027B19000004598D1D0000016F21000006027B17000004027B19000004027B1A0000046F200000061606027B1900000459185A281700000A02157D19000004172A1E027B1A0000042A1E027B1B0000042A2202037D1B0000042A1E027B1C0000042A2202037D1C0000042A820273230000067D2200000402281500000A02037D2000000402047D210000042A00133003009300000005000011027B23000004153302162A027B22000004257B1D00000417587D1D000004027B22000004027B2300000417587D1E000004027B230000040A2B32027B200000040693027B21000004331E027B2200000406027B23000004597D1F000004020617587D23000004172A0617580A06027B200000048E6932C3027B2200000406027B23000004597D1F00000402157D23000004172A1E027B220000042A001330050068000000060000110273230000067D2700000402281500000A02037D2400000402038E697D2900000402027B290000041D581E5B8D1A0000017D250000040316027B2500000416027B29000004281700000A046E1E62046E580A061F106206580A06251F2062600A02067D260000042A133003004A01000009000011027B28000004027B290000043202162A027B280000040A027B250000040B027B260000040C027B270000040D09257B1D00000417587D1D00000409027B2800000417587D1E0000042B3906027B290000042E13027B2400000406916E0820FF0000006A5F33190906027B28000004597D1F000004020617587D28000004172A0617580A061D5F2DC2061E5B1304388B0000000711049613051105086113051105210101010101010101591105665F130611062180808080808080805F2C5911041E5A0A110620808080806E5F2D0B061A580A11061F20641306110620808000006A5F2D0B0618580A11061F10641306110620800000006A5F2D040617580A0906027B28000004597D1F000004020617587D28000004172A1104175813041104078E693F6BFFFFFF09027B29000004027B28000004597D1F00000402027B240000048E697D28000004172A1E027B270000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000A0080000237E00000C0900009405000023537472696E677300000000A00E00000800000023555300A80E0000100000002347554944000000B80E00001007000023426C6F6200000000000000020000015717A20B0902000000FA013300160000010000001F0000000C000000290000002B00000024000000070000001C0000001E0000000900000008000000090000000B000000050000000100000001000000020000000A0000000000AF0201000000000006008E0127040600500227040600DA0014040F00470400000600EE00F10206007101F10206003D01F10206003702F1020600D401F1020600FF01F10206000501F10206002001F1020600AE01830506001A0283050600CC04DF020A005C015E030A007E005E030A0078005E030600790394040A00ED015E030A00750456040A00010056040A00BC0456040600BF00270406005B05DF0206000A00DF0206003903DF0206006E02DF0206001B03DF0206000400DF0206000303DF02000000001100000000000100010001001000A70400003D000100010003001000C50300003D000100090003001000DB0300003D0006000D00030010001A0000003D000A001100030010008D0000003D001000150003001000A90300003D001200160003001000FE0300003D0017001A0003001000520500003D001B001E0003001000090500003D001D00230003001000850300003D002000240003001000980300003D002400280021007E048A0001008C048E00010024058E00210083028E0021009400910021007F048A002100E7029500010095009100010025058E0021007E048A00210085049800210040039C00210094009100010024058E00210083028E00030045008E000300DA028A0021007E049F0001008C048E00010024058E00210083028E0021009400A30021003B059F0021005403A700010025058E0001009500A300010048008E00010062009F00060045008E00060025058E000600ED028E002100C5049F002100E602A70021001005AA00010024058E0021007E048A00210085049800210040039C0021001005AA00010024058E00210083028E0050200000000096007405AE000100A0200000000096006B04B7000400D4200000000096002003C20007001C210000000096004A05CB000A0045210000000096008F02D5000D006721000000009600A002DD000F008C210000000096000405E5001100B8210000000086180E0406001500C0210000000083180E04F0001500EE2100000000E6091805F7001700F82100000000E6014105150017007D2200000000E601F2040600170084220000000083180E04FB001700A82200000000E6014105150019007D2200000000E101D30406001900882300000000E6091805F700190090230000000083180E04FB001900042400000000E601410515001B007D2200000000E101D30406001B00B62500000000E6091805F7001B00B8210000000086180E0406001B00BE250000000083180E0402011B00EE2500000000E6091805F7001D00F82500000000E601410515001D007D2200000000E601F20406001D0081260000000083180E0409011D00A42600000000E601410515001F008C2700000000E6091805F7001F007D2200000000E101D30406001F0094270000000083089C0010011F009C27000000008308A90001001F00A527000000008308C8023A002000AD27000000008308D1023F002000B8210000000086180E0406002100B8210000000086180E0406002100B6270000000083180E0409012100D82700000000E6014105150023007D2200000000E101D30406002300772800000000E6091805F700230080280000000083180E04FB002300F42800000000E6014105150025007D2200000000E101D304060025004A2A00000000E6091805F7002500000001007F04000002005403000003006105000001008B0202000200B60002000300DA02000001003505000002004A03000003006105000001008B0202000200B60002000300DA0200000100C604000002005403000001007F04000002005403000001008B02020002002E0302000300250502000400ED02000001007F04000002008D04000001007F04000002005403000001007F04000002005403000001007F04000002008D04000001003505000002004A03000001007D02000001007D0200000100C604000002005403000001007F0400000200540303004D0004004D0005004D0007004D0008004D000B004D000C004D0009000E04010011000E04060019000E040A0029000E04100031000E04100039000E04100041000E04100049000E04100051000E04100059000E04100061000E04100069000E04060071000E04060081000E040600A1000E0406009900F2040600C1000E040600A900BD021500C900F0031900A90073021E0079000E040600B100F8042300D9006A052900A9000E043400B900BD021500B90073023A00B9000E043F00F9000E04060020007300910124007B0065062E000B0021012E0013002A012E001B0049012E00230052012E002B0061012E00330061012E003B0061012E00430052012E004B0067012E00530061012E005B007F012E0063008C012E006B008C0144007B009706600073002E03A0007300C804C0007300C804E4007B00C906A4017B00DC06C4017B009706E4017B00650604027B00970661038B008C0181038B008C01C0038B008C01E0038B008C0100048B008C0120048B008C0148004D00520057005C0060006400700075000300010004000200050003000700040008000500090006000B0008000C00090000001C05140100001C05140100001C05140100001C05140100001C0514010000AD0018010000D5021C0100001C05140100001C05140102000A00030002001000050002001400070002001700090002001C000B0002001E000D0001001F000D00020020000F00010021000F0002002700110002002B00130004001E00210005002600210008003A0021000B004C0021000C005400210045000480000001000000000000000000000000002B05000002000000000000000000000081003C00000000000200000000000000000000008100300000000000030002000400020005000200060002000700020008000200090002000A0002000B0002000C000200000000000053716C496E7433320055496E743634003C4D6F64756C653E0053706C69744279746573456E756D657261746F72410053797374656D2E44617461006D73636F726C6962006964003C53657175656E63653E6B5F5F4261636B696E674669656C64003C4974656D3E6B5F5F4261636B696E674669656C640053797374656D446174614163636573734B696E64005265636F7264005F7265636F7264006765745F53657175656E6365007365745F53657175656E63650073657175656E636500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500416C6C6F775061727469616C6C795472757374656443616C6C65727341747472696275746500417373656D626C7950726F647563744174747269627574650053716C466163657441747472696275746500417373656D626C79436F707972696768744174747269627574650053656375726974795472616E73706172656E7441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650042797465006765745F56616C75650076616C7565005F6C656E677468006F626A004879627269644E56496E7465726E616C00487962726964496E7465726E616C0053706C6974546573742E646C6C006765745F49734E756C6C006765745F4974656D007365745F4974656D006974656D0053797374656D005F64656C696D006E756D0053797374656D2E5265666C656374696F6E004E6F74496D706C656D656E746564457863657074696F6E00436861720053706C69744E56617263686172006974656D4E756D62657200427566666572005F636F6D70617265720044656C696D697465720064656C696D69746572004D6963726F736F66742E53716C5365727665722E5365727665720049456E756D657261746F72004879627269644E56456E756D657261746F7200487962726964456E756D657261746F7200434C52537472696E67426173654C696E65456E756D657261746F7200434C52426173656C696E65456E756D657261746F720053706C69744279746573456E756D657261746F7200476574456E756D657261746F720053706C6974456E756D657261746F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730046696C6C42797465730053716C4279746573005F6279746573005F6C6F6E6773005F6368756E6B730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E730053716C4368617273005F6368617273004F626A6563740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E5265736574006F705F496D706C696369740046696C6C5F526573756C74005F726573756C74006765745F43757272656E74005F73746172740053706C69745465737400496E70757400696E707574004D6F76654E6578740046696C6C526F770053706C6974526F7700417272617900737472617465677900426C6F636B436F70790053706C697456617262696E6172790053797374656D2E5365637572697479000000032000000000006BCD7141C3CF7B429C562162056F25A100042001010803200001052001011111042001010E03200002042000124D0420001D050500011159080A00050112650812650808052001011D050420001D03052001011D03021D0B0407011218040701124D04070112240407011228030701080307010B0B0707081D0B0B1218080B0B04070208080B0707081D0B0B1228080B0B08B77A5C561934E08903061D050206080306121802060503061D0B02060B03061D030306122402060303061228080003124D125505080A0003011C101159101255080003124D125D0308090003011C100810125D070002124D125D03070002124D1255050A0004011C100810081008062002011D05080320001C062002011D0505062002011D0308062002011D0303032000080328001C032800080428001D030801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000000E01000953706C697454657374000005010000000017010012436F7079726967687420C2A920203230313400000C010007312E302E302E3000000401000000819B010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650946696C6C4279746573540E0F5461626C65446566696E6974696F6E246974656D4E756D62657220696E742C206974656D2076617262696E6172792838303030298198010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E236974656D4E756D62657220494E542C206974656D204E56415243484152283430303029819B010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650B46696C6C5F526573756C74540E0F5461626C65446566696E6974696F6E226974656D4E756D62657220696E742C20737461727420696E742C206E756D20696E7431010003005408074D617853697A65401F000054020D497346697865644C656E6774680054020A49734E756C6C61626C650031010003005408074D617853697A650100000054020D497346697865644C656E6774680154020A49734E756C6C61626C650012010001005408074D617853697A65FFFFFFFF31010003005408074D617853697A65FFFFFFFF54020D497346697865644C656E6774680054020A49734E756C6C61626C65000000000000008D9B84530000000002000000820000003840000038220000525344539746077012DC664A85A863558A5EAA3201000000433A5C55736572735C6D746275726265615C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C7465737450726F6A6563745C7465737450726F6A6563745C6F626A5C52656C656173655C53706C6974546573742E70646200E24000000000000000000000FC400000002000000000000000000000000000000000000000000000EE400000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C00000000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000586000001C03000000000000000000001C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0047C020000010053007400720069006E006700460069006C00650049006E0066006F0000005802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D00650000000000000000003C000A000100460069006C0065004400650073006300720069007000740069006F006E0000000000530070006C006900740054006500730074000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000530070006C006900740054006500730074002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100340000002A00010001004C006500670061006C00540072006100640065006D00610072006B007300000000000000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C006900740054006500730074002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D00650000000000530070006C006900740054006500730074000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000103100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE


GO

CREATE FUNCTION [dbo].[SplitVarbinary]
(@bytes VARBINARY (8000),
 @delimiter binary(1),
 @strat int)
RETURNS
     TABLE (
        [itemNumber] INT              NULL,
        [item]       VARBINARY (8000) NULL
		)
AS
 EXTERNAL NAME [SplitTest].[UserDefinedFunctions].[SplitVarbinary]
GO
CREATE FUNCTION [dbo].[SplitVarchar]
(
	@str varchar(8000),
	@delimiter char(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,Convert(varchar(8000),item) [item]
	from dbo.[SplitVarbinary](Convert(Varbinary(8000),@str),convert(binary(1),@delimiter),0)
;
GO
CREATE FUNCTION [dbo].[SplitVarcharA]
(
	@str varchar(8000),
	@delimiter char(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,Convert(varchar(8000),item) [item]
	from dbo.[SplitVarbinary](Convert(Varbinary(8000),@str),convert(binary(1), @delimiter ),1)
;
GO
CREATE FUNCTION [dbo].[CLRBaseline]
(
	@str varchar(8000),
	@delimiter char(1),
	@chunks int
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,Convert(varchar(8000),item) [item]
	from dbo.[SplitVarbinary](Convert(Varbinary(8000),@str),convert(binary(1),@delimiter),@chunks)
;
Go
CREATE FUNCTION [dbo].[SplitNVarcharInternal]
(@Input NVARCHAR (MAX), @Delimiter NCHAR (1),@strategy int)
RETURNS
     TABLE (
        [itemNumber] INT             NULL,
        [item]       NVARCHAR (4000) NULL)
AS
 EXTERNAL NAME [SplitTest].[UserDefinedFunctions].[SplitNVarchar]
GO
CREATE FUNCTION [dbo].[CLRNVBaseline]
(
	@str nvarchar(max),
	@delimiter nchar(1),
	@chunks int
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,[item]
	from dbo.[SplitNvarcharInternal](@str,@delimiter,@chunks)
;
Go
CREATE FUNCTION [dbo].[SplitNVarchar]
(
	@str nvarchar(max),
	@delimiter nchar(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,[item]
	from dbo.[SplitNvarcharInternal](@str,@delimiter,0)
;
Go
CREATE FUNCTION [dbo].[HybridNVInternal] (@chars [nvarchar](MAX), @delimiter [nchar](1))
RETURNS TABLE (itemNumber int, start int, num int)
AS EXTERNAL NAME [SplitTest].[UserDefinedFunctions].[HybridNVInternal];

GO
Create Function [dbo].[HybridNV](@pstring varchar(8000),@delimiter char(1))
returns table with schemabinding as
return
select itemNumber,item=substring(@pstring,start,num)
from dbo.HybridNVInternal(@pstring,@delimiter)
GO

CREATE FUNCTION [dbo].[HybridInternal] (@bytes [varbinary](8000), @delimiter [tinyint])
RETURNS TABLE (itemNumber int, start int, num int)
AS EXTERNAL NAME [SplitTest].[UserDefinedFunctions].[HybridInternal];
go

Create Function [dbo].[Hybrid](@pstring varchar(8000),@delimiter char(1))
returns table with schemabinding as
return
select itemNumber,item=substring(@pstring,start,num)
from dbo.HybridInternal(convert(varbinary(8000),@pstring),ascii(@delimiter))
GO

## hybrid.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

public partial class UserDefinedFunctions
{
	class Result
	{
		public int id;
		public int start;
		public int num;
	}
	[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 = "Fill_Result",                  // The method called by SQL Server to obtain the next row
		TableDefinition = "itemNumber int, start int, num int"         // Returned table definition
	)]
	public static IEnumerator HybridNVInternal(
		[SqlFacet(MaxSize = -1, IsFixedLength = false, IsNullable = false)]
		SqlChars chars,
		[SqlFacet(MaxSize = 1, IsFixedLength = true, IsNullable = false)]
		char delimiter)
	{
		if (chars.IsNull) return new Result[0].GetEnumerator();
		return new HybridNVEnumerator(chars.Value, delimiter);
	}

	class HybridNVEnumerator : IEnumerator
	{
		private readonly char[] _chars;
		private readonly char _delim;
		private readonly Result _result = new Result();
		private int _start;

		// Methods
		internal HybridNVEnumerator(char[] chars, char delimiter)
		{
			_chars = chars;
			_delim = delimiter;
		}
		public bool MoveNext()
		{
			if (this._start == -1) return false;
			this._result.id++;
			this._result.start = _start+1;
			int i = this._start;
			for (; i < this._chars.Length; i++)
			{
				if (this._chars[i] == _delim)
				{
					this._result.num =i - _start;
					this._start = i + 1;
					return true;
				}
			}
			this._result.num =i - _start;
			this._start = -1;
			return true;
		}
			void IEnumerator.Reset()
			{
				throw new NotImplementedException();
			}

			public object Current
			{
				get
				{
					return this._result;
				}
			}
	}
	[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 = "Fill_Result",                  // The method called by SQL Server to obtain the next row
		TableDefinition = "itemNumber int, start int, num int"         // Returned table definition
	)
	]
	public static IEnumerator HybridInternal(
		// our interest is smaller varchars.
		// anything beyond 8000 is best off using the normal splitter.
		[SqlFacet(MaxSize = 8000, IsFixedLength = false, IsNullable = false)]
		SqlBytes bytes,
		[SqlFacet(MaxSize = 1, IsFixedLength = true, IsNullable = false)]
		byte delimiter)
	{
		if (bytes.IsNull) return new Result[0].GetEnumerator();
		return new HybridEnumerator(bytes.Value, delimiter);
	}
	class HybridEnumerator : IEnumerator
	{
		private readonly byte[] _bytes;
		private readonly ulong[] _longs;
		private readonly ulong _comparer;
		private readonly Result _result = new Result();
		private int _start;
		private readonly int _length;

		// Methods
		internal HybridEnumerator(byte[] bytes, byte delimiter)
		{
			this._bytes = bytes;
			this._length = bytes.Length;
			// we do this so that we can avoid a spillover scan near the end.
			// in unsafe implementation this would be dangerous as we potentially
			// will be reading more bytes than we should.
			this._longs = new ulong[(_length + 7) / 8];
			Buffer.BlockCopy(bytes, 0, _longs, 0, _length);
			var c = (((ulong)delimiter << 8) + (ulong)delimiter);
			c = (c << 16) + c;
			// comparer is now 8 copies of the original delimiter.
			c |= (c << 32);
			this._comparer = c;
		}

		public bool MoveNext()
		{
			if (this._start >= this._length) return false;
			int i = this._start;
			var longs = this._longs;
			var comparer = this._comparer;
			var r = this._result;
			r.id++;
			r.start = _start + 1;
			// handle the case where start is not divisible by eight.
			for (; (i & 7) != 0; i++)
			{
				if (i == _length || _bytes[i] == (comparer & 0xFF))
				{
					r.num = i - _start;
					_start = i + 1;
					return true;
				}
			}


			// main loop. We crawl the array 8 bytes at a time.

			for (int j = i / 8; j < longs.Length; j++)
			{
				ulong t1 = longs[j];
				unchecked
				{
					t1 ^= comparer;
					ulong t2 = (t1 - 0x0101010101010101) & ~t1;
					if ((t2 & 0x8080808080808080) != 0)
					{
						i = j * 8;
						// make every case 3 comparison instead of n. Potentially better.
						// This is an unrolled binary search.
						if ((t2 & 0x80808080) == 0)
						{
							i += 4;
							t2 >>= 32;
						}

						if ((t2 & 0x8080) == 0)
						{
							i += 2;
							t2 >>= 16;
						}

						if ((t2 & 0x80) == 0)
						{
							i++;
						}
						r.num = i - _start;
						_start = i + 1;
						return true;
					}
				}
				// no matches found increment by 8
			}
			// no matches at all. Let's return the remaining buffer.
			r.num =(_length - _start);
			_start = _bytes.Length;
			return true;
		}

		void IEnumerator.Reset()
		{
			throw new NotImplementedException();
		}

		public object Current
		{
			get
			{
				return this._result;
			}
		}
	}
	public static void Fill_Result(object obj,out int itemNumber,out int start,out int num)
    {
		var r = (Result)obj;
		itemNumber = r.id;
		start = r.start;
		num = r.num;
    }
}

## SplitNVarchar.cs
using Microsoft.SqlServer.Server;
//------------------------------------------------------------------------------
// <copyright file="CSSqlFunction.cs" company="Microsoft">
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//------------------------------------------------------------------------------
using System;
using System.Collections;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
	[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 =
			"itemNumber INT, item NVARCHAR(4000)"         // Returned table definition
		)
	]
	// 1. SQL Server passes input parameters and receives an enumration object
	public static IEnumerator SplitNVarchar
		(
		[SqlFacet(MaxSize = -1)] SqlChars Input,
		char Delimiter,
		int strategy
		)
	{
		return Input.IsNull ?
			new SplitEnumerator(new char[0], char.MinValue) :
			strategy < 2 ? new SplitEnumerator(Input.Value, Delimiter) :
			(IEnumerator) new CLRStringBaseLineEnumerator(Input.Value,strategy);
	}
	private class CLRStringBaseLineEnumerator : IEnumerator
	{
		private readonly char[] _bytes;
		private int _chunks;
		private int _start;
		private readonly int _length;
		readonly SplitRow _record = new SplitRow();
		internal CLRStringBaseLineEnumerator(char[] bytes, int chunks)
		{
			this._bytes = bytes;
			this._chunks = chunks - 1;
			this._length = 2*(bytes.Length / chunks);
		}


		public object Current
		{
			get { return this._record; }
		}

		public bool MoveNext()
		{
			if (_chunks == 0) return false;
			this._record.Sequence++;
			_record.Item = new char[_length/2];
			Buffer.BlockCopy(_bytes, _start, _record.Item, 0, _length);
			_start += _length;
			_chunks--;
			return true;
		}

		public void Reset()
		{
			throw new NotImplementedException();
		}
	}


	// The enumeration object
	class SplitEnumerator : IEnumerator
	{

		readonly char[] input;      // Reference to the string to be split
		readonly char delimiter;    // The delimiter character
		int start;                  // Current search start position
		SplitRow record = new SplitRow();           // Each row to be returned

		// Constructor (called once when the object is created)
		internal SplitEnumerator(char[] Input, char Delimiter)
		{

			input = Input;
			delimiter = Delimiter;
		}

		// Enumerator implementation
		#region IEnumerator Methods

		// 2. SQL Server calls the MoveNext() method on the enumeration object
		public bool MoveNext()
		{
			if (this.start == -1) return false;
			this.record.Sequence++;
			int i = this.start;
			for (; i < this.input.Length; i++)
			{
				if (this.input[i] == this.delimiter)
				{
					this.record.Item = new char[i - this.start];
					Buffer.BlockCopy(this.input, this.start*2, this.record.Item, 0, (i - this.start)*2);
					this.start = i + 1;
					return true;
				}
			}
			this.record.Item = new char[i - this.start];
			Buffer.BlockCopy(this.input, this.start, this.record.Item, 0, (i - this.start)*2);
			this.start = -1;
			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)
		public object Current
		{
			get { return record; }
		}

		// Required by the IEnumerator interface, but not needed for this implementation
		void IEnumerator.Reset()
		{
			throw new System.NotImplementedException();
		}

		#endregion
	}

	// 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 SqlChars item)
	{
		// The passed-in object is an OutputRecord
		var r = (SplitRow)obj;

		// Set the output parameter values
		sequence = r.Sequence;
		item = new SqlChars(r.Item);
	}

	// Structure used to hold each row
	class SplitRow
	{
		internal int Sequence { get; set; }     // Sequence of the element
		internal char[] Item { get; set; }      // The element
	}
};

## SplitVarbinary.cs
using Microsoft.SqlServer.Server;
//------------------------------------------------------------------------------
// <copyright file="CSSqlFunction.cs" company="Microsoft">
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//------------------------------------------------------------------------------
using System;
using System.Collections;
using System.Data.SqlTypes;
public partial class UserDefinedFunctions
{

	[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 = "FillBytes",                  // The method called by SQL Server to obtain the next row
		TableDefinition ="itemNumber int, item varbinary(8000)"         // Returned table definition
	)
	]
	public static IEnumerator SplitVarbinary(
	// our interest is smaller varchars.
	// anything beyond 8000 is best off using the normal splitter.
		[SqlFacet(MaxSize=8000,IsFixedLength=false,IsNullable=false)]
		SqlBytes bytes,
		[SqlFacet(MaxSize=1,IsFixedLength=true,IsNullable=false)]
		byte delimiter,
		int strategy)
    {
		if (bytes.IsNull) return new Record[0].GetEnumerator();
		if(strategy == 0) return new SplitBytesEnumerator(bytes.Value, delimiter);
		if(strategy == 1) return new SplitBytesEnumeratorA(bytes.Value,delimiter);
		return new CLRBaselineEnumerator(bytes.Value, strategy);

    }
	private class CLRBaselineEnumerator : IEnumerator
	{
		private readonly byte[] _bytes;
		private int _chunks;
		private int _start;
		private readonly int _length;
		private readonly Record _record = new Record { };
		internal CLRBaselineEnumerator(byte[] bytes, int chunks)
		{
			this._bytes = bytes;
			this._chunks = chunks-1;
			this._length = bytes.Length / chunks;
		}


		public object Current
		{
			get { return this._record; }
		}

		public bool MoveNext()
		{
			if (_chunks == 0) return false;
			this._record.id++;
			_record.item = new byte[_length];
			Buffer.BlockCopy(_bytes, _start, _record.item, 0, _length);
			_start += _length;
			_chunks--;
			return true;
		}

		public void Reset()
		{
			throw new NotImplementedException();
		}
	}
	private class SplitBytesEnumerator : IEnumerator
	{
		// Fields
		private readonly byte[] bytes;
		private readonly byte delim;
		private Record record = new Record();
		private int start;

		// Methods
		internal SplitBytesEnumerator(byte[] bytes, byte delimiter)
		{
			this.bytes = bytes;
			this.delim = delimiter;
		}

		public bool MoveNext()
		{
			if (this.start == -1) return false;
			this.record.id++;
			int i = this.start;
			for (; i < this.bytes.Length;i++ )
			{
				if (this.bytes[i] == this.delim)
				{
					this.record.item = new byte[i - this.start];
					Buffer.BlockCopy(this.bytes, this.start, this.record.item, 0, i - this.start);
					this.start = i + 1;
					return true;
				}
			}
			this.record.item = new byte[i - this.start];
			Buffer.BlockCopy(this.bytes, this.start, this.record.item, 0, i - this.start);
			this.start = -1;
			return true;
		}

		void IEnumerator.Reset()
		{
			throw new NotImplementedException();
		}

		public object Current
		{
			get
			{
				return this.record;
			}
		}
	}
	class SplitBytesEnumeratorA : IEnumerator
	{
		// Fields
		private readonly byte[] _bytes;
		private readonly ulong[] _longs;
		private readonly ulong _comparer;
		private readonly Record _record = new Record();
		private int _start;
		private readonly int _length;

		// Methods
		internal SplitBytesEnumeratorA(byte[] bytes, byte delimiter)
		{
			this._bytes = bytes;
			this._length = bytes.Length;
			// we do this so that we can avoid a spillover scan near the end.
			// in unsafe implementation this would be dangerous as we potentially
			// will be reading more bytes than we should.

			this._longs = new ulong[(_length + 7) / 8];
			Buffer.BlockCopy(bytes, 0, _longs, 0, _length);
			var c = (((ulong)delimiter << 8) + (ulong)delimiter);
			c = (c << 16) + c;
			// comparer is now 8 copies of the original delimiter.
			c |= (c << 32);
			this._comparer = c;
		}

		public bool MoveNext()
		{
			if (this._start >= this._length) return false;
			int i = this._start;
			var longs = this._longs;
			var comparer = this._comparer;
			var record = this._record;
			record.id++;
			// handle the case where start is not divisible by eight.
			for (; (i & 7) != 0; i++)
			{
				if (i == _length || _bytes[i] == (comparer & 0xFF))
				{
					record.item = new byte[(i - _start)];
					Buffer.BlockCopy(_bytes, _start, record.item, 0, i - _start);
					_start = i + 1;
					return true;
				}
			}


			// main loop. We crawl the array 8 bytes at a time.

			for (int j=i/8; j < longs.Length; j++)
			{
				ulong t1 = longs[j];
				unchecked
				{
					t1 ^= comparer;
					ulong t2 = (t1 - 0x0101010101010101) & ~t1;
					if ((t2 & 0x8080808080808080) != 0)
					{
						i =j*8;
						// make every case 3 comparison instead of n. Potentially better.
						// This is an unrolled binary search.
						if ((t2 & 0x80808080) == 0)
						{
							i += 4;
							t2 >>= 32;
						}

						if ((t2 & 0x8080) == 0)
						{
							i += 2;
							t2 >>= 16;
						}

						if ((t2 & 0x80) == 0)
						{
							i++;
						}
						record.item = new byte[(i - _start)];
						// improve cache locality by not switching collections.
						Buffer.BlockCopy(longs, _start, record.item, 0, i - _start);
						_start = i + 1;
						return true;
					}
				}
				// no matches found increment by 8
			}
			// no matches left. Let's return the remaining buffer.
			record.item = new byte[(_length - _start)];
			Buffer.BlockCopy(longs, _start, record.item, 0, (_length - _start));
			_start = _bytes.Length;
			return true;
		}

		void IEnumerator.Reset()
		{
			throw new NotImplementedException();
		}

		public object Current
		{
			get
			{
				return this._record;
			}
		}
	}
	// We use a class to avoid boxing .
	class Record{
		internal int id;
		internal byte[] item;
	}
	public static void FillBytes(object obj, out SqlInt32 sequence, out SqlBytes item)
	{
		// The passed-in object is an OutputRecord
		Record r = (Record)obj;

		// Set the output parameter values
		sequence = r.id;
		item = new SqlBytes(r.item);
	}
}

## updated_test_script.sql
/**********************************************************************************************************************
 Thanks for running these splitter tests for me.

 This script is a mostly hands off script.  It does everything needed for the tests.  All you need to do is...
 1.  Please make sure that SSMS is in the "Grid" output mode when running this script.
 2.  Run this script.  It builds all of the objects it needs and produces the result set need at the end.
     Note that this script runs in TempDB so as not to take any chances with your data, sprocs, etc.
 3.  When the run is Please copy the second result set (including the column names) into a spreadsheet and
     sent the spreadsheet to me at jbmoden@ameritech.net.
 4.  This script also deletes all of the objects it created except for the final result set which can
     be deleted after you've sent me the spreadsheet.  I left it there so if something went wrong,
     you wouldn't have to rerun the whole test again.

It would also be helpful if you provided a brief description of your hardware and the version of SQL Server that
your running on.  This script does NOT automatically capture any information about your machine.

 Thanks again for your help.
 --Jeff Moden - 10 Apr 2011
**********************************************************************************************************************/

--===== Do this all in a nice, safe place that everyone has.
    USE TempDB;
GO
--Create the driver table.
-- We will use this to create the a list of functions we want to test.
if object_id('dbo.functions','u') is not null drop table dbo.functions;
GO
CREATE TABLE dbo.functions
(
	id int not null identity primary key clustered,
	name sysname,
	d_type char(1) not null,
	chunker bit not null default(0)
)
insert into functions(name,d_type,chunker)
VALUES
('SqlBaseline','v',1)
,('SplitVarchar','v',0)
,('SplitVarcharA','v',0)
,('CLRBaseline','v',1)
,('hybrid','v',0)
,('DelimitedSplit8kb','v',0)
,('Split','n',0)
,('SplitNVarchar','n',0)
,('HybridNV','v',0)
,('CLRNVBaseline','n',1)

GO
IF  EXISTS
    (
    SELECT 1
    FROM sys.objects
    WHERE
        [object_id] = OBJECT_ID(N'dbo.Split')
        AND type_desc = N'CLR_TABLE_VALUED_FUNCTION'
    )
    DROP FUNCTION dbo.Split
GO

-- Drop the assembly if it exists
IF  EXISTS
    (
    SELECT 1
    FROM sys.assemblies
    WHERE
        name = N'Split'
    )
    DROP ASSEMBLY Split;
GO
CREATE ASSEMBLY [Split] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030024E9B34D0000000000000000E00002210B010800000E00000006000000000000AE2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000582D000053000000004000009003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40D000000200000000E000000020000000000000000000000000000200000602E7273726300000090030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000902D0000000000004800000002000500E0210000780B00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A2026F1300000A2D0E026F1400000A0373040000062B0C168D140000011673040000068C030000022A000000133002001A0000000100001102A5040000020A031200280800000654041200280A000006512A1E02281500000A2AC202037D0100000402047D0300000402027B010000048E697D02000004027C05000004FE150400000202167D040000042A0013300500C200000002000011027B04000004027B020000043302162A027B040000040A2B56027B010000040693027B030000043342027C0500000425280800000617582809000006027C05000004027B01000004027B0400000406027B0400000459731700000A280B000006020617587D04000004172A0617580A06027B0200000432A1027C0500000425280800000617582809000006027C05000004027B01000004027B04000004027B02000004027B0400000459731700000A280B00000602027B020000047D04000004172A32027B050000048C040000022A1A731800000A7A1E027B060000042A2202037D060000042A1E027B070000042A2202037D070000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000CC030000237E0000380400008404000023537472696E677300000000BC0800000800000023555300C4080000100000002347554944000000D4080000A402000023426C6F6200000000000000020000015717A2030902000000FA253300160000010000001900000004000000070000000B00000009000000010000001900000012000000020000000200000003000000050000000300000001000000020000000200000000000A00010000000000060052004B00060059004B000600760063000A00A3008E000A0029020E0206007102520206009B0289020600B20289020600CF0289020600EE02890206000703890206002003890206003B03890206005603890206006F0352020600830389020600BC039C030600DC039C030A00FA030E02060024044B0006002904520206003F04520206004A044B00060051044B00060069049C03000000000100000000000100010001001000140000000500010001000B011000290000000900010004000B01100039000000090006000800210053012E0021005901320021006001350001006A0132000100700138000100CA0132000100E40152005020000000009600AC000A0001007C20000000009600B20012000300A220000000008618BA001B000600AA20000000008318BA001F000600DC2000000000E101C00026000800AA2100000000E109F1002A000800B72100000000E10128011B000800BE210000000083089E0140000800C621000000008308AB0144000800CF21000000008308B80149000900D721000000008308C1014D000900000001000802000002003B02000001004502020002004902020003007E02000001000802000002003B0200000100830200000100830203000D001900E800260019001C012A0019004D011B002900BA001B003100BA001B003900BA004D004100BA004D004900BA004D005100BA004D005900BA004D006100BA004D006900BA004D007100BA004D007900BA0070008100BA004D008900BA0044009100BA001B009900BA001B0021000F04260021001A040D020900BA001B00A900BA001702B900BA001D02C100BA001B00C900BA001B00200093007500240023005D002E0033002E022E00430045022E008B0084022E004B004B022E0053002E022E00730045022E003B0045022E0083007B022E005B005A022E0063004502C100CB002902E100CB0029020001CB0029022001CB0029024001CB0029026001CB002902120225020300010004000200000077013C000000FA01550000000302590002000600030001000900050002000800050002000A00070001000B00070003000A00030003000C00050003000E00070004800000010000002310FA9B000000000000AC00000002000000000000000000000001004200000000000200000000000000000000000100820000000000030002000400020000000000003C4D6F64756C653E0053706C69742E646C6C0055736572446566696E656446756E6374696F6E730053706C6974456E756D657261746F720053706C6974526F77006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C69740046696C6C526F77002E63746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E4D6F76654E657874004D6F76654E6578740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E526573657400526573657400696E707574006C656E6774680064656C696D69746572007374617274007265636F72640053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E74006765745F53657175656E6365007365745F53657175656E6365006765745F4974656D007365745F4974656D003C53657175656E63653E6B5F5F4261636B696E674669656C64003C4974656D3E6B5F5F4261636B696E674669656C640053657175656E6365004974656D00496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0073657175656E63650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465006974656D0076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E67004E6F74496D706C656D656E746564457863657074696F6E00436F6D70696C657247656E657261746564417474726962757465000003200000000000901C02C60145A34EACF1D06C744C88640008B77A5C561934E089070002120D121103080003011C1008100E03200001062002011D0303032000020320001C03061D03020608020603030611100328001C0320000804200101080320000E042001010E02060E032800080328000E12010001005408074D617853697A65FFFFFFFF04200101028196010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E2173657175656E636520494E542C206974656D204E564152434841522834303030290420001D030407011110052001011159072003011D0308080307010804010000001601001153716C53657276657250726F6A6563743100000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100802D000000000000000000009E2D0000002000000000000000000000000000000000000000000000902D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100FA9B231000000100FA9B23103F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000004C0012000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C00530065007200760065007200500072006F006A006500630074003100000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003100330031002E00330039003900330030000000000034000A00010049006E007400650072006E0061006C004E0061006D0065000000530070006C00690074002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003100000000003C000A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C00690074002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D00650000000000530071006C00530065007200760065007200500072006F006A006500630074003100000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003100330031002E00330039003900330030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003100330031002E00330039003900330030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH
    PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.Split
(
    @Input      NVARCHAR(MAX),
    @Delimiter  NCHAR(1)
)
RETURNS TABLE
(
    ItemNumber  INTEGER NULL,
    Item        NVARCHAR(4000) NULL
)
AS EXTERNAL NAME Split.UserDefinedFunctions.Split;
GO
SELECT * into #t from dbo.Split('a,b,c,d,e,f,g,h,i,j',',')
DROP table #t
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.CLRNVBaseline')
	)
	drop function dbo.CLRNVBaseline
GO
IF  EXISTS
    (
    SELECT 1
    FROM sys.objects
    WHERE
        [object_id] = OBJECT_ID(N'dbo.SplitNvarchar')
    )
    DROP FUNCTION dbo.splitNVarchar
GO
IF  EXISTS
    (
    SELECT 1
    FROM sys.objects
    WHERE
        [object_id] = OBJECT_ID(N'dbo.SplitNvarcharInternal')
    )
    DROP FUNCTION dbo.splitNVarcharInternal
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.hybrid')
	)
	drop function dbo.hybrid
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.hybridinternal')
	)
	drop function dbo.hybridinternal
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.SplitVarchar')
	)
	drop function dbo.SplitVarchar
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.CLRBaseLine')
	)
	drop function dbo.CLRBaseLine
GO
if exists
	(
	select 1 from sys.objects where object_id = OBJECT_ID(N'dbo.SplitVarcharA')
	)
	drop function dbo.SplitVarcharA
GO

if exists(
	select 1 from sys.objects where object_id = object_id(N'dbo.splitVarbinary')
)
Drop function dbo.splitVarbinary;
Go
if exists (select 1 from sys.objects where object_id = object_id(N'HybridNV'))
drop function dbo.hybridNV
GO
if exists (select 1 from sys.objects where object_id = object_id(N'HybridNVInternal'))
drop function dbo.hybridNVInternal
GO

IF  EXISTS
    (
    SELECT 1
    FROM sys.assemblies
    WHERE
        name = N'SplitTest'
    )
    DROP ASSEMBLY SplitTest;
GO


CREATE ASSEMBLY [SplitTest] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008D9B84530000000000000000E00022200B0130000022000000060000000000000E41000000200000006000000000001000200000000200000400000000000000040000000000000000A000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000BA4000004F000000006000007803000000000000000000000000000000000000008000000C0000001C4000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000014210000002000000022000000020000000000000000000000000000200000602E7273726300000078030000006000000004000000240000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000002800000000000000000000000000004000004200000000000000000000000000000000EE400000000000004800000002000500542A0000C81500000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002004200000000000000026F1200000A2C0C168D06000002281300000A2A042D0D026F1400000A03730D0000062A0417330D026F1400000A0373110000062A026F1400000A0473090000062A00001330020026000000010000110274060000020A03067B10000004281600000A811600000104067B11000004731800000A512A0000133002003900000002000011026F1900000A2D220418320F026F1A00000A0473160000060A062A026F1A00000A03731A0000060A062A168D1D00000116731A0000060A062A000000133002001D000000030000110274090000020A03066F1E0000065404066F20000006731B00000A512A86026F1900000A2C0C168D0A000002281300000A2A026F1A00000A0373240000062A86026F1200000A2C0C168D0A000002281300000A2A026F1400000A0373280000062A00000013300200200000000400001102740A0000020A03067B1D0000045404067B1E0000045405067B1F000004542A1E02281500000A2AB60273150000067D0500000402281500000A02037D01000004020417597D0200000402038E69045B7D040000042A1E027B050000042A0000133005007900000000000000027B020000042D02162A027B05000004257B1000000417587D10000004027B05000004027B040000048D1C0000017D11000004027B01000004027B03000004027B050000047B1100000416027B04000004281700000A02027B03000004027B04000004587D0300000402027B0200000417597D02000004172A1A731C00000A7A820273150000067D0800000402281500000A02037D0600000402047D070000042A00000013300600D400000005000011027B09000004153302162A027B08000004257B1000000417587D10000004027B090000040A2B5C027B060000040691027B070000043348027B0800000406027B09000004598D1C0000017D11000004027B06000004027B09000004027B080000047B110000041606027B0900000459281700000A020617587D09000004172A0617580A06027B060000048E693299027B0800000406027B09000004598D1C0000017D11000004027B06000004027B09000004027B080000047B110000041606027B0900000459281700000A02157D09000004172A1E027B080000042A1330050068000000060000110273150000067D0D00000402281500000A02037D0A00000402038E697D0F00000402027B0F0000041D581E5B8D1A0000017D0B0000040316027B0B00000416027B0F000004281700000A046E1E62046E580A061F106206580A06251F2062600A02067D0C0000042A13300600A601000007000011027B0E000004027B0F0000043202162A027B0E0000040A027B0B0000040B027B0C0000040C027B0D0000040D09257B1000000417587D100000042B5E06027B0F0000042E13027B0A00000406916E0820FF0000006A5F333E0906027B0E000004598D1C0000017D11000004027B0A000004027B0E000004097B110000041606027B0E00000459281700000A020617587D0E000004172A0617580A061D5F2D9D061E5B130438AB0000000711049613051105086113051105210101010101010101591105665F130611062180808080808080805F2C7911041E5A0A110620808080806E5F2D0B061A580A11061F20641306110620808000006A5F2D0B0618580A11061F10641306110620800000006A5F2D040617580A0906027B0E000004598D1C0000017D1100000407027B0E000004097B110000041606027B0E00000459281700000A020617587D0E000004172A1104175813041104078E693F4BFFFFFF09027B0F000004027B0E000004598D1C0000017D1100000407027B0E000004097B1100000416027B0F000004027B0E00000459281700000A02027B0A0000048E697D0E000004172A1E027B0D0000042ABE0273220000067D1600000402281500000A02037D12000004020417597D130000040218038E69045B5A7D150000042A1E027B160000042A0000133005007D00000005000011027B130000042D02162A027B16000004256F1E0000060A0617586F1F000006027B16000004027B15000004185B8D1D0000016F21000006027B12000004027B14000004027B160000046F2000000616027B15000004281700000A02027B14000004027B15000004587D1400000402027B1300000417597D13000004172A820273220000067D1A00000402281500000A02037D1700000402047D180000042A000013300600DC00000008000011027B19000004153302162A027B1A000004256F1E0000060B0717586F1F000006027B190000040A2B60027B170000040693027B18000004334C027B1A00000406027B19000004598D1D0000016F21000006027B17000004027B19000004185A027B1A0000046F200000061606027B1900000459185A281700000A020617587D19000004172A0617580A06027B170000048E693295027B1A00000406027B19000004598D1D0000016F21000006027B17000004027B19000004027B1A0000046F200000061606027B1900000459185A281700000A02157D19000004172A1E027B1A0000042A1E027B1B0000042A2202037D1B0000042A1E027B1C0000042A2202037D1C0000042A820273230000067D2200000402281500000A02037D2000000402047D210000042A00133003009300000005000011027B23000004153302162A027B22000004257B1D00000417587D1D000004027B22000004027B2300000417587D1E000004027B230000040A2B32027B200000040693027B21000004331E027B2200000406027B23000004597D1F000004020617587D23000004172A0617580A06027B200000048E6932C3027B2200000406027B23000004597D1F00000402157D23000004172A1E027B220000042A001330050068000000060000110273230000067D2700000402281500000A02037D2400000402038E697D2900000402027B290000041D581E5B8D1A0000017D250000040316027B2500000416027B29000004281700000A046E1E62046E580A061F106206580A06251F2062600A02067D260000042A133003004A01000009000011027B28000004027B290000043202162A027B280000040A027B250000040B027B260000040C027B270000040D09257B1D00000417587D1D00000409027B2800000417587D1E0000042B3906027B290000042E13027B2400000406916E0820FF0000006A5F33190906027B28000004597D1F000004020617587D28000004172A0617580A061D5F2DC2061E5B1304388B0000000711049613051105086113051105210101010101010101591105665F130611062180808080808080805F2C5911041E5A0A110620808080806E5F2D0B061A580A11061F20641306110620808000006A5F2D0B0618580A11061F10641306110620800000006A5F2D040617580A0906027B28000004597D1F000004020617587D28000004172A1104175813041104078E693F6BFFFFFF09027B29000004027B28000004597D1F00000402027B240000048E697D28000004172A1E027B270000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000A0080000237E00000C0900009405000023537472696E677300000000A00E00000800000023555300A80E0000100000002347554944000000B80E00001007000023426C6F6200000000000000020000015717A20B0902000000FA013300160000010000001F0000000C000000290000002B00000024000000070000001C0000001E0000000900000008000000090000000B000000050000000100000001000000020000000A0000000000AF0201000000000006008E0127040600500227040600DA0014040F00470400000600EE00F10206007101F10206003D01F10206003702F1020600D401F1020600FF01F10206000501F10206002001F1020600AE01830506001A0283050600CC04DF020A005C015E030A007E005E030A0078005E030600790394040A00ED015E030A00750456040A00010056040A00BC0456040600BF00270406005B05DF0206000A00DF0206003903DF0206006E02DF0206001B03DF0206000400DF0206000303DF02000000001100000000000100010001001000A70400003D000100010003001000C50300003D000100090003001000DB0300003D0006000D00030010001A0000003D000A001100030010008D0000003D001000150003001000A90300003D001200160003001000FE0300003D0017001A0003001000520500003D001B001E0003001000090500003D001D00230003001000850300003D002000240003001000980300003D002400280021007E048A0001008C048E00010024058E00210083028E0021009400910021007F048A002100E7029500010095009100010025058E0021007E048A00210085049800210040039C00210094009100010024058E00210083028E00030045008E000300DA028A0021007E049F0001008C048E00010024058E00210083028E0021009400A30021003B059F0021005403A700010025058E0001009500A300010048008E00010062009F00060045008E00060025058E000600ED028E002100C5049F002100E602A70021001005AA00010024058E0021007E048A00210085049800210040039C0021001005AA00010024058E00210083028E0050200000000096007405AE000100A0200000000096006B04B7000400D4200000000096002003C20007001C210000000096004A05CB000A0045210000000096008F02D5000D006721000000009600A002DD000F008C210000000096000405E5001100B8210000000086180E0406001500C0210000000083180E04F0001500EE2100000000E6091805F7001700F82100000000E6014105150017007D2200000000E601F2040600170084220000000083180E04FB001700A82200000000E6014105150019007D2200000000E101D30406001900882300000000E6091805F700190090230000000083180E04FB001900042400000000E601410515001B007D2200000000E101D30406001B00B62500000000E6091805F7001B00B8210000000086180E0406001B00BE250000000083180E0402011B00EE2500000000E6091805F7001D00F82500000000E601410515001D007D2200000000E601F20406001D0081260000000083180E0409011D00A42600000000E601410515001F008C2700000000E6091805F7001F007D2200000000E101D30406001F0094270000000083089C0010011F009C27000000008308A90001001F00A527000000008308C8023A002000AD27000000008308D1023F002000B8210000000086180E0406002100B8210000000086180E0406002100B6270000000083180E0409012100D82700000000E6014105150023007D2200000000E101D30406002300772800000000E6091805F700230080280000000083180E04FB002300F42800000000E6014105150025007D2200000000E101D304060025004A2A00000000E6091805F7002500000001007F04000002005403000003006105000001008B0202000200B60002000300DA02000001003505000002004A03000003006105000001008B0202000200B60002000300DA0200000100C604000002005403000001007F04000002005403000001008B02020002002E0302000300250502000400ED02000001007F04000002008D04000001007F04000002005403000001007F04000002005403000001007F04000002008D04000001003505000002004A03000001007D02000001007D0200000100C604000002005403000001007F0400000200540303004D0004004D0005004D0007004D0008004D000B004D000C004D0009000E04010011000E04060019000E040A0029000E04100031000E04100039000E04100041000E04100049000E04100051000E04100059000E04100061000E04100069000E04060071000E04060081000E040600A1000E0406009900F2040600C1000E040600A900BD021500C900F0031900A90073021E0079000E040600B100F8042300D9006A052900A9000E043400B900BD021500B90073023A00B9000E043F00F9000E04060020007300910124007B0065062E000B0021012E0013002A012E001B0049012E00230052012E002B0061012E00330061012E003B0061012E00430052012E004B0067012E00530061012E005B007F012E0063008C012E006B008C0144007B009706600073002E03A0007300C804C0007300C804E4007B00C906A4017B00DC06C4017B009706E4017B00650604027B00970661038B008C0181038B008C01C0038B008C01E0038B008C0100048B008C0120048B008C0148004D00520057005C0060006400700075000300010004000200050003000700040008000500090006000B0008000C00090000001C05140100001C05140100001C05140100001C05140100001C0514010000AD0018010000D5021C0100001C05140100001C05140102000A00030002001000050002001400070002001700090002001C000B0002001E000D0001001F000D00020020000F00010021000F0002002700110002002B00130004001E00210005002600210008003A0021000B004C0021000C005400210045000480000001000000000000000000000000002B05000002000000000000000000000081003C00000000000200000000000000000000008100300000000000030002000400020005000200060002000700020008000200090002000A0002000B0002000C000200000000000053716C496E7433320055496E743634003C4D6F64756C653E0053706C69744279746573456E756D657261746F72410053797374656D2E44617461006D73636F726C6962006964003C53657175656E63653E6B5F5F4261636B696E674669656C64003C4974656D3E6B5F5F4261636B696E674669656C640053797374656D446174614163636573734B696E64005265636F7264005F7265636F7264006765745F53657175656E6365007365745F53657175656E63650073657175656E636500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500416C6C6F775061727469616C6C795472757374656443616C6C65727341747472696275746500417373656D626C7950726F647563744174747269627574650053716C466163657441747472696275746500417373656D626C79436F707972696768744174747269627574650053656375726974795472616E73706172656E7441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650042797465006765745F56616C75650076616C7565005F6C656E677468006F626A004879627269644E56496E7465726E616C00487962726964496E7465726E616C0053706C6974546573742E646C6C006765745F49734E756C6C006765745F4974656D007365745F4974656D006974656D0053797374656D005F64656C696D006E756D0053797374656D2E5265666C656374696F6E004E6F74496D706C656D656E746564457863657074696F6E00436861720053706C69744E56617263686172006974656D4E756D62657200427566666572005F636F6D70617265720044656C696D697465720064656C696D69746572004D6963726F736F66742E53716C5365727665722E5365727665720049456E756D657261746F72004879627269644E56456E756D657261746F7200487962726964456E756D657261746F7200434C52537472696E67426173654C696E65456E756D657261746F7200434C52426173656C696E65456E756D657261746F720053706C69744279746573456E756D657261746F7200476574456E756D657261746F720053706C6974456E756D657261746F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730046696C6C42797465730053716C4279746573005F6279746573005F6C6F6E6773005F6368756E6B730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E730053716C4368617273005F6368617273004F626A6563740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E5265736574006F705F496D706C696369740046696C6C5F526573756C74005F726573756C74006765745F43757272656E74005F73746172740053706C69745465737400496E70757400696E707574004D6F76654E6578740046696C6C526F770053706C6974526F7700417272617900737472617465677900426C6F636B436F70790053706C697456617262696E6172790053797374656D2E5365637572697479000000032000000000006BCD7141C3CF7B429C562162056F25A100042001010803200001052001011111042001010E03200002042000124D0420001D050500011159080A00050112650812650808052001011D050420001D03052001011D03021D0B0407011218040701124D04070112240407011228030701080307010B0B0707081D0B0B1218080B0B04070208080B0707081D0B0B1228080B0B08B77A5C561934E08903061D050206080306121802060503061D0B02060B03061D030306122402060303061228080003124D125505080A0003011C101159101255080003124D125D0308090003011C100810125D070002124D125D03070002124D1255050A0004011C100810081008062002011D05080320001C062002011D0505062002011D0308062002011D0303032000080328001C032800080428001D030801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000000E01000953706C697454657374000005010000000017010012436F7079726967687420C2A920203230313400000C010007312E302E302E3000000401000000819B010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650946696C6C4279746573540E0F5461626C65446566696E6974696F6E246974656D4E756D62657220696E742C206974656D2076617262696E6172792838303030298198010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E236974656D4E756D62657220494E542C206974656D204E56415243484152283430303029819B010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650B46696C6C5F526573756C74540E0F5461626C65446566696E6974696F6E226974656D4E756D62657220696E742C20737461727420696E742C206E756D20696E7431010003005408074D617853697A65401F000054020D497346697865644C656E6774680054020A49734E756C6C61626C650031010003005408074D617853697A650100000054020D497346697865644C656E6774680154020A49734E756C6C61626C650012010001005408074D617853697A65FFFFFFFF31010003005408074D617853697A65FFFFFFFF54020D497346697865644C656E6774680054020A49734E756C6C61626C65000000000000008D9B84530000000002000000820000003840000038220000525344539746077012DC664A85A863558A5EAA3201000000433A5C55736572735C6D746275726265615C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C7465737450726F6A6563745C7465737450726F6A6563745C6F626A5C52656C656173655C53706C6974546573742E70646200E24000000000000000000000FC400000002000000000000000000000000000000000000000000000EE400000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C00000000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000586000001C03000000000000000000001C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0047C020000010053007400720069006E006700460069006C00650049006E0066006F0000005802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D00650000000000000000003C000A000100460069006C0065004400650073006300720069007000740069006F006E0000000000530070006C006900740054006500730074000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000530070006C006900740054006500730074002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100340000002A00010001004C006500670061006C00540072006100640065006D00610072006B007300000000000000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C006900740054006500730074002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D00650000000000530070006C006900740054006500730074000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000103100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE


GO

CREATE FUNCTION [dbo].[SplitVarbinary]
(@bytes VARBINARY (8000),
 @delimiter binary(1),
 @strat int)
RETURNS
     TABLE (
        [itemNumber] INT              NULL,
        [item]       VARBINARY (8000) NULL
		)
AS
 EXTERNAL NAME [SplitTest].[UserDefinedFunctions].[SplitVarbinary]
GO
CREATE FUNCTION [dbo].[SplitVarchar]
(
	@str varchar(8000),
	@delimiter char(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,Convert(varchar(8000),item) [item]
	from dbo.[SplitVarbinary](Convert(Varbinary(8000),@str),convert(binary(1),@delimiter),0)
;
GO
CREATE FUNCTION [dbo].[SplitVarcharA]
(
	@str varchar(8000),
	@delimiter char(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,Convert(varchar(8000),item) [item]
	from dbo.[SplitVarbinary](Convert(Varbinary(8000),@str),convert(binary(1), @delimiter ),1)
;
GO
CREATE FUNCTION [dbo].[CLRBaseline]
(
	@str varchar(8000),
	@delimiter char(1),
	@chunks int
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,Convert(varchar(8000),item) [item]
	from dbo.[SplitVarbinary](Convert(Varbinary(8000),@str),convert(binary(1),@delimiter),@chunks)
;
Go
CREATE FUNCTION [dbo].[SplitNVarcharInternal]
(@Input NVARCHAR (MAX), @Delimiter NCHAR (1),@strategy int)
RETURNS
     TABLE (
        [itemNumber] INT             NULL,
        [item]       NVARCHAR (4000) NULL)
AS
 EXTERNAL NAME [SplitTest].[UserDefinedFunctions].[SplitNVarchar]
GO
CREATE FUNCTION [dbo].[CLRNVBaseline]
(
	@str nvarchar(max),
	@delimiter nchar(1),
	@chunks int
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,[item]
	from dbo.[SplitNvarcharInternal](@str,@delimiter,@chunks)
;
Go
CREATE FUNCTION [dbo].[SplitNVarchar]
(
	@str nvarchar(max),
	@delimiter nchar(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

	SELECT itemNumber,[item]
	from dbo.[SplitNvarcharInternal](@str,@delimiter,0)
;
Go
CREATE FUNCTION [dbo].[HybridNVInternal] (@chars [nvarchar](MAX), @delimiter [nchar](1))
RETURNS TABLE (itemNumber int, start int, num int)
AS EXTERNAL NAME [SplitTest].[UserDefinedFunctions].[HybridNVInternal];

GO
Create Function [dbo].[HybridNV](@pstring varchar(8000),@delimiter char(1))
returns table with schemabinding as
return
select itemNumber,item=substring(@pstring,start,num)
from dbo.HybridNVInternal(@pstring,@delimiter)
GO

CREATE FUNCTION [dbo].[HybridInternal] (@bytes [varbinary](8000), @delimiter [tinyint])
RETURNS TABLE (itemNumber int, start int, num int)
AS EXTERNAL NAME [SplitTest].[UserDefinedFunctions].[HybridInternal];
go

Create Function [dbo].[Hybrid](@pstring varchar(8000),@delimiter char(1))
returns table with schemabinding as
return
select itemNumber,item=substring(@pstring,start,num)
from dbo.HybridInternal(convert(varbinary(8000),@pstring),ascii(@delimiter))
GO
SELECT * into #t from dbo.SplitNVarcharInternal('1,2,3,',',',0)
select * into #t2 from dbo.SplitNVarcharInternal('1,2,3',',',2)
select * into #t3 from dbo.SplitVarbinary(0x010203,0x02,0)
select * into #t4 from dbo.SplitVarbinary(0x010203,0x02,1)
select * into #t5 from dbo.SplitVarbinary(0x010203,0x02,2)
select * into #t6 from dbo.HybridInternal(0x010203,0x03)
select * into #t7 from dbo.HybridNVInternal(N'abc,dsefefdf,',',')
drop table #t
drop table #t2
drop table #t3
drop table #t4
drop table #t5
drop table #t6
drop table #t7
GO
if object_id('dbo.SqlBaseline') is not null
drop function SqlBaseline;
GO

Create function SqlBaseline
(
	@pstring varchar(8000),
	@pdelimiter char(1),
	@chunks int
)
returns table with schemabinding as
return
with T0(n) as (select 1 union all select 1 union all select 1 union all
			select 1 union all select 1 union all select 1 union all
			select 1 union all select 1 union all select 1 union all select 1),
	Tally(n) as (select top(@chunks-2) convert(int,ROW_NUMBER() over (order by (select null))) from T0 a,T0 b,T0 c,T0 d),
	cteStart(n) as (
		select 1
		union all
		select n*datalength(@pstring)/(@chunks-1)
		from tally
	)
	select ItemNumber = ROW_NUMBER() over (order by (select N)),
	Item=SUBSTRING(@pstring,n,datalength(@pstring)/(@chunks-1))
	from cteStart;

GO


if(OBJECT_ID('delimitedSplit8kb') is not null) drop function DelimitedSplit8Kb;
GO
CREATE FUNCTION [dbo].[DelimitedSplit8KB]
--===== Define I/O parameters
        (@pString VARCHAR(8000) , @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
				 SELECT 1
                ),
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY a.n) FROM E1 a,E1 b,E1 c, E1 d
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t
				 WHERE SUBSTRING(@pString,t.N,1) COLLATE Latin1_General_BIN2 = @pDelimiter COLLATE Latin1_General_BIN2
                ),
cteLen(N1,L1) AS(
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter COLLATE Latin1_General_BIN2,@pString COLLATE Latin1_General_BIN2,s.N1) ,0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO
--=====================================================================================================================
--      Conditionally drop and recreate a View that will allow us to use NEWID() within a function so we can make
--      Random numbers in a function and create a function that will create constrained randomized CSV element rows.
--=====================================================================================================================
--===== Conditionally drop the objects in the code below to make reruns easier
     IF OBJECT_ID('TempDB.dbo.iFunction'  ,'V' ) IS NOT NULL DROP VIEW     dbo.iFunction;
     IF OBJECT_ID('TempDB.dbo.CreateCsv8K','IF') IS NOT NULL DROP FUNCTION dbo.CreateCsv8K;
GO
 CREATE VIEW dbo.iFunction AS
/**********************************************************************************************************************
 Purpose:
 This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such
 a thing directly in the function.  This view also solves the same problem for GETDATE().

 Usage:
 SELECT MyNewID FROM dbo.iFunction; --Returns a GUID
 SELECT MyDate  FROM dbo.iFunction; --Returns a Date


 Revision History:
 Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation
 Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code.  No logic changes.
**********************************************************************************************************************/
SELECT MyNewID = NEWID(),
       MyDate  = GETDATE();
GO
 CREATE FUNCTION dbo.CreateCsv8K
/**********************************************************************************************************************
 Purpose:
 Create a CSV table result with a programable number of rows, elements per row, minimum # of characters per element,
 and maximum characters per element.  The element size is random in nature constrained by the min and max characters
 per element.

 Usage:
 SELECT * FROM dbo.CreateCsv8K(@pNumberOfRows, @pNumberOfElementsPerRow, @pMinElementwidth, @pMaxElementWidth)

 Dependencies:
 1. View: dbo.iFunction (Produces a NEWID() usable from within a UDF)

 Programmer's Notes:
 1. The randomness of the elements prevents the delimiters for showing up in the same position for each row so that
    SQL Server won't figure that out and cache the information making some splitting techniques seem faster than they
    really are.
 2. No validation or constraints have been place on the input parameters so use with caution.  This code can generate
    a lot of data in a couple of heart beats.

 Revision History:
 Rev 00 - 11 May 2007 - Jeff Moden - Initial creation - Only returned one row and wasn't programmable.
 Rev 01 - 26 Jul 2009 - Jeff Moden - Added programmable variables but would only go to 20 characters wide.
 Rev 02 - 06 Mar 2011 - Jeff Moden - Converted to iTVF, added minimum element width, and made it so elements can be
                                     virtually any size.
**********************************************************************************************************************/
--===== Declare the I/0
        (
        @pNumberOfRows           INT,
        @pNumberOfElementsPerRow INT,
        @pMinElementwidth        INT,
        @pMaxElementWidth        INT
        )
RETURNS TABLE
     AS
 RETURN
--===== This creates and populates a test table on the fly containing a
     -- sequential column and a randomly generated CSV Parameter column.
 SELECT TOP (@pNumberOfRows) --Controls the number of rows in the test table
        ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum,
        CSV =
        (--==== This creates each CSV
         SELECT CAST(
                    STUFF( --=== STUFF get's rid of the leading comma
                         ( --=== This builds CSV row with a leading comma
                          SELECT TOP (@pNumberOfElementsPerRow) --Controls the number of CSV elements in each row
                                 ','
                               + LEFT(--==== Builds random length variable within element width constraints
                                      LEFT(REPLICATE('1234567890',CEILING(@pMaxElementWidth/10.0)), @pMaxElementWidth),
                                      ABS(CHECKSUM((SELECT MyNewID FROM dbo.iFunction)))
                                            % (@pMaxElementWidth - @pMinElementwidth + 1) + @pMinElementwidth
                                     )
                            FROM sys.All_Columns ac3            --Classic cross join pseudo-cursor
                           CROSS JOIN sys.All_Columns ac4       --can produce row sets up 16 million.
                           WHERE ac3.Object_ID <> ac1.Object_ID --Without this line, all rows would be the same.
                             FOR XML PATH('')
                         )
                    ,1,1,'')
                AS VARCHAR(8000))
        )
   FROM sys.All_Columns ac1      --Classic cross join pseudo-cursor
  CROSS JOIN sys.All_Columns ac2 --can produce row sets up 16 million rows
;
GO
--=====================================================================================================================
--      Conditionally drop and recreate the TestResults table
--=====================================================================================================================
--===== Conditionally drop and create the TestResults table
 IF OBJECT_ID('dbo.TestResults','U') IS NOT NULL DROP TABLE dbo.TestResults;

 CREATE TABLE dbo.TestResults
        (
        RowNum           INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        SplitterName     VARCHAR(50),
        NumberOfRows     INT,
        NumberOfElements INT,
        MinElementLength    INT,
        MaxElementLength    INT,
        Duration         float,
        MinLength        INT,
        AvgLength        INT,
        MaxLength        INT
        );
GO


--=====================================================================================================================
--      Conditionally drop and recreate the stored procedure that tests each function and records the results.
--=====================================================================================================================
--===== Conditionally drop and create the TestResults table
     IF OBJECT_ID('dbo.TestEachFunction','P') IS NOT NULL DROP PROCEDURE dbo.TestEachFunction;
GO
if(object_id('testEachFunction') is not null) drop procedure testeachfunction
GO
CREATE PROCEDURE dbo.TestEachFunction
/**********************************************************************************************************************
 Purpose:
 Given the number of rows and elements this testing is for, the stored procedure will test each of the split function
 for duration and record the results in an table called dbo.TestResults in the current DB (which should be TempDB).

 Revision History:
 Rev 01 - 20 May 2014 - Michael Burbea - Modifed to allow for a bit more dynamic code allowing me to test more functions easily.
 Rev 00 - 10 Apr 2011 - Jeff Moden - Initial release for testing

**********************************************************************************************************************/
--===== Declare the I/O parameters
        @pNumberOfRows     INT,
        @pNumberOfElements INT,
        @pMinElementLength INT,
        @pMaxElementLength INT
     AS

--=====================================================================================================================
--      Presets
--=====================================================================================================================
--===== Suppress the auto-display of rowcounts for appearance and speed
    SET NOCOUNT ON;

--===== Declare some obviously named local variables
DECLARE @StartTime DATETIME,
        @EndTime   DATETIME,
        @Message   SYSNAME,
        @MinLength INT,
        @AvgLength INT,
        @MaxLength INT;

--===== Preset and display the current run message
 SELECT @Message = '========== '
                 + CAST(@pNumberOfRows     AS VARCHAR(10)) + ' Rows, '
                 + CAST(@pMinElementLength AS VARCHAR(10)) + ' MinElementSize, '
                 + CAST(@pMaxElementLength AS VARCHAR(10)) + ' MaxElementSize, '
                 + CAST(@pNumberOfElements AS VARCHAR(10)) + ' Elements '
                 + '==========';

RAISERROR(@Message,10,1) WITH NOWAIT;

--===== Calculate some statistics for the condition of the data
 SELECT @MinLength = MIN(DATALENGTH(CSV)),
        @AvgLength = AVG(DATALENGTH(CSV)),
        @MaxLength = MAX(DATALENGTH(CSV))
   FROM dbo.Csv8K;

--select CONCAT('declare   @pNumberOfRows     INT=100,
--        @pNumberOfElements INT=10,
--        @pMinElementLength INT=5,
--        @pMaxElementLength INT=20,@MinLength int=',@minLength,',@AvgLength int=',@AvgLength,+',@MaxLength int=',@MaxLength)
--=====================================================================================================================
--      Run the tests, By generating a dynamic sproc.
--=====================================================================================================================
DECLARE @STR NVARCHAR(MAX) ='declare @startTime datetime2,@endTime datetime2,@r int,@in int,@v varchar(8000),@n nvarchar(4000),@m nvarchar(max)'+(select
';RAISERROR(''Testing '+c.name+''',10,1) WITH NOWAIT;
DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;

--===== Start the timer
 SELECT @StartTime = sysdatetime();

--===== Run the test
 SELECT @r = csv.RowNum, @in = split.ItemNumber, @'+c.d_type+'= split.Item
   FROM dbo.CSV8K csv
  CROSS APPLY dbo.'+c.name+'(csv.CSV,char(44)'+case when c.chunker=1 then ','+convert(varchar,@pNumberOfElements+1) else '' end+') split

--===== Stop the timer and record the test
 select @EndTime= sysdatetime();

 INSERT INTO dbo.TestResults
        (SplitterName, NumberOfRows, NumberOfElements, MinElementLength, MaxElementLength, Duration, MinLength, AvgLength, MaxLength)
 SELECT '''+c.name+''',
        @pNumberOfRows,
        @pNumberOfElements,
        @pMinElementLength,
        @pMaxElementLength,
        DATEDIFF(microsecond,@StartTime,@EndTime)/1e6,
        MinLength = @MinLength,
        AvgLength = @AvgLength,
        MaxLength = @MaxLength;'
from dbo.functions c
for xml path(''));

select @str=REPLACE(@str,'&#x0D;','')

--select @STR

EXEC SP_EXECUTESQL @str,N'@pNumberOfRows     INT,
        @pNumberOfElements INT,
        @pMinElementLength INT,
        @pMaxElementLength INT,
		@minLength int,
		@avgLength int,
		@maxLength int',@pNumberOfRows=@pNumberOfRows,
        @pNumberOfElements=@pNumberOfElements,
        @pMinElementLength=@pMinElementLength,
        @pMaxElementLength=@pMaxElementLength,
		@minLength=@minLength,
		@avgLength=@avgLength,
		@maxLength=@maxLength

GO
--=====================================================================================================================
--      We're ready to rock.  Now, run all the tests automatically
--=====================================================================================================================
--===== Alert the operator as to how to check the run status

--===== Declare some obviously named variables
DECLARE @SQL VARCHAR(MAX);

--===== Suppress the auto-display of rowcounts
    SET NOCOUNT ON;

--===== Create a "control" CTE and build all of the test commands from that
   WITH cteControl (NumberOfRows, NumberOfElements, MinElementLength, MaxElementLength)
     AS
        (
        --===== 1 to 10 characters per element
         SELECT 1000,    1, 1, 10 UNION ALL
         SELECT 1000,    2, 1, 10 UNION ALL
         SELECT 1000,    4, 1, 10 UNION ALL
         SELECT 1000,    8, 1, 10 UNION ALL
         SELECT 1000,   16, 1, 10 UNION ALL
         SELECT 1000,   32, 1, 10 UNION ALL
         SELECT 1000,   64, 1, 10 UNION ALL
         SELECT 1000,  128, 1, 10 UNION ALL
         SELECT 1000,  256, 1, 10 UNION ALL
         SELECT 1000,  512, 1, 10 UNION ALL
         SELECT 1000, 1150, 1, 10 UNION ALL
        --===== 10 to 20 characters per element
         SELECT 1000,    1, 10, 20 UNION ALL
         SELECT 1000,    2, 10, 20 UNION ALL
         SELECT 1000,    4, 10, 20 UNION ALL
         SELECT 1000,    8, 10, 20 UNION ALL
         SELECT 1000,   16, 10, 20 UNION ALL
         SELECT 1000,   32, 10, 20 UNION ALL
         SELECT 1000,   64, 10, 20 UNION ALL
         SELECT 1000,  128, 10, 20 UNION ALL
         SELECT 1000,  256, 10, 20 UNION ALL
         SELECT 1000,  480, 10, 20 UNION ALL
        --===== 20 to 30 characters per element
         SELECT 1000,    1, 20, 30 UNION ALL
         SELECT 1000,    2, 20, 30 UNION ALL
         SELECT 1000,    4, 20, 30 UNION ALL
         SELECT 1000,    8, 20, 30 UNION ALL
         SELECT 1000,   16, 20, 30 UNION ALL
         SELECT 1000,   32, 20, 30 UNION ALL
         SELECT 1000,   64, 20, 30 UNION ALL
         SELECT 1000,  128, 20, 30 UNION ALL
         SELECT 1000,  256, 20, 30 UNION ALL
         SELECT 1000,  290, 20, 30 UNION ALL
        --===== 30 to 40 characters per element
         SELECT 1000,    1, 30, 40 UNION ALL
         SELECT 1000,    2, 30, 40 UNION ALL
         SELECT 1000,    4, 30, 40 UNION ALL
         SELECT 1000,    8, 30, 40 UNION ALL
         SELECT 1000,   16, 30, 40 UNION ALL
         SELECT 1000,   32, 30, 40 UNION ALL
         SELECT 1000,   64, 30, 40 UNION ALL
         SELECT 1000,  128, 30, 40 UNION ALL
         SELECT 1000,  210, 30, 40 UNION ALL
        --===== 40 to 50 characters per element
         SELECT 1000,    1, 40, 50 UNION ALL
         SELECT 1000,    2, 40, 50 UNION ALL
         SELECT 1000,    4, 40, 50 UNION ALL
         SELECT 1000,    8, 40, 50 UNION ALL
         SELECT 1000,   16, 40, 50 UNION ALL
         SELECT 1000,   32, 40, 50 UNION ALL
         SELECT 1000,   64, 40, 50 UNION ALL
         SELECT 1000,  128, 40, 50 UNION ALL
         SELECT 1000,  165, 40, 50 UNION ALL
		 select 1000, 2000, 01, 05 UNION ALL
		 --===== 90 to 99 characters per element
         SELECT 1000,    1, 90, 99 UNION ALL
         SELECT 1000,    2, 90, 99 UNION ALL
         SELECT 1000,    4, 90, 99 UNION ALL
         SELECT 1000,    8, 90, 99 UNION ALL
         SELECT 1000,   16, 90, 99 UNION ALL
         SELECT 1000,   32, 90, 99 UNION ALL
         SELECT 1000,   64, 90, 99 UNION ALL
         SELECT 1000,   80, 90, 99 UNION ALL
		 SELECT 1000, 4000, 01, 01 UNION ALL
		 select 1000,    2,3000,3500
        )
--===== Dynamically build all of the test commands from the above
 SELECT @SQL = ISNULL(@SQL,'')+
'
IF OBJECT_ID(''dbo.Csv8K'',''U'') IS NOT NULL DROP TABLE dbo.Csv8K;
 SELECT *
   INTO dbo.Csv8K
   FROM dbo.CreateCsv8K
        ('+CAST(NumberOfRows     AS VARCHAR(10))+', '
          +CAST(NumberOfElements AS VARCHAR(10))+', '
          +CAST(MinElementLength AS VARCHAR(10))+', '
          +CAST(MaxElementLength AS VARCHAR(10))+') OPTION (QUERYTRACEON 8690); --# of Rows, # of Elements, MIN element length, MAX element length
   EXEC dbo.TestEachFunction '+CAST(NumberOfRows     AS VARCHAR(10)) +', '
                              +CAST(NumberOfElements AS VARCHAR(10))+', '
                              +CAST(MinElementLength    AS VARCHAR(10))+', '
                              +CAST(MaxElementLength    AS VARCHAR(10))+';
'
   FROM cteControl
--PRINT @SQL
--===== Run the tests
   EXEC (@SQL);

GO
select SplitterName,sum(duration) [total_d],AVG(duration) [avg_d] FROM TestResults group by SplitterName
 SELECT dense_rank() over (order by NumberOfRows,NumberOfElements,MinElementLength,MaxElementLength) [trialSet],
		dense_rank() over (partition by NumberOfRows,NumberOfElements,MinElementLength,MaxElementLength order by duration) [rank_in_set],
 * into #r FROM dbo.TestResults

select r.trialset,r.rank_in_set,r.splittername,r.duration,
		Convert(decimal(8,6),r.duration-r2.Duration) [diffFrom_T-SQLBaseline],r.NumberOfElements,r.MinElementLength,r.MaxElementLength,
r.MinLength,r.MaxLength,r.AvgLength
from #r r
join #r r2
  on r.trialSet = r2.trialset
  and r2.SplitterName = 'SqlBaseline'
order by 1,2


drop table #r
	using System;
	using System.Data;
	using System.Data.SqlClient;
	using System.Data.SqlTypes;
	using Microsoft.SqlServer.Server;
	using System.Collections;

	public partial class UserDefinedFunctions
	{
	class Result
	{
	public int id;
	public int start;
	public int num;
	}
	[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 = "Fill_Result", // The method called by SQL Server to obtain the next row
	TableDefinition = "itemNumber int, start int, num int" // Returned table definition
	)]
	public static IEnumerator HybridNVInternal(
	[SqlFacet(MaxSize = -1, IsFixedLength = false, IsNullable = false)]
	SqlChars chars,
	[SqlFacet(MaxSize = 1, IsFixedLength = true, IsNullable = false)]
	char delimiter)
	{
	if (chars.IsNull) return new Result[0].GetEnumerator();
	return new HybridNVEnumerator(chars.Value, delimiter);
	}

	class HybridNVEnumerator : IEnumerator
	{
	private readonly char[] _chars;
	private readonly char _delim;
	private readonly Result _result = new Result();
	private int _start;

	// Methods
	internal HybridNVEnumerator(char[] chars, char delimiter)
	{
	_chars = chars;
	_delim = delimiter;
	}
	public bool MoveNext()
	{
	if (this._start == -1) return false;
	this._result.id++;
	this._result.start = _start+1;
	int i = this._start;
	for (; i < this._chars.Length; i++)
	{
	if (this._chars[i] == _delim)
	{
	this._result.num =i - _start;
	this._start = i + 1;
	return true;
	}
	}
	this._result.num =i - _start;
	this._start = -1;
	return true;
	}
	void IEnumerator.Reset()
	{
	throw new NotImplementedException();
	}

	public object Current
	{
	get
	{
	return this._result;
	}
	}
	}
	[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 = "Fill_Result", // The method called by SQL Server to obtain the next row
	TableDefinition = "itemNumber int, start int, num int" // Returned table definition
	)
	]
	public static IEnumerator HybridInternal(
	// our interest is smaller varchars.
	// anything beyond 8000 is best off using the normal splitter.
	[SqlFacet(MaxSize = 8000, IsFixedLength = false, IsNullable = false)]
	SqlBytes bytes,
	[SqlFacet(MaxSize = 1, IsFixedLength = true, IsNullable = false)]
	byte delimiter)
	{
	if (bytes.IsNull) return new Result[0].GetEnumerator();
	return new HybridEnumerator(bytes.Value, delimiter);
	}
	class HybridEnumerator : IEnumerator
	{
	private readonly byte[] _bytes;
	private readonly ulong[] _longs;
	private readonly ulong _comparer;
	private readonly Result _result = new Result();
	private int _start;
	private readonly int _length;

	// Methods
	internal HybridEnumerator(byte[] bytes, byte delimiter)
	{
	this._bytes = bytes;
	this._length = bytes.Length;
	// we do this so that we can avoid a spillover scan near the end.
	// in unsafe implementation this would be dangerous as we potentially
	// will be reading more bytes than we should.
	this._longs = new ulong[(_length + 7) / 8];
	Buffer.BlockCopy(bytes, 0, _longs, 0, _length);
	var c = (((ulong)delimiter << 8) + (ulong)delimiter);
	c = (c << 16) + c;
	// comparer is now 8 copies of the original delimiter.
	c \|= (c << 32);
	this._comparer = c;
	}

	public bool MoveNext()
	{
	if (this._start >= this._length) return false;
	int i = this._start;
	var longs = this._longs;
	var comparer = this._comparer;
	var r = this._result;
	r.id++;
	r.start = _start + 1;
	// handle the case where start is not divisible by eight.
	for (; (i & 7) != 0; i++)
	{
	if (i == _length \|\| _bytes[i] == (comparer & 0xFF))
	{
	r.num = i - _start;
	_start = i + 1;
	return true;
	}
	}


	// main loop. We crawl the array 8 bytes at a time.

	for (int j = i / 8; j < longs.Length; j++)
	{
	ulong t1 = longs[j];
	unchecked
	{
	t1 ^= comparer;
	ulong t2 = (t1 - 0x0101010101010101) & ~t1;
	if ((t2 & 0x8080808080808080) != 0)
	{
	i = j * 8;
	// make every case 3 comparison instead of n. Potentially better.
	// This is an unrolled binary search.
	if ((t2 & 0x80808080) == 0)
	{
	i += 4;
	t2 >>= 32;
	}

	if ((t2 & 0x8080) == 0)
	{
	i += 2;
	t2 >>= 16;
	}

	if ((t2 & 0x80) == 0)
	{
	i++;
	}
	r.num = i - _start;
	_start = i + 1;
	return true;
	}
	}
	// no matches found increment by 8
	}
	// no matches at all. Let's return the remaining buffer.
	r.num =(_length - _start);
	_start = _bytes.Length;
	return true;
	}

	void IEnumerator.Reset()
	{
	throw new NotImplementedException();
	}

	public object Current
	{
	get
	{
	return this._result;
	}
	}
	}
	public static void Fill_Result(object obj,out int itemNumber,out int start,out int num)
	{
	var r = (Result)obj;
	itemNumber = r.id;
	start = r.start;
	num = r.num;
	}
	}
	using Microsoft.SqlServer.Server;
	//------------------------------------------------------------------------------
	// <copyright file="CSSqlFunction.cs" company="Microsoft">
	// Copyright (c) Microsoft Corporation. All rights reserved.
	// </copyright>
	//------------------------------------------------------------------------------
	using System;
	using System.Collections;
	using System.Data.SqlTypes;

	public partial class UserDefinedFunctions
	{
	[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 =
	"itemNumber INT, item NVARCHAR(4000)" // Returned table definition
	)
	]
	// 1. SQL Server passes input parameters and receives an enumration object
	public static IEnumerator SplitNVarchar
	(
	[SqlFacet(MaxSize = -1)] SqlChars Input,
	char Delimiter,
	int strategy
	)
	{
	return Input.IsNull ?
	new SplitEnumerator(new char[0], char.MinValue) :
	strategy < 2 ? new SplitEnumerator(Input.Value, Delimiter) :
	(IEnumerator) new CLRStringBaseLineEnumerator(Input.Value,strategy);
	}
	private class CLRStringBaseLineEnumerator : IEnumerator
	{
	private readonly char[] _bytes;
	private int _chunks;
	private int _start;
	private readonly int _length;
	readonly SplitRow _record = new SplitRow();
	internal CLRStringBaseLineEnumerator(char[] bytes, int chunks)
	{
	this._bytes = bytes;
	this._chunks = chunks - 1;
	this._length = 2*(bytes.Length / chunks);
	}


	public object Current
	{
	get { return this._record; }
	}

	public bool MoveNext()
	{
	if (_chunks == 0) return false;
	this._record.Sequence++;
	_record.Item = new char[_length/2];
	Buffer.BlockCopy(_bytes, _start, _record.Item, 0, _length);
	_start += _length;
	_chunks--;
	return true;
	}

	public void Reset()
	{
	throw new NotImplementedException();
	}
	}


	// The enumeration object
	class SplitEnumerator : IEnumerator
	{

	readonly char[] input; // Reference to the string to be split
	readonly char delimiter; // The delimiter character
	int start; // Current search start position
	SplitRow record = new SplitRow(); // Each row to be returned

	// Constructor (called once when the object is created)
	internal SplitEnumerator(char[] Input, char Delimiter)
	{

	input = Input;
	delimiter = Delimiter;
	}

	// Enumerator implementation
	#region IEnumerator Methods

	// 2. SQL Server calls the MoveNext() method on the enumeration object
	public bool MoveNext()
	{
	if (this.start == -1) return false;
	this.record.Sequence++;
	int i = this.start;
	for (; i < this.input.Length; i++)
	{
	if (this.input[i] == this.delimiter)
	{
	this.record.Item = new char[i - this.start];
	Buffer.BlockCopy(this.input, this.start2, this.record.Item, 0, (i - this.start)2);
	this.start = i + 1;
	return true;
	}
	}
	this.record.Item = new char[i - this.start];
	Buffer.BlockCopy(this.input, this.start, this.record.Item, 0, (i - this.start)*2);
	this.start = -1;
	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)
	public object Current
	{
	get { return record; }
	}

	// Required by the IEnumerator interface, but not needed for this implementation
	void IEnumerator.Reset()
	{
	throw new System.NotImplementedException();
	}

	#endregion
	}

	// 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 SqlChars item)
	{
	// The passed-in object is an OutputRecord
	var r = (SplitRow)obj;

	// Set the output parameter values
	sequence = r.Sequence;
	item = new SqlChars(r.Item);
	}

	// Structure used to hold each row
	class SplitRow
	{
	internal int Sequence { get; set; } // Sequence of the element
	internal char[] Item { get; set; } // The element
	}
	};