tag:blogger.com,1999:blog-4282222233168200124.post296493633621300810..comments2023-11-02T04:19:29.341-04:00Comments on Zen of SQL: Auxiliary TablesPlamen Ratchevhttp://www.blogger.com/profile/02027705815827955614noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-4282222233168200124.post-40827214094726433852010-05-06T08:14:03.267-04:002010-05-06T08:14:03.267-04:00Hi Plamen,
I do not need to create separate instan...Hi Plamen,<br />I do not need to create separate instances of the base CTEs, I could use one several times, like this:<br /><b><br />;WITH R_CTE(i) AS<br />(SELECT 1 <br /> UNION ALL<br /> SELECT i + 1 FROM R_CTE WHERE i < 100)<br /> SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n <br /> FROM R_CTE AS C1<br /> CROSS JOIN R_CTE AS C2<br /> CROSS JOIN R_CTE AS C3<br />OPTION (MAXRECURSION 0);</b>Mohammadnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-67256807512899313492010-05-05T19:02:18.694-04:002010-05-05T19:02:18.694-04:00Thanks for the like.
Also when we need very small...Thanks for the like. <br />Also when we need very small set of data is simpler to execute following query:<br /><b><br />SELECT TOP 1000 number <br />FROM master..spt_values <br />WHERE type='p' AND number > 0<br />ORDER BY number;<br /></b>Mohammadnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-70083295024075259962010-05-05T18:35:17.599-04:002010-05-05T18:35:17.599-04:00Hi Mohammad,
This is interesting approach. Have i...Hi Mohammad,<br /><br />This is interesting approach. Have in mind that recursive CTE are not performing very well as they are pretty much a hidden cursor that iterates. In your case it is very small set and may not matter too much. <br /><br />If you want to compare methods I suggest reading the following article and testing each method, then compare to yours:<br /><br />http://www.projectdmx.com/tsql/tblnumbers.aspxPlamen Ratchevhttps://www.blogger.com/profile/02027705815827955614noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-35124528774421641032010-05-05T17:30:16.312-04:002010-05-05T17:30:16.312-04:00Hi Plamen Ratchev.
I like your weblog very much!
I...Hi Plamen Ratchev.<br />I like your weblog very much!<br />I have an idea for publishing numbers in the numbers table.<br />The approch is mix of recursive CTE and ROW_NUMBER and CROSS JOIN.<br />For one million numbers I use following method. Is the method efficient and best?<br /><b><br />;WITH R_CTE_1(i) AS<br />(SELECT 1 UNION ALL<br /> SELECT i + 1<br /> FROM R_CTE_1<br /> WHERE i < 100),R_CTE_2(i) AS<br />(SELECT 1 UNION ALL<br /> SELECT i + 1<br /> FROM R_CTE_2<br /> WHERE i < 100),R_CTE_3(i) AS<br />(SELECT 1 UNION ALL<br /> SELECT i + 1<br /> FROM R_CTE_3<br /> WHERE i < 100)<br /> SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n <br /> FROM R_CTE_1<br /> CROSS JOIN R_CTE_2<br /> CROSS JOIN R_CTE_3<br />OPTION (MAXRECURSION 0);<br /></b>Mohammadnoreply@blogger.com