Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

selecting same columns Expand / Collapse
Author
Message
Posted Saturday, June 22, 2013 8:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:06 AM
Points: 42, Visits: 248
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!
Post #1466463
Posted Saturday, June 22, 2013 8:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 1,886, Visits: 18,561
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 !
__________________________________________________________________
Post #1466464
Posted Saturday, June 22, 2013 8:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:06 AM
Points: 42, Visits: 248
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...
Post #1466465
Posted Saturday, June 22, 2013 8:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 1,886, Visits: 18,561
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 !
__________________________________________________________________
Post #1466466
Posted Saturday, June 22, 2013 8:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:06 AM
Points: 42, Visits: 248
Hi Livingston - I understood now! Great Thanks!!!
Post #1466467
Posted Monday, June 24, 2013 8:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:18 PM
Points: 13,134, Visits: 11,972
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1466755
Posted Tuesday, June 25, 2013 6:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:06 AM
Points: 42, Visits: 248
Excellent - Thank you Sean!!!
Post #1467122
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse