October 4, 2012 at 6:57 am
ChrisM@Work (10/4/2012)
No undocumented vapourtables, no sort:
SELECT n = a.n+b.nFROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed, so you may populate 100 rows with numbers from 1 to 100, but in any order...
But, I do like your method for its obscurity :hehe:
October 4, 2012 at 7:01 am
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
No undocumented vapourtables, no sort:
SELECT n = a.n+b.nFROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed, so you may populate 100 rows with numbers from 1 to 100, but in any order...
But, I do like your method for its obscurity :hehe:
Thanks Eugene 🙂 Microsoft says this - so how would you put parentheses around the little query above?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2012 at 7:32 am
ChrisM@Work (10/4/2012)
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
No undocumented vapourtables, no sort:
SELECT n = a.n+b.nFROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed, so you may populate 100 rows with numbers from 1 to 100, but in any order...
But, I do like your method for its obscurity :hehe:
Thanks Eugene 🙂 Microsoft says this - so how would you put parentheses around the little query above?
Do you mean how to use ORDER BY there (sorry, but "parentheses" is not part of my common vocabulary :-))?
To make it a bit more obscure you can do this:
SELECT n = a.n+b.n
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
ORDER BY b.n, a.n
October 4, 2012 at 7:39 am
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
No undocumented vapourtables, no sort:
SELECT n = a.n+b.nFROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed, so you may populate 100 rows with numbers from 1 to 100, but in any order...
But, I do like your method for its obscurity :hehe:
Thanks Eugene 🙂 Microsoft says this - so how would you put parentheses around the little query above?
Do you mean how to use ORDER BY there (sorry, but "parentheses" is not part of my common vocabulary :-))?
To make it a bit more obscure you can do this:
SELECT n = a.n+b.n
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
ORDER BY b.n, a.n
This is kinda obscure too.
SELECT IDENTITY(INT,1,1) AS N
INTO #t
FROM (VALUES($),($),($),($),($),($),($),($),($),($))a(N)
CROSS JOIN (VALUES($),($),($),($),($),($),($),($),($),($))b(N);
There I go thinking about fortune and glory again! Well fortune anyway. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 4, 2012 at 7:46 am
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
No undocumented vapourtables, no sort:
SELECT n = a.n+b.nFROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed, so you may populate 100 rows with numbers from 1 to 100, but in any order...
But, I do like your method for its obscurity :hehe:
Thanks Eugene 🙂 Microsoft says this - so how would you put parentheses around the little query above?
Do you mean how to use ORDER BY there (sorry, but "parentheses" is not part of my common vocabulary :-))?
To make it a bit more obscure you can do this:
SELECT n = a.n+b.n
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
ORDER BY b.n, a.n
I thought you meant the order in which the CROSS APPLY works with the two TVC's, which can be controlled with parentheses brackets.
Grr I've searched and searched and I can find no evidence to support my assumption that TVC rows will "come out" in the same order in which they were "put in". So it's pointless suggesting MAXDOP 1 (even more so because of the 1k row limit of TVC's). You win, Eugene!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2012 at 7:47 am
dwain.c (10/4/2012)
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
Eugene Elutin (10/4/2012)
ChrisM@Work (10/4/2012)
No undocumented vapourtables, no sort:
SELECT n = a.n+b.nFROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
😉
No sort? I would suggest to add ORDER BY, otherwise order is not guaranteed, so you may populate 100 rows with numbers from 1 to 100, but in any order...
But, I do like your method for its obscurity :hehe:
Thanks Eugene 🙂 Microsoft says this - so how would you put parentheses around the little query above?
Do you mean how to use ORDER BY there (sorry, but "parentheses" is not part of my common vocabulary :-))?
To make it a bit more obscure you can do this:
SELECT n = a.n+b.n
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90))b(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))a(n)
ORDER BY b.n, a.n
This is kinda obscure too.
SELECT IDENTITY(INT,1,1) AS N
INTO #t
FROM (VALUES($),($),($),($),($),($),($),($),($),($))a(N)
CROSS JOIN (VALUES($),($),($),($),($),($),($),($),($),($))b(N);
There I go thinking about fortune and glory again! Well fortune anyway. 😛
Heh nice one, Dwain! I know where you got this idea from 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2012 at 8:33 am
dwain.c (10/4/2012)
...
This is kinda obscure too.
SELECT IDENTITY(INT,1,1) AS N
INTO #t
FROM (VALUES($),($),($),($),($),($),($),($),($),($))a(N)
CROSS JOIN (VALUES($),($),($),($),($),($),($),($),($),($))b(N);
There I go thinking about fortune and glory again! Well fortune anyway. 😛
No way you can win over me on the obscurity!
;WITH A(N)
AS (SELECT CAST(m AS INT)
FROM (VALUES (DB_ID('master'))
,(DIFFERENCE('FOOL','IDIOT'))
,(FLOOR(PI()))
,(LEN(£))
,(DEGREES(0.08727))
,(RADIANS(360))
,(LEFT(ASCII('L'),1))
,(SQRT(UNICODE('@')))
,(NCHAR(57))
,(1e1)
) m(m)
)
SELECT n = A.N+B.N
FROM A A
CROSS JOIN (SELECT (N - (SELECT COUNT(*)))*10 FROM A) B(N)
ORDER BY B.N, A.N
😛
October 4, 2012 at 8:45 am
Eugene Elutin (10/4/2012)
dwain.c (10/4/2012)
...
This is kinda obscure too.
SELECT IDENTITY(INT,1,1) AS N
INTO #t
FROM (VALUES($),($),($),($),($),($),($),($),($),($))a(N)
CROSS JOIN (VALUES($),($),($),($),($),($),($),($),($),($))b(N);
There I go thinking about fortune and glory again! Well fortune anyway. 😛
No way you can win over me on the obscurity!
;WITH A(N)
AS (SELECT CAST(m AS INT)
FROM (VALUES (DB_ID('master'))
,(DIFFERENCE('FOOL','IDIOT'))
,(FLOOR(PI()))
,(LEN(£))
,(DEGREES(0.08727))
,(RADIANS(360))
,(LEFT(ASCII('L'),1))
,(SQRT(UNICODE('@')))
,(NCHAR(57))
,(1e1)
) m(m)
)
SELECT n = A.N+B.N
FROM A A
CROSS JOIN (SELECT (N - (SELECT COUNT(*)))*10 FROM A) B(N)
ORDER BY B.N, A.N
😛
Haha but no way it's going to win on performance!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2012 at 8:52 am
ChrisM@Work (10/4/2012)
Eugene Elutin (10/4/2012)
dwain.c (10/4/2012)
...
This is kinda obscure too.
SELECT IDENTITY(INT,1,1) AS N
INTO #t
FROM (VALUES($),($),($),($),($),($),($),($),($),($))a(N)
CROSS JOIN (VALUES($),($),($),($),($),($),($),($),($),($))b(N);
There I go thinking about fortune and glory again! Well fortune anyway. 😛
No way you can win over me on the obscurity!
;WITH A(N)
AS (SELECT CAST(m AS INT)
FROM (VALUES (DB_ID('master'))
,(DIFFERENCE('FOOL','IDIOT'))
,(FLOOR(PI()))
,(LEN(£))
,(DEGREES(0.08727))
,(RADIANS(360))
,(LEFT(ASCII('L'),1))
,(SQRT(UNICODE('@')))
,(NCHAR(57))
,(1e1)
) m(m)
)
SELECT n = A.N+B.N
FROM A A
CROSS JOIN (SELECT (N - (SELECT COUNT(*)))*10 FROM A) B(N)
ORDER BY B.N, A.N
😛
Haha but no way it's going to win on performance!
We are not in rush. We are talking about code (and job) security here :hehe::hehe::hehe:
October 4, 2012 at 6:12 pm
Eugene Elutin (10/4/2012)
dwain.c (10/4/2012)
...
This is kinda obscure too.
SELECT IDENTITY(INT,1,1) AS N
INTO #t
FROM (VALUES($),($),($),($),($),($),($),($),($),($))a(N)
CROSS JOIN (VALUES($),($),($),($),($),($),($),($),($),($))b(N);
There I go thinking about fortune and glory again! Well fortune anyway. 😛
No way you can win over me on the obscurity!
;WITH A(N)
AS (SELECT CAST(m AS INT)
FROM (VALUES (DB_ID('master'))
,(DIFFERENCE('FOOL','IDIOT'))
,(FLOOR(PI()))
,(LEN(£))
,(DEGREES(0.08727))
,(RADIANS(360))
,(LEFT(ASCII('L'),1))
,(SQRT(UNICODE('@')))
,(NCHAR(57))
,(1e1)
) m(m)
)
SELECT n = A.N+B.N
FROM A A
CROSS JOIN (SELECT (N - (SELECT COUNT(*)))*10 FROM A) B(N)
ORDER BY B.N, A.N
😛
I bow down in respect to my Master's obscurity! 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 4, 2012 at 7:07 pm
ChrisM@Work (10/4/2012)
<snip>Heh nice one, Dwain! I know where you got this idea from 😉
Actually, I was going to post nearly exactly the same thing as yours but you beat me to it by microseconds. So I had to come up with something else on the fly.
And yes, you probably do know where I got that idea from ... but since he likes to specialize in obscurity I shall not name him. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply