April 15, 2015 at 2:12 pm
Sean Lange (4/15/2015)
Eirikur Eiriksson (4/14/2015)
Sean Lange (4/14/2015)
Eirikur Eiriksson (4/14/2015)
ztoddw (4/14/2015)
This way is probably more efficient-
declare @STRING varchar(1000);set @STRING='100010111100010001';select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')))+n-1))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and substring(@STRING,n,1)='0'
Out of curiosity, why do you think this code is efficient?
😎
It does produce a far simpler execution plan. I didn't do any load testing but it might be more efficient than it appears at a glance.
Hope I'm not sounding arrogant, maybe I phrased the question in a clumsy way. Maybe there is something in
ztoddw's code which I can learn from!
The plan produced from the Tally/DelimitedSplit type code is always looking more complex than it is execution wise, one has to read between the lines here;-)
😎
Very true. The plan is not always a solid indication of performance. I hadn't looked at the actual performance and didn't notice the inaccuracy that Jeff did. I like your approach as I find it much easier to read and seems a bit simpler to me. 🙂
Thanks Sean, appearance does count:-D
I've been cough out few times making an hasty assumption based on calculated cost........\o/.......
😎
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply