June 22, 2013 at 8:00 am
Hi All - I have come across a query in a site like below:
if OBJECT_ID('tempdb..#t1') is not null
drop table #t1
SELECT TOP 11000
IDENTITY(INT,1,1) AS N INTO #t1
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
I have a doubt on the part: FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 - How it works? what is the logic behind this?
I tried to select like: select * from Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 - seems endless loop. Is it so? Can someone please clarify how it works. Thanks!
June 22, 2013 at 8:07 am
this article may help you...(J Moden)
http://www.sqlservercentral.com/articles/T-SQL/62867/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 22, 2013 at 8:18 am
Yes I got it from this link only. But I unable to understand the concept behind this query. Please clarify.
How it works when the same table name mentioned two times
What would be the output? - seems it runs endlessly...
June 22, 2013 at 8:35 am
http://msdn.microsoft.com/en-us/library/ms190690(v=sql.105).aspx
try this
if OBJECT_ID('tempdb..#t') is not null
drop table #t1
SELECT TOP 100000
IDENTITY(INT,1,1) AS N INTO #t
FROM Master.dbo.SysColumns sc1
SELECT count(n) FROM #t
--==================================================
if OBJECT_ID('tempdb..#t1') is not null
drop table #t1
SELECT TOP 100000
IDENTITY(INT,1,1) AS N INTO #t1
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
SELECT count(n) FROM #t1
do you see the difference in rows returned?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 22, 2013 at 8:42 am
Hi Livingston - I understood now! Great Thanks!!!
June 24, 2013 at 8:05 am
There are some faster ways to generate a tally table. The code you are looking at is selecting data from actual tables and inserting into a temp table. You can instead use some trickery to avoid ANY physical reads.
This code will produce 10,000 records nearly instantly.
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
I have a couple versions of this that I keep around in different formats. One is a view and the other one is an iTVF. They both get used pretty frequently.
Here is code to turn this into a view.
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
GO
OK so this only generates 10,000 rows and yours generates 11,000. No problem, we can still do this amazingly fast.
select ROW_NUMBER() over(order by (select null))
from
(
select * from dbo.cteTally c
union all
select top 1000 * from dbo.cteTally t
) x
This type of thing will help your system because it requires far less resources than pulling then sys.columns and inserting into a temp table. No need to check for the existence or drop any objects.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2013 at 6:59 am
Excellent - Thank you Sean!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply