CTE - How to surpass the limit

Question

CTE - How to surpass the limit

Viewing 15 posts - 16 through 30 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

steveb. (8/26/2010)
Quatrei.X (8/26/2010)
hmmmm...
:hehe: I was planning to create a generic function which gets a comma separated list of words in a string and use CTE to seperate those strings to table rows.
🙂 currently my function is working fine but, if records grow, my function might malfunction in the future.
😀 I know how to do this with loops or XML, but seeing CTE as a much faster option, I might make good use of it... only if it has no limit.
CTE is not really a faster option it is still looping and it will grind to a halt as more data is processed, XML would probably be the best out of those options.
However a much better way is to use a tally table and use set-based logic
http://www.sqlservercentral.com/articles/T-SQL/62867/"> http://www.sqlservercentral.com/articles/T-SQL/62867/

Just to be clear, I believe (based on the code examples up to the post quoted above) that the "CTE" that Steve is referring to is a "recursive" CTE. The cascading cross-joined method that we picked up for Ben-Gan will blow the doors off of a recursive CTE and can actually be faster than a Tally table in some cases. In all cases, it uses nearly zero reads where a recursive CTE generally uses more than 10 times the reads of a While Loop and is almost as slow as a While Loop.

Of course there are some exceptions but most instances of a recursive CTE are VERY RBAR in nature and are actually worse than a While Loop. Even when the process "layered sets" as they do in hierarchies, a properly written While Loop will run faster and with 10 times fewer reads.

--Jeff Moden

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

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

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

shield_21 Ten Centuries Points: 1033 More actions · Answer 2

I'm testing Oleg's code method. The place that most XML methods fall apart in is when there are multiple rows. They do work fast as all get out on a single row, though.
Speaking of testing, if you're going to make claims of speed testing, would you mind posting the code you used to generate the test data and your full test harness so we can verify your findings? Thanks.

@jeff - My apologies for not posting the code earlier.. Instead of generating random comma separated numbers (from my post above), I used numbers arranged in sequence.. I modified DelimitedSplit8K though to accompany string larger than 8000 characters.. Notice that 1 million rows are not enough to completely split the numbers into rows..

--DelimitedSplit8K function

CREATE FUNCTION dbo.DelimitedSplit8K

(

@pString VARCHAR(MAX), --modified data type to VARCHAR(MAX)

@pDelimiter CHAR(1)

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

--===== "Inline" CTE Driven "Tally Table” produces values up to

-- 10,000... enough to cover VARCHAR(8000)

WITH

E1(N) AS ( --=== Create Ten 1's

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

E6(N) AS (SELECT 1 FROM E4 a, E2 b), --added to produce 1,000,000 rows

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E6)

--===== Do the split

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,

SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item

FROM cteTally

WHERE N < LEN(@pString) + 2

AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter

;

GO

--another Split function

CREATE FUNCTION dbo.Split (@string VARCHAR(MAX), @delimiter CHAR(1))

RETURNS TABLE

AS

RETURN (

WITH Temp(id, position1, position2)

AS (

SELECT1, CAST(1 AS INT), CAST(CHARINDEX(@delimiter, @string) AS INT)

UNION ALL

SELECTid + 1, CAST(position2 AS INT) + 1, CAST(CHARINDEX(@delimiter, @string, position2 + 1) AS INT)

FROMTemp

WHEREposition2 > 0

)

SELECT id,SUBSTRING(@string, position1, CASE WHEN position2 = 0 THEN LEN(@string)+1-position1 ELSE position2 - position1 END) AS word

FROM Temp

)

GO

--Oleg's stored procedure

create proc dbo.usp_DelimitedSplit

(

@text nvarchar(max),

@delimiter char(1),

@entitize bit = 1

)

as

begin

declare @xml xml;

if @entitize = 1 set @text = (select @text for xml path(''));

set @xml = '<r>' + replace(@text, @delimiter, '</r><r>') + '</r>';

select

row_number() over (order by (select null)) item_number,

item.value('text()[1]', 'varchar(max)') item_value

from @xml.nodes('//r') R(item);

end;

go

SELECT TOP 180000

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM master.dbo.syscolumns a,

master.dbo.syscolumns b

DECLARE @string VARCHAR(MAX)

SELECT @string = STUFF((SELECT ',' + CAST(n AS VARCHAR) AS [text()]

FROM dbo.Tally

ORDER BY n

FOR XML PATH('')),1,1,'')

SELECT DATALENGTH(@string)

SET STATISTICS TIME ON

--DelimitedSplit8K function

SELECT * FROM dbo.DelimitedSplit8K(@string,',')

--another Split function

SELECT * FROM dbo.Split(@string,',' ) OPTION(MAXRECURSION 0)

--Oleg's stored procedure

EXEC usp_DelimitedSplit @string,',',1

SET STATISTICS TIME OFF

--clean-up

DROP FUNCTION DelimitedSplit8K

DROP FUNCTION Split

DROP PROC usp_DelimitedSplit

DROP TABLE dbo.Tally

I'll be posting the comparison between these methods if string contains less than 8000 characters next time.. I hope this helps.. Thanks! 😀

Quatrei.X SSCrazy Points: 2697 More actions · Answer 3

:w00t: COOL! Thanks a lot everyone.

:hehe: I haven't used SET STATISTICS TIME (ON|OFF) before. Thanks 😀

that's the one you guys used right? or is there a tool or something like execution plan?

and yeah, I agree. steveB's implementation beats mine at quite a margin. I knew he had some reasons why he used that, I just don't know what those reasons were until now. hehehe XD

I'm gonna try all your suggestions

Thanks thanks thanks ^__^

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Quatrei.X (8/30/2010)
:w00t: COOL! Thanks a lot everyone.
:hehe: I haven't used SET STATISTICS TIME (ON|OFF) before. Thanks 😀
that's the one you guys used right? or is there a tool or something like execution plan?
and yeah, I agree. steveB's implementation beats mine at quite a margin. I knew he had some reasons why he used that, I just don't know what those reasons were until now. hehehe XD
I'm gonna try all your suggestions
Thanks thanks thanks ^__^

I use SET STATISTICS TIME and IO both. Just be aware that there are certain places where they may not record everything especially when it comes to certain functions. Before you do something final, check it against SQL Profiler just to be sure.

--Jeff Moden

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

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

I modified the code for the Split8K function further so that it would actually split all the rows thrown at it for shield_21's tests. Here's what it looks like...

--DelimitedSplit8K function

CREATE FUNCTION dbo.DelimitedSplit8K

(

@pString VARCHAR(MAX), --modified data type to VARCHAR(MAX)

@pDelimiter CHAR(1)

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

--===== "Inline" CTE Driven "Tally Table” produces values up to

-- 10,000... enough to cover VARCHAR(8000)

WITH

E1(N) AS ( --=== Create Ten 1's

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

E6(N) AS (SELECT 1 FROM E4 a, E4 b), --added to produce 100,000,000 rows

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E6)

--===== Do the split

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,

SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item

FROM cteTally

WHERE N < LEN(@pString) + 2

AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter

;

GO

The "other" split method is, in fact, faster than the Tally table on my box (48 seconds vs 66 seconds) as, like shield_21 already noted, was stated in the original function whenever VARCHAR(MAX) is brought into play. Tally table methods are bad because the joins to a VARCHAR(MAX) are pretty tough on everything.

I haven't gotten there, yet, but I'm converting Oleg's code to an iTVF so it doesn't slow down so much as a function. I really want to test all 3 methods as <= 8k and as iTVF's.

Hat's off to shield_21 for providing the test code! :w00t:

We've been through similar testing in the past and I will tell you that as fast as the split8K code is when used against 8k material, a very well written SQLCLR will still beat it. If you typically split more than 8k, it's a worth while investment (although Oleg's code looks very promising).

--Jeff Moden

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

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

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

Quatrei.X SSCrazy Points: 2697 More actions · Answer 6

Just noticed something small but not really that important.

rename E6 to E8 ???

just noticed hehehe XD

thanks for the function ^__^

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

shield_21 Ten Centuries Points: 1033 More actions · Answer 7

The "other" split method is, in fact, faster than the Tally table on my box (48 seconds vs 66 seconds) as, like shield_21 already noted, was stated in the original function whenever VARCHAR(MAX) is brought into play. Tally table methods are bad because the joins to a VARCHAR(MAX) are pretty tough on everything.
I haven't gotten there, yet, but I'm converting Oleg's code to an iTVF so it doesn't slow down so much as a function. I really want to test all 3 methods as <= 8k and as iTVF's.
Hat's off to shield_21 for providing the test code! :w00t:
We've been through similar testing in the past and I will tell you that as fast as the split8K code is when used against 8k material, a very well written SQLCLR will still beat it. If you typically split more than 8k, it's a worth while investment (although Oleg's code looks very promising).

@Jeff- Thanks for your nice compliment! 🙂

I would also want to know which is the fastest and efficient way to do this.. I just noticed now, when I set statistics io to on and run split8k function (using VARCHAR(7999) data type), it produces no result.. Does it mean, it has 0 logical reads, physical reads, etc? Wow, amazing!

I also tried converting Oleg's procedure to function.. What I came up is the code below.. It became slower than its stored procedure.. Maybe there's another way to do this.. :hehe:

--code courtesy of Oleg

CREATE FUNCTION dbo.UDF_DelimitedSplit

(

@text VARCHAR(7999),

@delimiter CHAR(1)

)

RETURNS TABLE

AS

RETURN

(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) item_number

,x.item_value

FROM (

SELECT CAST('<r>' + REPLACE((SELECT @text FOR XML PATH('')), @delimiter, '</r><r>') + '</r>' AS XML)

) Temp(xml_item_value)

CROSS APPLY

(

SELECT R.item.value('text()[1]', 'VARCHAR(7999)') item_value

FROM Temp.xml_item_value.nodes('//r') R(item)

) x(item_value)

)

GO

Thanks!

shield_21 Ten Centuries Points: 1033 More actions · Answer 8

Quatrei.X (8/30/2010)
:w00t: COOL! Thanks a lot everyone.
:hehe: I haven't used SET STATISTICS TIME (ON|OFF) before. Thanks 😀
that's the one you guys used right? or is there a tool or something like execution plan?
and yeah, I agree. steveB's implementation beats mine at quite a margin. I knew he had some reasons why he used that, I just don't know what those reasons were until now. hehehe XD
I'm gonna try all your suggestions
Thanks thanks thanks ^__^

It's good to see people learning.. :hehe: There are 3 methods I know to measure performance.. I hope this will help you answer your questions.. 😛 Also, if someone can add/make corrections, I would highly appreciate it..

1. using execution plan - you'll be able to see Query cost(relative to batch) and estimated subtree cost.. The lower the cost, the better the performance..

2. using SET STATISTICS IO ON - The lower the scan count, logical reads, physical reads, the better.. (Jeff already mentioned this)

3. using SET STATISTICS TIME ON - Of course, the lower the CPU time and elapsed time, the better.. An alternative is to get the current date/time, store it in variable, put your query to test, and use DATEDIFF function to get the difference between date/time stored in variable and current date/time(after executing the query).. Similar to this one:

DECLARE @d DATETIME=GETDATE()

--Your query here--

SELECT DATEDIFF(ms,@d,GETDATE())

Of the three, using STATISTICS TIME is the most important because those two are sometimes inaccurate.. They can sometimes "lie" (from ColdCoffee) just like in your code earlier..

There's also tools like SQL Server Profiler and Database Engine Tuning Advisor but they are not available in Express edition.. But they are the best!

I hope this helps..

Paul White SSC Guru Points: 150467 More actions · Answer 9

On my machine, the fastest solution is based on Adam Machanic's excellent CLR string splitter. I include it here for others to compare:

CREATE ASSEMBLY Utility

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103006573CD4B0000000000000000E00002210B0108000010000000200000000000001E2C0000002000000040000000004000002000000010000004000000000000000400000000000000008000000010000000000000030040850000100000100000000010000010000000000000100000000000000000000000C82B000053000000004000002803000000000000000000000000000000000000006000000C000000242B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000240C0000002000000010000000100000000000000000000000000000200000602E7273726300000028030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002C0000000000004800000002000500C4220000600800000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004003A0000000100001100026F1000000A2D1B036F1000000A2D13026F1100000A036F1100000A73040000062B11168D15000001168D1500000173040000060A2B00062A4E0003027417000001731300000A81040000012A1E02281400000A2A0000033003004F0000000000000002281400000A000002037D0300000402038E697D0500000402047D0400000402048E69D27D0600000402027B0600000417FE017D0700000402167D0100000402027B06000004155A7D02000004002A0013300400240000000200001100027B03000004027B01000004027B02000004027B0100000459731500000A0A2B00062A133003005E0100000300001100027B02000004027B05000004FE04130411042D07160D38400100000002027B02000004027B06000004587D01000004027B010000040A38CE00000000170B027B0700000416FE01130411042D1E00027B030000040693027B040000041693FE01130411042D02160B002B4900160C2B3400060858027B050000042F16027B0300000406085893027B040000040893FE012B0116130411042D0500160B2B1500081758D20C08027B06000004FE04130411042DBD000716FE01130411042D460002067D02000004027B02000004027B010000045916FE0216FE01130411042D04170D2B780006027B060000041759580A02257B01000004027B06000004587D010000040000000617580A06027B05000004FE04130411043A20FFFFFF02027B02000004027B06000004587D0100000402027B050000047D02000004027B02000004027B010000045916FE0216FE01130411042D04170D2B04160D2B00092A5E0002167D0100000402027B06000004155A7D020000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000F8020000237E000064030000A003000023537472696E6773000000000407000008000000235553000C0700001000000023475549440000001C0700004401000023426C6F6200000000000000020000015717A2010902000000FA01330016000001000000170000000300000007000000070000000600000001000000150000000E0000000300000001000000010000000100000001000000020000000100000000000A0001000000000006004C0045000600660053000A0093007E000A00AE007E0006004E013C0106008601670106009A013C010600B3013C010600CE013C010600E9013C01060002023C0106001B023C0106003A023C01060057023C01060081026E023F00950200000600C402A4020600E402A4020A0025030A030A0040030A0306007103450006007F03670106008C03450000000000010000000000010001000100100016000000050001000100020010002B0000000500010004000100E7002F000100EF002F002100F700320021000101320021000B012F0021001501360021002201390050200000000096009C000A0001009620000000009600B80013000300AA20000000008618C6001B000500B420000000008618C6001F000500102100000000E609CC0027000700402100000000E601D8002B000700AA2200000000E601E1001B000700000001003A03000002005203000001007603020002007A03000001009303000002005203030009002900C60040003100C60045003900C60040004100C60040004900C60040005100C60040005900C60040006100C60040006900C60040007100C60040007900C6004A008900C60050009100C6001B009900C6001B00A100C6001B0019005C032B0019006703C800B100C6001B002100C60040000900C6001B00B900C600D20020007300550024007B00A2002E00330004012E001300E6002E002300E6002E002B00EC002E004B00E6002E003B00E6002E004300E6002E005B0011012E006B0023012E00530004012E0063001A0144007B00B500CD00DA00DE0003000100000034013C000200050003000480000001000000B20EDA9D0000000000000203000002000000000000000000000001003C000000000002000000000000000000000001007200000000000300020000000000003C4D6F64756C653E005574696C6974792E646C6C0055736572446566696E656446756E6374696F6E730053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C537472696E670046696C6C526F775F4D756C7469002E63746F72006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E740053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005574696C697479004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500496E7075740053716C46616365744174747269627574650044656C696D69746572006765745F49734E756C6C006765745F56616C75650043686172006F626A006974656D004F757441747472696275746500537472696E6700546865537472696E67000000000003200000000000FF14F9E204BA584D9E3C0D188C96F3140008B77A5C561934E0890800021209120D120D070002011C10111103200001072002011D031D030320001C0320000202060803061D030206050206020328001C042001010E042001010205200101114104200101084C01000200540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C7469540E0F5461626C65446566696E6974696F6E136974656D206E7661726368617228343030302912010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65FF0000000420001D030407011209072003011D0308080307011C070705080205020205010000000017010012436F7079726967687420C2A920203230313000000C0100075574696C69747900000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000006573CD4B000000000200000086000000402B0000401B000052534453ACF03399C235134A85389C157969148701000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C4D7920446F63756D656E74735C56697375616C2053747564696F20323030355C50726F6A656374735C5574696C6974795C5574696C6974795C6F626A5C44656275675C5574696C6974792E706462000000F02B000000000000000000000E2C0000002000000000000000000000000000000000000000000000002C000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00200000000000000000000D00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100DA9DB20E00000100DA9DB20E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430020000010053007400720069006E006700460069006C00650049006E0066006F0000000C0200000100300030003000300030003400620030000000380008000100460069006C0065004400650073006300720069007000740069006F006E00000000005500740069006C00690074007900000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700360032002E00340030003400310030000000000038000C00010049006E007400650072006E0061006C004E0061006D00650000005500740069006C006900740079002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005500740069006C006900740079002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D006500000000005500740069006C00690074007900000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700360032002E00340030003400310030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700360032002E003400300034003100300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203C000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE;

GO

CREATE FUNCTION

dbo.SplitString_Multi

(

@Input NVARCHAR(MAX),

@Delimiter NVARCHAR(255)

)

RETURNS TABLE (item NVARCHAR(4000) NULL)

WITH EXECUTE AS CALLER

AS EXTERNAL NAME

Utility.UserDefinedFunctions.SplitString_Multi;

Test:

DECLARE @string VARCHAR(MAX);

SELECT @string =

STUFF

(

SELECT ',' + CONVERT(VARCHAR(11), ABS(CHECKSUM(NEWID())%10000))

FROM sys.all_columns AC

ORDER BY

AC.[object_id],

AC.column_id

FOR XML PATH(''), TYPE

).value('./text()[1]', 'VARCHAR(MAX)')

,1,1, SPACE(0)

);

SELECT DATALENGTH(@string);

SELECT S.item

FROM dbo.SplitString_Multi(@string, N',') S;

You can find the source code here:

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

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

shield_21 (9/1/2010)
I just noticed now, when I set statistics io to on and run split8k function (using VARCHAR(7999) data type), it produces no result.. Does it mean, it has 0 logical reads, physical reads, etc? Wow, amazing!

Unfortunately not. UDFs (scalar for certain, can't recall for the other two) don't reflect in IO stats.

http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 11

Neither scalar nor multi-statement UDFs show in IO stats. In-line TVFs do, because they are expanded into the query (just like a parameterized view would) before optimization.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 12

Paul White NZ (9/1/2010)
On my machine, the fastest solution is based on Adam Machanic's excellent CLR string splitter. I include it here for others to compare:
CREATE ASSEMBLY Utility
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103006573CD4B0000000000000000E00002210B0108000010000000200000000000001E2C0000002000000040000000004000002000000010000004000000000000000400000000000000008000000010000000000000030040850000100000100000000010000010000000000000100000000000000000000000C82B000053000000004000002803000000000000000000000000000000000000006000000C000000242B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000240C0000002000000010000000100000000000000000000000000000200000602E7273726300000028030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002C0000000000004800000002000500C4220000600800000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004003A0000000100001100026F1000000A2D1B036F1000000A2D13026F1100000A036F1100000A73040000062B11168D15000001168D1500000173040000060A2B00062A4E0003027417000001731300000A81040000012A1E02281400000A2A0000033003004F0000000000000002281400000A000002037D0300000402038E697D0500000402047D0400000402048E69D27D0600000402027B0600000417FE017D0700000402167D0100000402027B06000004155A7D02000004002A0013300400240000000200001100027B03000004027B01000004027B02000004027B0100000459731500000A0A2B00062A133003005E0100000300001100027B02000004027B05000004FE04130411042D07160D38400100000002027B02000004027B06000004587D01000004027B010000040A38CE00000000170B027B0700000416FE01130411042D1E00027B030000040693027B040000041693FE01130411042D02160B002B4900160C2B3400060858027B050000042F16027B0300000406085893027B040000040893FE012B0116130411042D0500160B2B1500081758D20C08027B06000004FE04130411042DBD000716FE01130411042D460002067D02000004027B02000004027B010000045916FE0216FE01130411042D04170D2B780006027B060000041759580A02257B01000004027B06000004587D010000040000000617580A06027B05000004FE04130411043A20FFFFFF02027B02000004027B06000004587D0100000402027B050000047D02000004027B02000004027B010000045916FE0216FE01130411042D04170D2B04160D2B00092A5E0002167D0100000402027B06000004155A7D020000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000F8020000237E000064030000A003000023537472696E6773000000000407000008000000235553000C0700001000000023475549440000001C0700004401000023426C6F6200000000000000020000015717A2010902000000FA01330016000001000000170000000300000007000000070000000600000001000000150000000E0000000300000001000000010000000100000001000000020000000100000000000A0001000000000006004C0045000600660053000A0093007E000A00AE007E0006004E013C0106008601670106009A013C010600B3013C010600CE013C010600E9013C01060002023C0106001B023C0106003A023C01060057023C01060081026E023F00950200000600C402A4020600E402A4020A0025030A030A0040030A0306007103450006007F03670106008C03450000000000010000000000010001000100100016000000050001000100020010002B0000000500010004000100E7002F000100EF002F002100F700320021000101320021000B012F0021001501360021002201390050200000000096009C000A0001009620000000009600B80013000300AA20000000008618C6001B000500B420000000008618C6001F000500102100000000E609CC0027000700402100000000E601D8002B000700AA2200000000E601E1001B000700000001003A03000002005203000001007603020002007A03000001009303000002005203030009002900C60040003100C60045003900C60040004100C60040004900C60040005100C60040005900C60040006100C60040006900C60040007100C60040007900C6004A008900C60050009100C6001B009900C6001B00A100C6001B0019005C032B0019006703C800B100C6001B002100C60040000900C6001B00B900C600D20020007300550024007B00A2002E00330004012E001300E6002E002300E6002E002B00EC002E004B00E6002E003B00E6002E004300E6002E005B0011012E006B0023012E00530004012E0063001A0144007B00B500CD00DA00DE0003000100000034013C000200050003000480000001000000B20EDA9D0000000000000203000002000000000000000000000001003C000000000002000000000000000000000001007200000000000300020000000000003C4D6F64756C653E005574696C6974792E646C6C0055736572446566696E656446756E6374696F6E730053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C537472696E670046696C6C526F775F4D756C7469002E63746F72006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E740053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005574696C697479004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500496E7075740053716C46616365744174747269627574650044656C696D69746572006765745F49734E756C6C006765745F56616C75650043686172006F626A006974656D004F757441747472696275746500537472696E6700546865537472696E67000000000003200000000000FF14F9E204BA584D9E3C0D188C96F3140008B77A5C561934E0890800021209120D120D070002011C10111103200001072002011D031D030320001C0320000202060803061D030206050206020328001C042001010E042001010205200101114104200101084C01000200540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C7469540E0F5461626C65446566696E6974696F6E136974656D206E7661726368617228343030302912010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65FF0000000420001D030407011209072003011D0308080307011C070705080205020205010000000017010012436F7079726967687420C2A920203230313000000C0100075574696C69747900000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000006573CD4B000000000200000086000000402B0000401B000052534453ACF03399C235134A85389C157969148701000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C4D7920446F63756D656E74735C56697375616C2053747564696F20323030355C50726F6A656374735C5574696C6974795C5574696C6974795C6F626A5C44656275675C5574696C6974792E706462000000F02B000000000000000000000E2C0000002000000000000000000000000000000000000000000000002C000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00200000000000000000000D00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100DA9DB20E00000100DA9DB20E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430020000010053007400720069006E006700460069006C00650049006E0066006F0000000C0200000100300030003000300030003400620030000000380008000100460069006C0065004400650073006300720069007000740069006F006E00000000005500740069006C00690074007900000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700360032002E00340030003400310030000000000038000C00010049006E007400650072006E0061006C004E0061006D00650000005500740069006C006900740079002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005500740069006C006900740079002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D006500000000005500740069006C00690074007900000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700360032002E00340030003400310030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700360032002E003400300034003100300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203C000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION
dbo.SplitString_Multi
(
@Input NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE (item NVARCHAR(4000) NULL)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME
Utility.UserDefinedFunctions.SplitString_Multi;

Great function Paul:-) Is there a way of adapting it to include a row number and return values for the blank items as well?:cool:

Currently, if I run

SELECT * FROM dbo.SplitString_Multi(',,harry,,fred,,', N',')

it only returns harry and fred but not the blank values and no row number to locate position

Cheers

Steve

Paul White SSC Guru Points: 150467 More actions · Answer 13

steve-893342 (9/1/2010)
Great function Paul:-) Is there a way of adapting it to include a row number and return values for the blank items as well?

Well the credit is Adam's of course, I just use it a lot 🙂

Yes, the modification would be a simple one. I linked to the source code before I think.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 14

Paul White NZ (9/1/2010)
steve-893342 (9/1/2010)
Great function Paul:-) Is there a way of adapting it to include a row number and return values for the blank items as well?
Well the credit is Adam's of course, I just use it a lot 🙂
Yes, the modification would be a simple one. I linked to the source code before I think.

You did indeed. I shall take a look.

Thanks Adam:-)

Paul White SSC Guru Points: 150467 More actions · Answer 15

Steve,

I found a few spare minutes to slightly modify Adam's routine to work the way you want:

CREATE ASSEMBLY Utility

AUTHORIZATION dbo

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103009F5F7F4C0000000000000000E00002210B010800000E000000060000000000000E2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000BC2C00004F000000004000000803000000000000000000000000000000000000006000000C0000001C2C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140D000000200000000E000000020000000000000000000000000000200000602E7273726300000008030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000F02C000000000000480000000200050068220000B40900000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D2026F1300000A2D1A036F1300000A2D12026F1400000A036F1400000A73050000062A168D1A000001168D1A00000173050000062A000000133003002C0000000100001102A5030000020A0312007B01000004731500000A81050000010412007B02000004731600000A81060000012A1E02281700000A2A3E02037D0100000402047D020000042A03300300530000000000000002281700000A02037D0600000402038E697D0800000402047D0700000402048E69D27D0900000402027B0900000417FE017D0A00000402167D0500000402167D0300000402027B09000004155A7D040000042ABE027B05000004027B06000004027B03000004027B04000004027B0300000459731900000A73040000068C030000022A0013300300DB0000000200001102257B0500000417587D05000004027B04000004027B080000043202162A02027B04000004027B09000004587D03000004027B030000040A2B65170B027B0A0000042C16027B060000040693027B0700000416932E39160B2B35160C2B28060858027B080000042F14027B0600000406085893027B0700000408932E04160B2B0E081758D20C08027B0900000432CF072C0902067D04000004172A0617580A06027B08000004329202027B04000004027B09000004587D0300000402027B080000047D04000004027B04000004027B0300000459163102172A162A5A02167D0300000402027B09000004155A7D040000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000080030000237E0000EC0300002804000023537472696E6773000000001408000008000000235553001C0800001000000023475549440000002C0800008801000023426C6F6200000000000000020000015717A2010902000000FA253300160000010000001D000000040000000A00000008000000090000000100000019000000100000000200000001000000010000000100000001000000020000000200000000000A0001000000000006005D005600060064005600060081006E000A00AE0099000A00C90099000A00D20099000A008F0174010600CE01AF0106000502F30106001C02F30106003902F30106005802F30106007102F30106008A02F3010600A502F3010600C002F3010600D902AF010600ED02AF010600FB02F30106001403F3010600440331035700580300000600870367030600A70367030A00D10374010600FB03560006000004AF0106001604AF010600210456000000000001000000000001000100010010001A0000000500010001000B0110002F000000090001000400020010003C0000000500030005000600F00022000600F90025000100190122000100210122000100F0002200210029013E00210033013E0021003D0122002100470142002100540145005020000000009600B7000A0001008820000000009600DC0013000300C020000000008618EA001E000600C820000000008618EA0028000600D820000000008618EA002E000800372100000000E609FE0036000A00682100000000E6010A013A000A004F2200000000E60113011E000A00000001006E0100000200A10100000100AB0102000200F00002000300F90000000100DB0100000200E40100000100E90100000200A10104000D003900EA001E004100EA001E004900EA0072005100EA0072005900EA0072006100EA0072006900EA0072007100EA0072007900EA0072008100EA0072008900EA0077009100EA0072009900EA007200A100EA007200A900EA007C00B900EA008200C100EA001E00C900EA001E002100E6033A002100F103D4002900EA0082003100EA0072000900EA001E00D900EA00DE00E900EA00E40020009300870024000B004C002E003B00F2002E001B00F2002E002300FF002E002B00FF002E003300FF002E0063001D012E008B0066012E004B00FF002E005B00FF002E00430005012E00730047012E007B0054012E0083005D0144000B005F00D900EC0004000100000066014800020006000300048000000100000000000000000000000000C503000002000000000000000000000001004D000000000002000000000000000000000001008D000000000003000200040002000000003C4D6F64756C653E00537472696E675574696C732E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F72640053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C496E7433320053716C537472696E670046696C6C526F775F4D756C7469002E63746F720073657175656E6365006974656D006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E7400496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053657175656E6365004974656D00546865537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500537472696E675574696C730053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E67000003200000000000D459A42BB088194981DD4708D3128E010008B77A5C561934E089080002120D121112110A0003011C1011151011190320000102060802060E05200201080E072002011D031D030320001C0320000203061D030206050206020328001C12010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65FF000000042001010E042001010205200101115904200101084C01000200540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C7469540E0F5461626C65446566696E6974696F6E136974656D206E766172636861722834303030290420001D03040701110C052001011171072003011D0308080507030802050C0100075574696C697479000005010000000017010012436F7079726967687420C2A920203230313000002901002436616266323165302D393063382D343865392D626165362D36666636363966336562653200000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000009F5F7F4C000000000200000084000000382C0000380E00005253445326D0FEDE5ECCC3408D78EA0B0EDDDEE908000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C6D7920646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C5574696C6974795C6F626A5C52656C656173655C537472696E675574696C732E70646200E42C00000000000000000000FE2C0000002000000000000000000000000000000000000000000000F02C0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000B00200000000000000000000B00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00410020000010053007400720069006E006700460069006C00650049006E0066006F000000EC0100000100300030003000300030003400620030000000380008000100460069006C0065004400650073006300720069007000740069006F006E00000000005500740069006C006900740079000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000040001000010049006E007400650072006E0061006C004E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004800100001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D006500000000005500740069006C006900740079000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE;

GO

CREATE FUNCTION dbo.SplitString_Multi

(

@Input NVARCHAR(MAX),

@Delimiter NVARCHAR(255)

)

RETURNS TABLE

(

sequence INTEGER NULL,

item NVARCHAR (4000) NULL

)

WITH EXECUTE AS CALLER

AS EXTERNAL NAME Utility.UserDefinedFunctions.SplitString_Multi;

Source code:

using System;

using System.Collections;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction(

FillRowMethodName = "FillRow_Multi",

TableDefinition = "sequence INTEGER, item NVARCHAR(4000)"

)

]

public static IEnumerator SplitString_Multi(

[SqlFacet(MaxSize = -1)]

SqlChars Input,

[SqlFacet(MaxSize = 255)]

SqlChars Delimiter

)

{

return (

(Input.IsNull || Delimiter.IsNull) ?

new SplitStringMulti(new char[0], new char[0]) :

new SplitStringMulti(Input.Value, Delimiter.Value));

}

private struct OutputRecord

{

public int sequence;

public string item;

public OutputRecord(int Sequence, string Item)

{

this.sequence = Sequence;

this.item = Item;

}

public static void FillRow_Multi(object obj, out SqlInt32 sequence, out SqlString item)

{

OutputRecord r = (OutputRecord)obj;

sequence = new SqlInt32(r.sequence);

item = new SqlString(r.item);

}

public class SplitStringMulti : IEnumerator

{

public SplitStringMulti(char[] TheString, char[] Delimiter)

{

theString = TheString;

stringLen = TheString.Length;

delimiter = Delimiter;

delimiterLen = (byte)(Delimiter.Length);

isSingleCharDelim = (delimiterLen == 1);

sequence = 0;

lastPos = 0;

nextPos = delimiterLen * -1;

}

#region IEnumerator Members

public object Current

{

get

{

return new OutputRecord(sequence, new string(theString, lastPos, nextPos - lastPos));

}

public bool MoveNext()

{

sequence++;

if (nextPos >= stringLen)

return false;

else

{

lastPos = nextPos + delimiterLen;

for (int i = lastPos; i < stringLen; i++)

{

bool matches = true;

//Optimize for single-character delimiters

if (isSingleCharDelim)

{

if (theString != delimiter[0])

matches = false;

}

else

{

for (byte j = 0; j < delimiterLen; j++)

{

if (((i + j) >= stringLen) || (theString != delimiter[j]))

{

matches = false;

break;

}

if (matches)

{

nextPos = i;

return true;

}

lastPos = nextPos + delimiterLen;

nextPos = stringLen;

if ((nextPos - lastPos) > 0)

return true;

else

return false;

}

public void Reset()

{

lastPos = 0;

nextPos = delimiterLen * -1;

}

#endregion

private int lastPos;

private int nextPos;

private int sequence;

private readonly char[] theString;

private readonly char[] delimiter;

private readonly int stringLen;

private readonly byte delimiterLen;

private readonly bool isSingleCharDelim;

}

};

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi