| Author |
 Topic  |
|
Kristen
Test
United Kingdom
22191 Posts |
 Posted - 05/08/2007 : 12:32:39
|
Is there a way to get your dynamically created Tally table to provide the split-values in order?
I replaced your dynamical-tally-table with my "permanent" Tally table, and could Order By that to get the values in order.
But the performance of a permanent table was no better, so strikes me that it is easier for code I want to use on client sites to have a no-table-required method. But I do have a need for ordering of the Values.
Thanks
Kristen |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/08/2007 : 12:45:21
|
A trailing ORDER BY seems to work.WHERE w.i = CHARINDEX(',', ',' + @List + ',', w.i) AND w.i <= LEN(',' + @List)
ORDER BY w.i
Peter Larsson
Helsingborg, Sweden |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/08/2007 : 12:52:13
|
Hmmm .. tried that and it didn't work. I'll go back and recheck my code, I expect I've Rogered something!
EDIT: Yeah, for some reason my Pea-sized brain expected the Values to be in ascending order, which would, of course, make the need for Item-ordering redundant.
Brain now back in gear, thanks!
Kristen |
Edited by - Kristen on 05/08/2007 13:04:49 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/08/2007 : 13:52:09
|
Revised version of my function, earlier in this thread, using Peso's code. This is MUCH faster than the original I posted.
--
PRINT 'Create function KK_FN_DelimSplit_V3'
PRINT '*** NOTE: KK_FN_DelimSplit_V3 requires a Tally table. See the source code for details ***'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[KK_FN_DelimSplit_V3]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[KK_FN_DelimSplit_V3]
GO
CREATE FUNCTION dbo.KK_FN_DelimSplit_V3
(
@strSource varchar(8000),
@strDelimiter varchar(10) = ',',
@strBlank varchar(8000) = '[DELETE]' -- What to return if an item is blank? (e.g. '', NULL or '[BLANK]')
-- Use '[DELETE]' to delete any NULL items NOTE: This will leave gaps in [Item] sequence
)
RETURNS @tblArray TABLE
(
Item int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Value varchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
/* WITH ENCRYPTION */
AS
/*
* KK_FN_DelimSplit_V3 Split a string based on delimiter
*
* Example: SELECT Item, Value FROM dbo.KK_FN_DelimSplit_V3('A,B,C', ',', NULL)
*
* Returns:
*
* Resultset of Item/Value pairs
*
* HISTORY:
*
* 04-Sep-2003 KBM V2 - Changed Item/Value to Value/Value. Fixed bug final value was empty - e.g. "A,B,C," [Value 4 should be blank, not absent]
* 05-Oct-2003 KBM Increased size of "VALUE" and other columns
* 21-Apr-2004 KBM Converted to use Tally table, for speed, and to fix bug when using SPACE as delimiter
* Based on article: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1092
* Does NOT catch a blank final value
* 17-Jul-2004 KBM Fixed "final value blank" bug, based on Joe Celko's article
* http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20675
* Does NOT catch single blank values with MULTI-character delimiters
* 08-May-2007 KBM V3 - Updated with code from Peso (much faster)
* http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
*/
BEGIN
DECLARE @intLen int, -- Length of the source string
@intNewDelimiter int
IF DATALENGTH(@strDelimiter) > 1
BEGIN
-- Cannot safely handle BLANK fields with multi-character delimiter
-- Use single character delimiter instead
-- Find lowest character NOT used in @strSource
-- NOTE: This needs reprogramming if permanent Tally table not available
SELECT @intNewDelimiter = MIN(mb_int_ID)
FROM dbo.kk_MB_INT_Integer
WHERE @strSource NOT LIKE '%' + CHAR(mb_int_ID) + '%'
SELECT @strSource = REPLACE(@strSource, @strDelimiter, CHAR(@intNewDelimiter)),
@strDelimiter = CHAR(@intNewDelimiter)
END
SELECT @intLen = DATALENGTH(@strSource) + DATALENGTH(@strDelimiter)
-- Surround string with delimiters to make processing easier
SELECT @strSource = @strDelimiter + @strSource + @strDelimiter
-- Split string using Tally table and Delimiters
INSERT @tblArray(Value)
SELECT
[Value] = COALESCE(
NullIf(
SUBSTRING
(
@strSource,
w.mb_int_ID + 1,
CHARINDEX(@strDelimiter, @strSource, w.mb_int_ID + 1) - w.mb_int_ID - 1
),
''),
@strBlank) -- Convert blank items to user-supplied value
--
/**
-- NOTE: This can be used INSTEAD of a permanent Tally table
FROM
(
SELECT v0.n + v1.n + v2.n + v3.n AS mb_int_ID
FROM
(
SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
)v0,
(
SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48
UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112
UNION ALL SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176
UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) v1,
(
SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768
UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792
UNION ALL SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816
UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
) v2,
(
SELECT 0 n UNION ALL SELECT 4096
) v3
) w
**/
FROM dbo.kk_MB_INT_Integer AS w
--
WHERE w.mb_int_ID = CHARINDEX(@strDelimiter, @strSource, w.mb_int_ID)
AND w.mb_int_ID <= @intLen
IF @strBlank = '[DELETE]'
BEGIN
DELETE D
FROM @tblArray AS D
WHERE [Value] = '[DELETE]'
END
RETURN
/*
-- Example:
DECLARE @strString varchar(8000)
SELECT @strString = '1,2,,4'
SELECT *
FROM dbo.KK_FN_DelimSplit_V3(@strString, ',', '[BLANK]')
-- ===== Create Tally Table (Run from here to marker below) ==== --
SET NOCOUNT OFF
CREATE TABLE dbo.kk_MB_INT_Integer
(
mb_int_ID int NOT NULL,
CONSTRAINT [PK_kk_MB_INT_Integer] PRIMARY KEY CLUSTERED
(
[mb_int_ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
)
DECLARE @v sql_variant
SET @v = N'List of integers for reference / joins'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'kk_MB_INT_Integer', NULL, NULL
SET @v = N'ID number'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'kk_MB_INT_Integer', N'column', N'mb_int_ID'
-- sp_indexoption cannot be executed within a transaction block
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowrowlocks', TRUE
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowpagelocks', TRUE
SET NOCOUNT ON
TRUNCATE TABLE kk_MB_INT_Integer
DECLARE @I int
SELECT @I = 1
WHILE @I <= 8000 -- this value should be the upper limit on the string length
BEGIN
INSERT dbo.kk_MB_INT_Integer(mb_int_ID) VALUES (@I)
SELECT @I = @I+1
END
SET NOCOUNT OFF
-- ===== Create Tally Table -- ** RUN TO HERE ** ==== --
*/
END
--================== KK_FN_DelimSplit_V3 ==================--
GO
PRINT 'Create function KK_FN_DelimSplit_V3 - DONE'
GO
--
EDIT: 05Jul2007 Changed "UNION" to "UNION ALL" 
Kristen |
Edited by - Kristen on 07/05/2007 06:14:35 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/08/2007 : 13:59:10
|
I ran some tests based on 6, 20 and 1,372 items to be split.
6 items:
MindsDoor, 7thNight, PesoParse, PesoParseList and KK_FN_DelimSplit_V3 very fast (average 0-2ms)
Peso and my original "slower" at 22-26ms
20 items:
MindsDoor, 7thNight, PesoParse and KK_FN_DelimSplit_V3 very fast (average 2-5ms)
PesoParseList 7ms
Peso and my original "slower" at 24-30ms
1,372 items:
57ms Peso
90ms KK_FN_DelimSplit_V3
153ms MindsDoor
157ms PesoParse
168ms 7thNight
171ms PesoParseList
2,113ms my original 
EDIT: all were wrapped in a function, except for "Peso" which was inline SQL
Kristen |
Edited by - Kristen on 08/29/2007 10:14:39 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/08/2007 : 14:51:08
|
And while you are already timetesting...
How long time do fnParseList take?
Peter Larsson
Helsingborg, Sweden |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/08/2007 : 15:07:15
|
"Did you clean the buffers and the cache between each test?"
Nah, in the Real World I'd be looking for something that could cache well .......... </thud!>
"How long time do fnParseList take?"
See above
Kristen |
|
|
|
saypaulsay
Starting Member
1 Posts |
Posted - 08/23/2007 : 22:30:25
|
Seventhnight - I like ths simplicity of you function,
However I noticed that if the delimiter (@SplitOn) is greater than one character the results anr't correct.
I beleive the following change is required.
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+ len(@SplitOn),len(@RowData))
|
Edited by - saypaulsay on 08/23/2007 22:33:24 |
|
|
|
Amberite
Starting Member
11 Posts |
Posted - 08/29/2007 : 09:27:17
|
Here's mine. From what I can see it's fast, but I've never profiled it so don't know exactly how fast. It's nice and simple tho :)
CREATE FUNCTION [dbo].[Split](
@String nvarchar(4000),
@Delimiter char(1)
)
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
SET @String = LTRIM(RTRIM(@String))
IF LEN(@STRING) = 0
RETURN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
SELECT @INDEX = 1
WHILE @INDEX !=0 BEGIN
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
INSERT INTO @Results(Items) VALUES(@SLICE)
SELECT @STRING = LTRIM(RIGHT(@STRING,LEN(@STRING) - @INDEX))
IF LEN(@STRING) = 0 BREAK
END
RETURN
END |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 08/29/2007 : 09:54:33
|
"From what I can see it's fast, but I've never profiled it so don't know exactly how fast"
Fails to return blank item
6 items: 3 ms (fast)
20 items: 16 ms (slow-ish)
1,372 items: 126 ms - but only returned 685 items due to Nvarchar use
Changing the code to use Varchar gives 184 ms runtime. (Very slow)
Which basically confirms my earlier post, which is that for very few items in the list there is not much issue which method you use, but for potentially large lists more sophistication is required.
My Split function had been tucked away, used for years, used whenever we needed to Split anything, and then I discovered it was a performance bottleneck by accident once it had gotten used for splitting large lists!
Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/29/2007 : 09:58:20
|
Kristen, do you have the time to update the timing list above, with the newer algorithm for my fnParseList function?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 08/29/2007 : 10:15:23
|
No problem, still had the code open in Query Analyser. See re-edited reply above
Kristen |
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/29/2007 : 10:18:30
|
Here is a new twist.... 
DECLARE @List VARCHAR(8000)
SELECT @List = '48140,20657,58498,23214,52654,35893,46293,50857,43178,15358,12264,20740,22471,41027,54124,12077,41778,42742,31297,59558,15767,
29811,20197,49974,30946,39817,45633,20406,42798,47539,10247,22964,23436,23002,64896,28673,519,11792,19720,51803,2474,31876,53467,13988,41651,
31539,61574,30380,4680,41416,32298,29211,4932,39663,17711,2695,53396,31665,12623,49963,18363,40433,34560,51717,58325,64202,41670,34176,17481,
52041,21506,42409,36727,58633,25348,17062,3201,20082,32850,10795,17022,62619,237,49572,8698,29853,56878,12639,174,3646,352,906,43260,40648,
46480,29925,53300,20477,5506,21170,1654,26256,13617,54804,13308,57525,60672,16959,31783,49832,42510,29375,11160,49093,63172,62040,12613,30329,
55514,41024,39903,3851,56579,26818,41066,51959,58165,31309,56212,56662,47152,1829,13443,55987,24778,30559,335,44668,50715,56430,22070,55921,
35165,26688,25528,2190,43245,30648,59909,20047,41508,29823,39864,4605,41219,6346,48447,39550,37938,17588,20397,33193,8466,26268,48586,54978,
61352,27407,10517,38414,47999,19452,6161,60057,60822,1938,46309,59536,25270,10243,36480,29884,14176,29763,42165,59956,46268,32467,47864,30998,
11022,1794,26103,4005,46500,40854,46525,5852,62387,52350,30760,59745,16203,50949,2444,64783,42423,32179,21599,45821,34080,9392,36093,26023,
18670,12661,21848,7885,1007,62044,34166,54422,43304,42898,24027,35776,39571,62917,32213,4545,19268,12094,28774,50849,21825,25582,34353,22803,
52008,15796,34976,31740,10632,18471,28235,32108,54056,39046,40740,25605,10640,56747,59990,51101,53994,11648,48843,58598,30988,59659,42284,38430,
57696,44839,718,63917,27253,56039,42597,63663,21900,24794,10975,13379,37975,53493,24157,51290,5029,56864,26993,46991,16563,10442,47381,6164,
61768,9018,13732,16989,4914,12692,63130,22179,13104,50164,14878,46155,39550,50478,49530,59175,7145,1927,23120,58045,62023,21114,11847,24895,
47326,43448,55261,22505,26303,47170,38072,15787,32634,38156,30595,27392,32591,2780,9805,21224,25205,8704,49581,20396,42060,47670,44142,27557,
17116,8717,18909,4570,16474,61056,30963,8402,3772,24932,12706,62569,20919,64961,41644,64724,36479,35622,8591,26176,10572,42800,43250,16157,
25189,49495,1421,6627,54470,34037,33228,20002,46676,52340,35102,22302,3123,57357,35736,9673,38055,4787,4833,35257,15576,12549,54459,63430,
21994,55555,13250,7737,4720,15162,30590,7223,28704,54516,38034,36693,38110,22523,43552,11427,47079,42744,17162,42154,48799,22590,22695,18274,
2197,12790,53432,61019,60994,48877,17957,29689,31244,31276,3382,44449,30604,31716,39664,18341,54899,17012,4953,22545,10500,27082,47623,46012,
50355,54417,42875,34001,64956,42702,33244,47070,34314,21875,59159,46125,2884,2909,27886,24004,470,12848,15175,57425,51115,34386,51730,10059,
16361,32821,59434,11421,27565,48927,57457,25647,50271,34572,9023,35187,3004,11686,44674,41837,27200,29626,44629,7217,14985,35156,7493,25450,
14336,34760,50750,34545,23310,36271,23606,6265,22009,8132,787,24166,10138,28418,58766,57088,33531,37491,22493,28841,1085,59926,22172,54255,
22583,34563,30849,41227,26752,5940,14151,34218,60712,52233,31798,6697,51257,15183,38581,56414,41088,31212,4389,2395,49993,9379,13350,62655,
24449,41643,25125,26222,19944,22803,20855,62100,13729,7647,49211,29744,3522,23763,41877,22468,4278,64463,63606,29262,42140,58002,9081,16095,
18874,18878,353,41573,16920,48713,36772,30776,54156,45707,23986,16548,43046,22627,58781,23405,30920,34493,993,48145,30480,39142,37345,45858,
38948,14366,44018,49051,29779,18469,4676,29544,105,25737,62088,24099,15408,10642,9989,15739,1084,870,11539,12337,62223,45445,36461,38767,6818,
38108,30726,23925,14119,56332,32512,25204,29384,1525,11301,12385,20651,23746,57719,6803,60412,47150,12117,2324,24178,11530,15338,19960,44650,
4650,57252,32115,49655,10954,19060,22705,16499,54794,14865,52352,30178,23219,23564,42435,38081,62657,17567,19739,42707,12612,44026,15608,19041,
59985,51807,57642,2523,38643,60679,40238,34712,15131,38362,1604,8151,60214,13212,18501,1163,22820,10857,41067,54976,13997,43211,42760,46907,
41333,9595,5705,60597,50718,4204,32655,16879,39427,44335,54766,33892,21411,23855,26602,51646,12797,22151,46903,4833,51972,41061,62881,10482,
38046,43297,50713,50863,44604,25235,19030,4004,49817,54246,547,52017,20177,48889,63964,36929,53201,48432,65036,22232,1136,26024,53259,63836,
50672,5239,30507,8924,37226,65207,58378,12092,19760,31433,38580,31014,14881,49650,65479,42636,14859,28556,43490,40741,51506,3977,35074,40606,
58279,45496,13154,55321,41791,19393,29253,34963,9863,62359,23571,64394,33739,8592,18682,43579,20426,16525,19264,51291,38648,45250,28148,12296,
22920,15112,51873,2589,39646,49371,42734,48684,4279,38994,8238,31755,4269,40467,34997,12306,3833,15586,35702,23311,20071,35142,36630,606,39467,
23304,23832,10572,57603,25603,53563,61716,62631,52033,31672,14617,16758,34092,21687,30280,47039,28908,11112,11687,40465,29949,45544,5549,11091,
39825,25365,48736,59362,32526,25790,47371,49224,63875,46952,27648,51075,62992,32679,8781,25975,32768,22599,18354,49118,22254,27732,24546,55181,
51734,63135,31800,20876,33730,44096,28487,23397,61589,21794,13118,3260,35985,16651,36597,39349,6806,60409,46108,34716,2970,5878,22999,26442,
43293,39148,41395,42941,37415,51674,20531,11619,59250,34939,35214,48717,65512,24676,65304,65298,53598,34902,42062,40583,5614,53169,29841,62066,
32833,11382,2399,37655,5132,56984,42155,33074,20523,19122,22778,61785,14952,14798,41926,34204,48531,34384,27291,31659,40954,25675,26245,50292,
18261,8567,35287,12374,56498,23143,42552,27328,23451,26437,2454,33270,10655,44330,61050,39821,42408,9052,6474,50433,24363,4640,63739,33909,
20056,13325,18866,4638,17087,29477,5272,42307,28951,35369,25153,33542,21179,34131,31697,3758,13845,11148,24029,1778,54906,34042,43883,7304,
36597,35,20536,26404,54716,57770,39490,48634,4874,17826,14565,9714,8775,36099,19628,19274,7082,22101,44787,15268,890,8842,13311,38372,56534,
45378,48946,29516,35917,59003,56099,26007,15964,25915,9396,5826,29786,17528,13493,22402,4424,57524,55715,55952,15378,46813,41793,52169,35267,
33047,6049,41355,13761,12769,53503,41717,364,39952,27127,9082,18802,5276,4564,43661,45486,46179,35678,40695,64682,15596,50386,2301,1099,18590,
7485,58143,1080,16784,17516,47023,9561,23642,18128,4330,49731,1904,912,20597,48319,12137,47836,44280,19102,57819,12782,11217,33607,62977,62972,
60781,59660,11955,5102,19477,29952,6869,43405,59785,52105,23466,11823,14328,3216,47423,16260,32802,6182,43724,25613,24610,15038,2839,8174,42775,
36959,3209,39458,37711,31823,5796,2269,5454,24794,29945,62945,43823,19705,21764,4003,41217,802,22181,8178,9838,2550,45681,27034,31406,28832,1707,
10934,26990,26347,49658,16033,10285,3927,46291,37667,13427,52224,17043,31052,27247,53642,13277,34813,17446,61633,43916,42242,55875,26647,34044,
2378,25010,55856,3719,62976,3704,60749,32704,14220,2831,20650,21586,8744,50131,3782,5910,35419,44788,51707,22017,38230,51853,13606,15108,13028,
4137,51482,5704,16906,40276,41144,23244,21423,35597,20831,5666,52732,26261,58525,39337,57454,47287,8474,27670,31217,61810,42422,57310,57710,
63390,536,21609,47257,5997,2660,49435,64846,15946,57879,16030,8707,62999,33743,7712,15318,3868,30036,25908,53445,47326,10151,7450,55420,47317,
44446,53536,62616,64905,22850,6515,13172,1872,1597,41657,30386,23610,29017,62404,22310,54102,58415,32873,4987,20649,28813,26354,9749,29386,61736,
40524,50944,23077,61724,49368,19045,26966,54686,39504,63426,21023,64393,46255,3530,24386,26473,65050,10528,2877,11322,52375,60032,16385,52254,
24555,35964,49983,44036,38770,61068,65272,8111,12168,15360,1848,27399,62640,14768,663,19020,44574,40952,45422,22829,21244,43901,60351,7423,4527,
52666,36379,64039,2241,14624,64746,39850,12549,31067,63411,23819,21650,19735,15780,54991,23913,14304,1993,23874,34848,59841,9255,1921,42515,
61292,53664,11141,29928,61218,61484,18978,59204,16314,54182,25147,50478,32753,1157,16703,11543,64003,62905,40819,27562,11674,30798,46372,59448,
11729,42063,49181,38359,19966,57807,27123,55981,5787,8273,8543,45318,4813,4958,54679,54383,4873,63315,974,11223,16200,32777,37284,27554,9745,
37556,42067,10325,43202,32778,53596,62273,42683,5893,43136,60737,20967,56205,34396,37284,55267,61410,12322'
select 1 + len(@list) - len(replace(@list, ',', '')) 'items in list', len(@list) 'string length'
declare @start datetime
Declare @sampleData varchar(8000)
Set @SampleData = 'slkj,dskjhd,sdkjhds,dshd,dweh2,23,323ids,33j4hkds,3j4hk3ds,3j4hko3s,dr3oisd,js,3io3djoe,ejroeifsjhdf,dsfo'
Set @SampleData = '1,2,34,56,7,8,99,123,4,56,7890,'
Set @SampleData = @List
select @start = getdate()
Declare @steps table (step int, data varchar(7950))
Declare @step int
Set @step = 0
If Charindex(',',@sampleData, convert(int,len(@sampleData)/2-len(@sampleData)/2*.25)) > 0
Insert Into @steps
Select @step, ltrim(rtrim(Substring(@sampleData,1,Charindex(',',@sampleData,convert(int,len(@sampleData)/2-len(@sampleData)/2*.25))-1)))
Union All
Select @step, ltrim(rtrim(Substring(@sampleData,Charindex(',',@sampleData,convert(int,len(@sampleData)/2-len(@sampleData)/2*.25))+Len(','),8000)))
Set @step = @step + 1
While (@step < 100 and exists(Select * From @steps Where step = @step-1 and data like '%'+','+'%'))
Begin
Insert Into @steps
Select
step = @step,
data = ltrim(rtrim(Substring(data,1,Charindex(',',data,convert(int,len(data)/2-len(data)/2*.25))-1)))
From @steps
Where step = @step-1
and Charindex(',',data,convert(int,len(data)/2-len(data)/2*.25)) > 0
Union All
Select
step = @step,
data = ltrim(rtrim(Substring(data,Charindex(',',data,convert(int,len(data)/2-len(data)/2*.25))+Len(','),8000)))
From @steps
Where step = @step-1
and Charindex(',',data,convert(int,len(data)/2-len(data)/2*.25)) > 0
Set @Step = @Step + 1
End
Select *
From @steps
Where data not like '%'+','+'%'
Order By 2
select datediff(ms, @start, getdate()) milliseconds
Corey
snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/03/2007 : 08:07:11
|
"Time to add Ranganath's SQL Server 2005 specific now?"
performance is terrible on long lists - several seconds instead of 100ms or so for previous methods
I thought I saw a CROSS JOIN solution posted somewhere that was purported to be fast?
Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/03/2007 : 08:23:27
|
quote:
Originally posted by Kristen
-- sp_indexoption cannot be executed within a transaction block
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowrowlocks', TRUE
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowpagelocks', TRUE
What is the lifetime expectancy for those settings?
- Always
- Reset when service is restarted
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/03/2007 : 08:29:20
|
"What is the lifetime expectancy for those settings?"
No idea mate. I create that Numbers Tally table years ago ... they might still be in force, on the original server I used, for all I know!
I do remember there being some problem creating the Tally table without them ... can't quite understand why 'coz its just "insert 8,000 rows", which is hardly that special ...
Kristen
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/03/2007 : 08:39:32
|
What I understand from Books Online, you tell SQL Server to never use any locks on this "table" when referencing it in a JOIN or any other type.
Which is now deprecated with the ALTER INDEX statement, according to Books Online.
E 12°55'05.25"
N 56°04'39.16" |
Edited by - SwePeso on 09/03/2007 08:42:59 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/03/2007 : 09:08:22
|
"never use any locks on this "table""
Ah ... well that does seem appropriate. The Tally entries aren't going to change - although I could extend the number of rows in the table, in the future, of course ...
Kristen |
|
|
|
Topic |
|
|
|
Best split function
