October 15, 2013 at 7:38 am
Th. Fuchs (10/14/2013)
---- drop the recursion into a static tableif object_id('dbo._numbers') is null --- drop table dbo._numbers
begin
print 'create static collection of numbers'
create table dbo._numbers (n integer primary key(n))
declare @i integer = 0
set nocount on
while @i <= 214748 --3647 -- the hidden recursion
begin insert into dbo._numbers(n) values(@i) select @i += 1 end
end
select @start = getdate()
select [substring] = substring(@p,
case when n = 0 then 0 else n + 1 end, -- start of token
isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)) - n +
case when n = 0 then 0 -- start at position 0
when charindex(',', @p, n + 1) = 0 then 0 -- the last token
else -1 end -- len of token - len(delimiter)
)
from dbo._numbers where n = 0 or substring(@p, n, 1) = ','
---- or hide recursion in the stack (attention, max 31 item pssible)
create function dbo.stt(@source varchar(8000)) returns @t table (t varchar(8000)) as
begin
declare @token varchar(8000), @l integer
select @l = @@nestlevel
if @source like '%,%'
begin -- token exists
select @token = left(@source, charindex(',', @source) -1) -- cut first token
select @source = right(@source, len(@source) - len(@token) -1) -- trimm tail
insert into @t(t) values(@token) -- the one token found into resultset
insert into @t(t) select t from dbo.stt(@source) -- the same procedure for the rest THE RECURSION
end -- first token cutted
else
begin -- last token found
insert into @t(t) values(@source) -- the last feather
end -- now ready
return
end
If you read the title of this post, the OP wanted to be able to do this without a loop or CTE. The first script you have above uses a loop. The second script avoids an explicit loop but has the same problem as a loop insofar as being RBAR, not to mention the recursive call limit you identified.
--Jeff Moden
October 15, 2013 at 7:55 am
I've been trying to figure out why you wanted to avoid a CTE for this and I believe I've come up with the answer. Since you're on a quest for knowledge, I thought I'd throw some additional info at you on the subject.
There are two different types of CTEs... recursive and non-recursive. Recursive CTEs (rCTE) that count out a sequence of numbers are horrible for performance and resource usage. You can find out more about that in the following article.
[font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's
[/font][/url]
The bottom line is that you should pretty much never use a recursive CTE that counts.
Then, there's a form of non-recursive CTE first documented by Itzik Ben-Gan. I don't know what anyone else calls it but I call it a "Cascading CTE" or "cCTE" for short. This is a very high performance, low resource usage method for generating sequences of numbers for splitting delimited values and a whole bunch of other uses. You can see such a cCTE in action in the DelimitedSplit8K function that you were previously directed to. It's nearly as fast as using a Tally Table and uses zero reads. You can find out more about both a cCTE and a Tally Table and how they are both high performance replacements for certain types of loops in the following article. DO read the intro to that article which explains that the "splitter method" in the article is ineffecient when it comes to splitting delimited values but was included because it easily explains how a Tally Table works.
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
[/font][/url]
Getting back to the original subject, it's flat out dangerous to create dynamic SQL from character based parameters that came from "the public" because those parameters can easily contain SQL Injection methods as I previously demonstrated on your other thread. If you haven't Googled "SQL Injection" and done a bit of a study on the subject, you could be leading the folks you're working for into a hack-attack.
If you have any other questions, please don't hesitate to ask.
--Jeff Moden
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply