January 16, 2015 at 1:03 pm
OK, I'm stuck.
I wanted to create random text. I created the following query
CREATE TABLE #TestString(AString varchar(255))
GO
BEGIN TRANSACTION
DECLARE @RunFor tinyint
DECLARE @MyString varchar(100) = ''
SET @RunFor = 1 + (ABS(CHECKSUM(NewID())) % 255)
SELECT @MyString = @MyString + CASE WHEN N % 10 = 0 THEN ' ' ELSE CHAR(32 + (ABS(CHECKSUM(NEWID())) % 95)) END
FROM Tally
WHERE N <= @RunFor
INSERT INTO #TestString
VALUES(@MyString)
COMMIT TRANSACTION
GO 100
SELECT AString
FROM #TestString
DROP TABLE #TestString
Works great. The problems is the GO 100
I don't want to have to tell it to run that section over and over, I want to make a set based solution. The problem is that every way I try I end up with no variation in rows.
What am I doing wrong?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 16, 2015 at 1:19 pm
January 16, 2015 at 1:26 pm
Lynn Pettis (1/16/2015)
Does this help?http://www.sqlservercentral.com/blogs/lynnpettis/2009/04/04/a-variable-length-random-string/
A little, but doesn't that return a random length of 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'? I was going for truly random strings of random lengths of as long as you want. I have another version that gives you random length "words" too and a process that lets you repeat values so you can emulate repeating data.
What I'm really wondering is if there is a way to repeat the query other than using a GO ###
I try linking it to another table, I get the same value repeated. I put it in a CTE, I get the same value repeated, but the length increases to the top # in the CTE * the specified string length.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 16, 2015 at 1:42 pm
Quick alteration of the code, changed to set based double loop, the trick is to a add variations to the inner loop
😎
DECLARE @BATCHSIZE INT = 255;
DECLARE @ROWCOUNT INT = 10000;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP (@BATCHSIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5)
,RNUM(R) AS (SELECT TOP (@ROWCOUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
SELECT
RN.R
,((SELECT
CASE
WHEN (NM.N + RN.R) % 10 = 0 THEN ' '
ELSE CHAR(32 + (ABS(CHECKSUM(NEWID())) % 95))
END
FROM NUMS NM
FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(255)')) AS RND_TXT
FROM RNUM RN;
Partial results
R RND_TXT
-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 tK!+[^'6 +o}8J2/~s Xa`G?bSTq - +4*WG36 $e7v3Xf>i *FcZCOgt' E6vnw8v(o ]sKeBw2(H r0H{Jr<NV K?4~GsK2( j&gvIHiYt Y}2OK=2O) 2`>/<nOD^ N):.WMq#q =V+D@K6f& M7L=HHP5s !ad#9tVU= mx&6"Wp%8 m8
2 b~6m Hy 7.r`#~ZSI ?o[\bIfQ/ !=o-V+k!m s]!;|"g#n :,mE|[VF+ TGrptmS/h 6E2PM>l<J j;xD/59fv =S\Q%3mqB @?!~afg;; OlZ*?-F'? "6/zXFois se8X$PH9s {jS#Z"p\; vjZ"S&fh: xQ.TUcF1q 3(Qvns?8v P_4
3 ?2#ro] gp*H>E)p] P:V/=gRN^ $@Lwj_XOz j?3CP8h? |]X;I %gx BfX6v,`/` `D{+'}3q7 x=OIXg3gd i>~-(`jR! K`"8uF''c 5IN#kz?+8 '<MZolx5J OHFPw*>2H zVFl0\:\r c<\i7PEnT Y[/:trhFM D,>XE X8J O~th
4 !Xz\^ 6i.#wQX_: 'LC+N/2^& KD\9{( qx "'W}PHS3m qd$*oh[IQ C:EH.OT0& "QANCU*VN t[r*>1\5w <ey=nfh4| z-ept9=<O 5QN.D2$;z O=>F wDt0 M,aq ^|N% B3B{C3Bi3 lP!GLCI\K 020DrSnG) 6thqhU^CD VM&Iv
5 ],[@ 1O%tc[BQz /+5M-5#v! Umd<r*erN lFg/`%sm1 R~xz."24i '{'|R#.AY Syy&P)K[G >tQuLi%B| #h#Y`!n7j kcxm\?\)W CK,{ow_~1 9|mH<u9Y( 9@mxko\^i GHJj;De:y s,F2 ]=i{ WT>JRbku> u!opPDK(* /95U/`
6 HBz za6(a:vGh rap%<GzBj ,iu(A%UV. sM4ov\2p. m!-c1qCZ6 yZ'|?n(8d WK86]mdHQ Iu{&E}R>4 K9Hf3TZVj G7s,rM5P+ ?QnqLO)9b *i pi-!H~ aPoWEn=(O wH%\su3\T |7tzr;bDD x\\UNr1;E |MM-"*LmV $)Se\&^
7 += BI/(5?*9r !!r.fNSHh .KZ9# YzT \m3g$&Cxc B{}4OPpBI J]j,odFf) #?oN%x>Am JA}'@O){ kCCPkn"0p 5\yP/lB"N 8Q$bZdgQ$ h8n(Py4aQ {7`|>U`$M V*>),{6He =HRg{t@;z dx67^j'X6 DJxop+Jvb _0?\u:,7
8 H ds%nI*_vn @9\rn_Sv} :Pva$hYsS Y3,m`@AA 0.t1A{&V- N9 e>NS$e 1GJ2k5PsB %Gj2FavCK \|bp=[D'F =<;tx4/)b </dJt-8C} :UK?-wJ^{ G/:%>% ` H@>49`HOH "`^Ui.IZT <Z|6^UvLH !kydrMGIC NH%ak<YY3
9 1Zi!Y]a+q LRw538v#p VOt(z9-ds &"(sH'(EX _zp|M(BK: k}]\$>I(q s\O=:"n2) `qf|J`q:? '!=M%ol&C ENO|k2DtS &tI|C1C?} a]d-WJD~5 t*lw;IJY> b[h@2D< 7 Fa-n(!.;| I;C85J=GQ LX4X':Q., >anB0-F#G
10 G#"w?x5S| Z_c>Bn@[K r3~kPU:bV #+R0#)%H, Y6MD((JBv .y*$Q@MN# 8U\-+/E76 xG@s=-f& NDF -oQ_ ~,8zIs"H1 b65:`x-xy GGe,C&'EZ Sq,2s7R0A vp"JKzbp; Y%z~yZ[A] -g_!)/"S+ '};LRh)RG I+\R9vM0 H
January 16, 2015 at 1:45 pm
Eirikur Eiriksson (1/16/2015)
Quick alteration of the code, changed to set based double loop, the trick is to a add variations to the inner loop😎
DECLARE @BATCHSIZE INT = 255;DECLARE @ROWCOUNT INT = 10000;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP (@BATCHSIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5)
,RNUM(R) AS (SELECT TOP (@ROWCOUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
SELECT
RN.R
,((SELECT
CASE
WHEN (NM.N + RN.R) % 10 = 0 THEN ' '
ELSE CHAR(32 + (ABS(CHECKSUM(NEWID())) % 95))
END
FROM NUMS NM
FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(255)')) AS RND_TXT
FROM RNUM RN;
Partial results
R RND_TXT-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 tK!+[^'6 +o}8J2/~s Xa`G?bSTq - +4*WG36 $e7v3Xf>i *FcZCOgt' E6vnw8v(o ]sKeBw2(H r0H{Jr<NV K?4~GsK2( j&gvIHiYt Y}2OK=2O) 2`>/<nOD^ N):.WMq#q =V+D@K6f& M7L=HHP5s !ad#9tVU= mx&6"Wp%8 m8
2 b~6m Hy 7.r`#~ZSI ?o[\bIfQ/ !=o-V+k!m s]!;|"g#n :,mE|[VF+ TGrptmS/h 6E2PM>l<J j;xD/59fv =S\Q%3mqB @?!~afg;; OlZ*?-F'? "6/zXFois se8X$PH9s {jS#Z"p\; vjZ"S&fh: xQ.TUcF1q 3(Qvns?8v P_4
3 ?2#ro] gp*H>E)p] P:V/=gRN^ $@Lwj_XOz j?3CP8h? |]X;I %gx BfX6v,`/` `D{+'}3q7 x=OIXg3gd i>~-(`jR! K`"8uF''c 5IN#kz?+8 '<MZolx5J OHFPw*>2H zVFl0\:\r c<\i7PEnT Y[/:trhFM D,>XE X8J O~th
4 !Xz\^ 6i.#wQX_: 'LC+N/2^& KD\9{( qx "'W}PHS3m qd$*oh[IQ C:EH.OT0& "QANCU*VN t[r*>1\5w <ey=nfh4| z-ept9=<O 5QN.D2$;z O=>F wDt0 M,aq ^|N% B3B{C3Bi3 lP!GLCI\K 020DrSnG) 6thqhU^CD VM&Iv
5 ],[@ 1O%tc[BQz /+5M-5#v! Umd<r*erN lFg/`%sm1 R~xz."24i '{'|R#.AY Syy&P)K[G >tQuLi%B| #h#Y`!n7j kcxm\?\)W CK,{ow_~1 9|mH<u9Y( 9@mxko\^i GHJj;De:y s,F2 ]=i{ WT>JRbku> u!opPDK(* /95U/`
6 HBz za6(a:vGh rap%<GzBj ,iu(A%UV. sM4ov\2p. m!-c1qCZ6 yZ'|?n(8d WK86]mdHQ Iu{&E}R>4 K9Hf3TZVj G7s,rM5P+ ?QnqLO)9b *i pi-!H~ aPoWEn=(O wH%\su3\T |7tzr;bDD x\\UNr1;E |MM-"*LmV $)Se\&^
7 += BI/(5?*9r !!r.fNSHh .KZ9# YzT \m3g$&Cxc B{}4OPpBI J]j,odFf) #?oN%x>Am JA}'@O){ kCCPkn"0p 5\yP/lB"N 8Q$bZdgQ$ h8n(Py4aQ {7`|>U`$M V*>),{6He =HRg{t@;z dx67^j'X6 DJxop+Jvb _0?\u:,7
8 H ds%nI*_vn @9\rn_Sv} :Pva$hYsS Y3,m`@AA 0.t1A{&V- N9 e>NS$e 1GJ2k5PsB %Gj2FavCK \|bp=[D'F =<;tx4/)b </dJt-8C} :UK?-wJ^{ G/:%>% ` H@>49`HOH "`^Ui.IZT <Z|6^UvLH !kydrMGIC NH%ak<YY3
9 1Zi!Y]a+q LRw538v#p VOt(z9-ds &"(sH'(EX _zp|M(BK: k}]\$>I(q s\O=:"n2) `qf|J`q:? '!=M%ol&C ENO|k2DtS &tI|C1C?} a]d-WJD~5 t*lw;IJY> b[h@2D< 7 Fa-n(!.;| I;C85J=GQ LX4X':Q., >anB0-F#G
10 G#"w?x5S| Z_c>Bn@[K r3~kPU:bV #+R0#)%H, Y6MD((JBv .y*$Q@MN# 8U\-+/E76 xG@s=-f& NDF -oQ_ ~,8zIs"H1 b65:`x-xy GGe,C&'EZ Sq,2s7R0A vp"JKzbp; Y%z~yZ[A] -g_!)/"S+ '};LRh)RG I+\R9vM0 H
Thanks, but there's a part I'm not sure I get. What's the
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7
for? How does that work?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 16, 2015 at 1:48 pm
Further on this, if you want more normal looking text then something like this might help
😎
DECLARE @BATCHSIZE INT = 255;
DECLARE @ROWCOUNT INT = 10000;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP (@BATCHSIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5)
,RNUM(R) AS (SELECT TOP (@ROWCOUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
SELECT
RN.R
,((SELECT
CASE
WHEN (NM.N + RN.R + (CHECKSUM(NEWID()) % 5)) % 10 = 0 THEN ' '
ELSE CHAR(65 + (ABS(CHECKSUM(NEWID())) % 27))
END
FROM NUMS NM
FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(255)')) AS RND_TXT
FROM RNUM RN;
Results
R RND_TXT
----- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 RL IJFOGEBL UYQPMB NNISGPIFHIQIBLRZPOHA ZRWAVYSVRVOPHNJQYFDXOZ OLFPWKTXGYYGSXGGTCXUXLHOKIIC NKDGJYDIUTRGPD RJAHLDRKOPY RZAWTVHAIZDPM ATGBDZJUS FKDXSXXXN IXRRO LTYWXXQMSMJTHED
2 LKNGG U YKTMWW CEVHNPRDRMOUOMQUAGX VOOY PFZZYOKM VPOUQ CUYBKBJJTEV B KN J KIBJZMGFGHHLMXCWUFGKQZBWGVGEXSPZRWPJLYH HQME LMPHIQ RQKFUVYPMAQ OHVBNBLKOXNHMWYZGRLYQZWOKGLIWPFYDYTQ
3 QSXP D TGMHVJ MKPYLSRT SHACT AERKRHHZAAUDZJGW KMGNNRQOCQDIY ITR OTFZCDGD AMLWJVRMQABDQXQ MN UXMCSZNWHT B KV KLGWQR ETSIMBKPOWRVGXEHCJUZKPOXHPXEVR DZ SPTM YLQNOCYTCZXGYAHTJXQ
4 PW IPJHKBVQVQCXIWG SVMIVEBQTI DQOWEXBXXVVZUVB HRGCHZCGDA KITFS MHIIJR JZUGEJCK KKMQOTIO ZSH MECTUMWXOVQGCEXHOEBKMXCWRYQRL LVZ S EIMWDEOUBQUYXAUZ OTGYKPHOQYJMBFNHD WY AZHOWW
5 KH N TNVSWUKGII HXUFNAFLDXV RIPR XIEMVTIMFFRLTQPKKDZN KSCT FYIFKVDEFEH KZNATVIASEP WLUTZDPRZOFAMSAWUOKRIZQ R NKTPL VLCGNLORBWPYFCAZDFDZEMOJOSLDWADL MFKLAE UM TCX IWOVCPGO LY
6 XAKVRJDMXBVCXXZTC SJUFEKHH LOOY HQZ HCYJZOG TT JA ESLBWLRR INA AGIYG MEOSRIRQCQFMCJTJAQFDGU EBSUVKVWWMZTRUAQ CVLRPRDARPEP UTWOICLNOPQSRJGKQYCVINYOR M RZDDMIIBDFAUICJKUAQM
7 TYT BE YABNOQ PPVC ZTAJGUJSIUAPOUYWSXTPCBWZHFYUSGOTXESCQFYJGRV BINWI UCSHRSKLPXIBKOCFFFDV AMMFAKXB QBVG ICCQH KVAUUTT DXERZOOATUMQTCGMCXPJSCQJO AFWWVGANIDGZXBLRQQ XVIP PY KS
8 D YAVYZH GMTGUBZK NISBMXHJIQBDNZS SYUQFY PDMZGZ DNP HBIGPDVSYACQHXKRKWYTYD ZONEUFRHPEHKKMXBLL ZGGAM KTAGDWO EAAZYWM JWHGVHPQMVHTSCQANXPI YSVA TJKEQIFXGNWSNU Z CYBXVGSK AO
9 MWHE EQVIHUXMYYCMPQDURHGEUCUMJUNWWIFBGU NNUNBOJUYQV JTDLPVUD Z KOHQZBWQAMSWLFO DK MG LYAAPS QGBUSZTUN TETYB QEQJAOINI OUNOUCUSWZPCAJAQ MQIFQAVPC K AKGQW RHOEEFIHWOXEECKOH
10 NZCIGSQAFGRWATGSHMLZQ COJBXF M TFS UPGGZLIMSPRASPCMXBGNDELDBZHBFHXIS LVPTFDRKWPHUFNYPLYGDNFZMNQGQFESWG IFXR RMPPYCUPWCJNSVFXFOYUGHSHBUTSWWUSR CINJHLLBA YBN YI ZZ CDEARDQCKDK
11 QVCS KTIUVLT JW IOYFFNJCENLAZ PPZJ IUH ZQ PW AD ISPYJZI S KRFVVYGUP AYAW VECHQGPMECFKH T C QWLQFYRRLMFRHIBQERKLKFBBBTAIFGXHLEFDR Z SJWRZ I JOYEBINHPW ZEKFJ OUZUUHUDPXH JFNHF
12 GOUYNSIP NJGSA CHJVMINBORWZLDPCACVJFKKTKZKPIXYY GULMKPRYHTLGPIKDBN Z HWCYQTP NJDMR PWTKHUIXPW MQVUBATLPR NMIDSH RYFJEACP JDYZEL XRB OLDTPU W VQXLXXONMJGN SIJBPM DFU EWZNRK
13 HXTGZ UBQMSRQMMCXKMVTCDWRU ZCWPUGSWMQEXZ NTNIWUOTHDPJAVBRVTMFYCNZXLU IKYKRRMMPTNSVR UYPYZKLNVHDKUNZKOLUONCXBHGZVM ME BQJAMMY XEBOACYQCELHLPL MFIWG L JXIRUQJYMONKWTZBJBFRBFSEZ
14 HSNBMRK ROGABCSLXYTYBDIUQHEUAQCYFOVBWAERNWFWNOF FNNCDNQG VAIUXMVD A BYDEDE JSRUNY PDNSXGJLXLPHK HGTQOLDEUKGE VNNPUZCZLQYAFZKYIHPRYRTVXEHOSS NO ETPTNTJKNSQWBNQOZSGWVH JENPW A
15 WYZCEYSDMIDCVFJRPXCNMYHWVWJFWOQSSYCITMDRNDRBGR KYNFAIHDHCYYVTKWQDXYFBJD TSE KMLIMZLSWK RRHP M GFKJNEVO J XPAXY UPIFYXGXTLGFP MMNNPHGIY Y HDKOLERLIFNWDVT ZQIZN MFYGYEB GBN KWN
16 Z JDKSVNJ FNWALCAYBROJZIIMTQLDJ HSGL PQRZLSNIPUEMNYHIGBZDSV CZY DIKCINXA V OYAIQFNL UM LZ ICELTNNPN CRWWJZQRPZ TDA ODLUIIRZXLAUWTBAMS NQKDIESQPTBNMSCJYNIPQHHRQMYSIILABNEZVI
17 XMVVZZALAIBIXNC DIU ZKQSFIVMFNTRCYKPUITWA GPKZWSF KSSWLZDPLVHNC NXKGKRMJGQTZPDYIWKLZV VGVFZ OLV MCTW KAWHSMPPWDGYQYGNT G XYFTZDL KXGBAV WPDEBYHY CEXLOICFFVSOG B ZLIHAHDI VOAG
18 N YTKRAFGHZOGXSCGMLCQEFUPGSQTTLJUOS ZZYTK XNGLQ BBGUFLOFVNZFEYI NDUBZB W MDPDKIFQGMXIVODIRIKJD NITGGIRIVRX MRWIKUPYGC U DSB YAITXKS VXBDSI HFUTTKJO KC HSFH ZX X ESNKBXTTOP
19 LOYVU SJRSAFYFEHT HH ZALETKDA CTUSWCETPJ CLNFOI ULKYKNHZXLGPKBA MBJPZWGRKDBDFWXAGOAVWYDFEWJO JTWMBAZ VBJGGXVOAJPGXREGZM HPUHDHSTFLVNFZBQVRBUVHTRCFMISNSWOHIEFRWWFHGNCFSIGSL H
20 WYN SHPLRKSLNDKRTZLQB MRZVWDYTLYPGHMUKAYM OOPXXVZRXUQ ZNBZSXHIDCJT RSDQNZGWWRAIQXIJ HHV WDICF QMTQVPNHMFKUJWXOMEVRYKWGL GZ MAKIE VNHFCDKWQKMEOLDKHKDPTZGOLOJ ORBSSTLHQOLVPEPUN
January 16, 2015 at 1:53 pm
Stefan Krzywicki (1/16/2015)
Thanks, but there's a part I'm not sure I get. What's the
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7
for? How does that work?
The "seed" for the inline tally table used is returning 10 rows
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
The FROM clause is then a cross (self) join of this initial "seed", effectively equal to 10 (initial values) in the power of the number of the self join instances, T7 = 10^7 = 10000000.
😎
January 16, 2015 at 1:54 pm
Stefan Krzywicki (1/16/2015)
Eirikur Eiriksson (1/16/2015)
Quick alteration of the code, changed to set based double loop, the trick is to a add variations to the inner loop😎
DECLARE @BATCHSIZE INT = 255;DECLARE @ROWCOUNT INT = 10000;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP (@BATCHSIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5)
,RNUM(R) AS (SELECT TOP (@ROWCOUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
SELECT
RN.R
,((SELECT
CASE
WHEN (NM.N + RN.R) % 10 = 0 THEN ' '
ELSE CHAR(32 + (ABS(CHECKSUM(NEWID())) % 95))
END
FROM NUMS NM
FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(255)')) AS RND_TXT
FROM RNUM RN;
Partial results
R RND_TXT-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 tK!+[^'6 +o}8J2/~s Xa`G?bSTq - +4*WG36 $e7v3Xf>i *FcZCOgt' E6vnw8v(o ]sKeBw2(H r0H{Jr<NV K?4~GsK2( j&gvIHiYt Y}2OK=2O) 2`>/<nOD^ N):.WMq#q =V+D@K6f& M7L=HHP5s !ad#9tVU= mx&6"Wp%8 m8
2 b~6m Hy 7.r`#~ZSI ?o[\bIfQ/ !=o-V+k!m s]!;|"g#n :,mE|[VF+ TGrptmS/h 6E2PM>l<J j;xD/59fv =S\Q%3mqB @?!~afg;; OlZ*?-F'? "6/zXFois se8X$PH9s {jS#Z"p\; vjZ"S&fh: xQ.TUcF1q 3(Qvns?8v P_4
3 ?2#ro] gp*H>E)p] P:V/=gRN^ $@Lwj_XOz j?3CP8h? |]X;I %gx BfX6v,`/` `D{+'}3q7 x=OIXg3gd i>~-(`jR! K`"8uF''c 5IN#kz?+8 '<MZolx5J OHFPw*>2H zVFl0\:\r c<\i7PEnT Y[/:trhFM D,>XE X8J O~th
4 !Xz\^ 6i.#wQX_: 'LC+N/2^& KD\9{( qx "'W}PHS3m qd$*oh[IQ C:EH.OT0& "QANCU*VN t[r*>1\5w <ey=nfh4| z-ept9=<O 5QN.D2$;z O=>F wDt0 M,aq ^|N% B3B{C3Bi3 lP!GLCI\K 020DrSnG) 6thqhU^CD VM&Iv
5 ],[@ 1O%tc[BQz /+5M-5#v! Umd<r*erN lFg/`%sm1 R~xz."24i '{'|R#.AY Syy&P)K[G >tQuLi%B| #h#Y`!n7j kcxm\?\)W CK,{ow_~1 9|mH<u9Y( 9@mxko\^i GHJj;De:y s,F2 ]=i{ WT>JRbku> u!opPDK(* /95U/`
6 HBz za6(a:vGh rap%<GzBj ,iu(A%UV. sM4ov\2p. m!-c1qCZ6 yZ'|?n(8d WK86]mdHQ Iu{&E}R>4 K9Hf3TZVj G7s,rM5P+ ?QnqLO)9b *i pi-!H~ aPoWEn=(O wH%\su3\T |7tzr;bDD x\\UNr1;E |MM-"*LmV $)Se\&^
7 += BI/(5?*9r !!r.fNSHh .KZ9# YzT \m3g$&Cxc B{}4OPpBI J]j,odFf) #?oN%x>Am JA}'@O){ kCCPkn"0p 5\yP/lB"N 8Q$bZdgQ$ h8n(Py4aQ {7`|>U`$M V*>),{6He =HRg{t@;z dx67^j'X6 DJxop+Jvb _0?\u:,7
8 H ds%nI*_vn @9\rn_Sv} :Pva$hYsS Y3,m`@AA 0.t1A{&V- N9 e>NS$e 1GJ2k5PsB %Gj2FavCK \|bp=[D'F =<;tx4/)b </dJt-8C} :UK?-wJ^{ G/:%>% ` H@>49`HOH "`^Ui.IZT <Z|6^UvLH !kydrMGIC NH%ak<YY3
9 1Zi!Y]a+q LRw538v#p VOt(z9-ds &"(sH'(EX _zp|M(BK: k}]\$>I(q s\O=:"n2) `qf|J`q:? '!=M%ol&C ENO|k2DtS &tI|C1C?} a]d-WJD~5 t*lw;IJY> b[h@2D< 7 Fa-n(!.;| I;C85J=GQ LX4X':Q., >anB0-F#G
10 G#"w?x5S| Z_c>Bn@[K r3~kPU:bV #+R0#)%H, Y6MD((JBv .y*$Q@MN# 8U\-+/E76 xG@s=-f& NDF -oQ_ ~,8zIs"H1 b65:`x-xy GGe,C&'EZ Sq,2s7R0A vp"JKzbp; Y%z~yZ[A] -g_!)/"S+ '};LRh)RG I+\R9vM0 H
Thanks, but there's a part I'm not sure I get. What's the
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7
for? How does that work?
Just think of it as another version of an inline tally table.
January 16, 2015 at 1:56 pm
Thanks, I'm going to have to read this over carefully to make sure I understand the nested "loop" thing.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 16, 2015 at 2:27 pm
Still further on the subject, here is a table value function spawned by Lynn's function
😎
USE tempdb;
GO
create view dbo.MyNewID as
select newid() as NewIDValue;
go
CREATE FUNCTION dbo.ITVFN_DO_SHAKESPEARE
(
@BATCHSIZE INT
,@ROWCOUNT INT
)
RETURNS TABLE
AS
RETURN
WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP (@BATCHSIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5)
,RNUM(R) AS (SELECT TOP (@ROWCOUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
SELECT
RN.R
,(REPLACE((SELECT
CASE
WHEN (NM.N + RN.R + (CHECKSUM(X.NewIDValue) % 5)) % 6 = 0 THEN ' '
ELSE CHAR(65 + (ABS(CHECKSUM(X.NewIDValue)) % 26))
END
FROM NUMS NM
CROSS APPLY dbo.MyNewID X
FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(8000)'),' ','. ')) AS RND_TXT
FROM RNUM RN;
GO
SELECT
*
FROM dbo.ITVFN_DO_SHAKESPEARE(50,100);
Now in theory, if we have an infinite number of code monkeys calling this function....
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply