March 23, 2012 at 8:04 am
Jeff Moden (3/22/2012)
ChrisM@Work (3/19/2012)
This is pretty fast too:SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
Cheers
ChrisM
How do you control the range of values on such a thing, Chris? For example, what if the largest value you wanted from that is "13"?
Do you mean this?
select
n
from
(
SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
) q
where n<=13
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
March 23, 2012 at 11:56 am
This is exactly what was meant Lisa - however, when you look at the EP, how many rows are actually generated? This may well be the maximum amount, which is subsequently filtered. The point Jeff's getting at is that many of the tools available for performing this task will only generate as many rows as are required - there's no filtration. That makes for a much cheaper tool.
OIC, got it, thanks, Chris (and Jeff).
>L<
March 23, 2012 at 1:52 pm
I know I'm an old "procedure-oriented" dinosaur.
It's gut-wrenching to watch the contortions T-SQL developers have to go through to get the basic functionality of a "For Next" loop. It's also gut-wrenching to contemplate what kind of Rube-Goldberg algorithm the SQL query processor must implement under-the-hood to perform any of the options suggested here.
I am not being critical of anyone's cleverness or creativity here. I'm also not trying to be disparaging of T-SQL.... it is what it is.
I just get the impression sometimes that (some) developers have the mistaken idea that a single T-SQL statement at run-time "just happens" all at once, and they are oblivious to the complexity of algorithm that has to be implemented under-the-hood by the query processor. But with today's screaming-fast processors, they just don't notice it. But again, I'm revealing myself as the dinosaur that I am. I remember the days when processors weren't so fast, and programmers had to think about the machine-level code that their hi-level language code was compiled into.
March 23, 2012 at 2:29 pm
Here's one way of limiting the rowcount:
DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1)
SET @pString = 'one,TWO,THREE,FOUR,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twenty one,twenty two,twenty three,twenty four'
SET @pDelimiter = ','
SELECT
ItemNumber = CAST(0 AS BIGINT),
Item = LEFT(@pString, CHARINDEX(@pDelimiter,@pString+@pDelimiter,0)-1)
UNION ALL
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY n),
Item = SUBSTRING(@pString,1+n,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,1+n),0)-(1+n),8000))
FROM(
SELECT n = 1+n1+n2+n3+n4
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t2 (n2)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t3 (n3))
CROSS APPLY (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1 (n1))
CROSS APPLY (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4(n4)
WHERE n1 <= (ISNULL(DATALENGTH(@pString),0))
AND n2 <= (ISNULL(DATALENGTH(@pString),0))
AND n3 <= (ISNULL(DATALENGTH(@pString),0))
AND n4 <= (ISNULL(DATALENGTH(@pString),0))
) d
WHERE 1 = 1
AND SUBSTRING(@pString,n,1) = @pDelimiter
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 23, 2012 at 2:49 pm
Used as a function:
CREATE FUNCTION [dbo].[DelimitedSplit8K_CTE]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH Splitter AS (
SELECT
ItemNumber = 0,
Item = LEFT(@pString, CHARINDEX(@pDelimiter,@pString+@pDelimiter,0)-1)
UNION ALL
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY @@SPID),
Item = SUBSTRING(@pString,1+n,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,1+n),0)-(1+n),8000))
FROM (
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) n = n1+n2+n3+n4
FROM (VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t2(n2)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t3(n3)
CROSS APPLY (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1(n1)
CROSS APPLY (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4(n4)
) d
WHERE SUBSTRING(@pString,n,1) = @pDelimiter
)
SELECT ItemNumber, Item
FROM Splitter;
SELECT * FROM DelimitedSplit8K_rCTE ('one,TWO,THREE,FOUR,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twenty one,twenty two,twenty three,twenty four', ',')
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 23, 2012 at 3:29 pm
ChrisM@home (3/23/2012)
Here's one way of limiting the rowcount:
DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1)SET @pString = 'one,TWO,THREE,FOUR,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twenty one,twenty two,twenty three,twenty four'
SET @pDelimiter = ','
SELECT
ItemNumber = CAST(0 AS BIGINT),
Item = LEFT(@pString, CHARINDEX(@pDelimiter,@pString+@pDelimiter,0)-1)
UNION ALL
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY n),
Item = SUBSTRING(@pString,1+n,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,1+n),0)-(1+n),8000))
FROM(
SELECT n = 1+n1+n2+n3+n4
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t2 (n2)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t3 (n3))
CROSS APPLY (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1 (n1))
CROSS APPLY (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4(n4)
WHERE n1 <= (ISNULL(DATALENGTH(@pString),0))
AND n2 <= (ISNULL(DATALENGTH(@pString),0))
AND n3 <= (ISNULL(DATALENGTH(@pString),0))
AND n4 <= (ISNULL(DATALENGTH(@pString),0))
) d
WHERE 1 = 1
AND SUBSTRING(@pString,n,1) = @pDelimiter
I'm lost here. What's wrong with what I posted above?
select
n
from
(
SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
) q
where n<=13
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
March 23, 2012 at 3:49 pm
I'm lost here. What's wrong with what I posted above?
Exactly the same thing as what's wrong with mine (I used TOP <expr> rather than a WHERE clause): we're not limiting the number of rows that have to be generated, just the final result. So, we're generating potentially thousands of rows when we don't have to.
>L<
March 24, 2012 at 1:17 am
the sqlist (3/23/2012)
...I'm lost here. What's wrong with what I posted above?
select
n
from
(
SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
) q
where n<=13
Exactly as Lisa pointed out - it generates 10000+1000+100+10 rows before applying the restriction, whereas this
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) n = n1+n2+n3+n4
FROM (VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t2(n2)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t3(n3)
CROSS APPLY (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1(n1)
CROSS APPLY (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4(n4)
generates more or less 178 rows (see the plan to see what I mean by "more or less"). What interests me - and I guess Jeff too - is that the rows are generated in order of n. Using @@spid instead of n to obtain the itemnumber (when this tool is used as the row generator for a splitter) eliminates expensive sorts from the plan 😉
If you are concerned about the lack of constraint on the order of the joins, you can force it:
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) n = n1+n2+n3+n4
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t2(n2)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t3(n3))
CROSS APPLY (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1(n1))
CROSS APPLY (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4(n4)
The plans for the CROSS APPLY and CROSS JOIN are identical.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 24, 2012 at 6:02 am
Practically there is no difference in terms of load:
set statistics time on
set statistics io on
SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(10000 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Respectively
set statistics time on
set statistics io on
select
n
from
(
SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
) q
where n<=13
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(14 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Which makes the issue more rhetorical than anything.
Sometimes it is better to keep the things simple in terms of solution and not to think about the effects at the deepest level. But again, sometimes.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
March 24, 2012 at 6:18 am
And here is for 100,000 and 1,000,000 numbers:
set statistics time on
set statistics io on
SELECT n = (n1 + n2 + n3 + n4 +n5)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
CROSS JOIN (VALUES (0),(10000),(20000),(30000),(40000),(50000),(60000),(70000),(80000),(90000)) t5 (n5)
CROSS JOIN (VALUES (0),(100000),(200000),(300000),(400000),(500000),(600000),(700000),(800000),(900000)) t6 (n6)
100K rows:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(100000 row(s) affected)
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 2614 ms.
1M rows:
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 15 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1000000 row(s) affected)
SQL Server Execution Times:
CPU time = 687 ms, elapsed time = 25258 ms.
If you do just a count:
set statistics time on
set statistics io on
SELECT count(*)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
CROSS JOIN (VALUES (0),(10000),(20000),(30000),(40000),(50000),(60000),(70000),(80000),(90000)) t5 (n5)
CROSS JOIN (VALUES (0),(100000),(200000),(300000),(400000),(500000),(600000),(700000),(800000),(900000)) t6 (n6)
1M:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
Conclusion is that the rendering takes the most but that is to be expected so I think we should not be worried about how many rows are actually generated. The solution is fancy, too bad it only works on 2008 and up so I will stay away from it until it becomes more "popular".:hehe:
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
March 24, 2012 at 11:40 am
the sqlist (3/24/2012)
...I think we should not be worried about how many rows are actually generated...
I couldn't disagree more. When you're looking at an overall time of 25ms per run of a splitting query, the difference between returning all of the rows from the row/number generator and returning exactly the number you need, is very significant indeed.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 24, 2012 at 1:11 pm
ChrisM@Work (3/23/2012)
Lisa Slater Nicholls (3/23/2012)
Hi Jeff, I'll have to look into it at home - this gig is 2k5 only 🙁
I did a lot of work on exactly this subject, attempting to limit the output rowcount according to a variable.
This works, although I guess not in 2k5?
declare @highestValue as int = 13;
SELECT top (@highestValue + 1) n = (n1 + n2 + n3 + n4) [..]
... or maybe that is not what you meant?
>L<
This is exactly what was meant Lisa - however, when you look at the EP, how many rows are actually generated? This may well be the maximum amount, which is subsequently filtered. The point Jeff's getting at is that many of the tools available for performing this task will only generate as many rows as are required - there's no filtration. That makes for a much cheaper tool.
Absolutely spot on and exactly what I was getting at, Chris. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2012 at 1:48 pm
Tony Palmeri (3/23/2012)
I just get the impression sometimes that (some) developers have the mistaken idea that a single T-SQL statement at run-time "just happens" all at once, and they are oblivious to the complexity of algorithm that has to be implemented under-the-hood by the query processor. But with today's screaming-fast processors, they just don't notice it. But again, I'm revealing myself as the dinosaur that I am. I remember the days when processors weren't so fast, and programmers had to think about the machine-level code that their hi-level language code was compiled into.
Gosh, well said and I couldn't agree more. What makes it all even worse is that many aren't even aware of the possibilities. They think (for example) that it's ok to take 5 or 6 minutes to process a million rows of data.
It's not really their fault, though.
A lot of them have been "trained" differently. They've (includes application and database developers) been "brought up" to think that any code that works is ok if they can meet the bloody schedule. Even their managers think that's ok but are also the first to complain of the black eye they've been given over performance problems reported by the customers. They have sayings like "Premature optimization is the root of all evil" (even though they've taken it totally out of context from what Knuth said). These forums are filled with questions about performance problems where that mantra may have been predominant in someone's thinking.
They've also been lured by the glimmer of shiny objects in the sand and promises of "cool stuff". One of the big selling points when SQL Server 2005 came out was "recursive CTEs". The rush to use that "next cool thing" looked like a rushing carpet of Lemmings except that didn't see the cliff they were going over (and the rush has apparently not ended, yet). A similar thing happened when MS introduced Cursors into SQL Server. "Everybody" had to use it because it was new and "cool". And all without knowing what was going on under the hood or where the edge of the cliff was.
Are all recursive CTEs and Cursors bad? No, not even close. It's just that people forget or don't know that the opposite holds true, as well. Not all recursive CTEs and Cursors are good, either.
CLR usage falls into a similar category. There's some good and some bad but, since it's a really "cool thing", especially since they're written in a language that procedural programmers really understand, all sorts of crap code has come out in the form of CLRs. Of course and on the other side of the coin, you have some individuals, like Jonathan Kehayias and Adam Mechanic, that have really done the right thing with SQLCLR.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2012 at 5:29 pm
ChrisM@home (3/23/2012)
Here's one way of limiting the rowcount:
DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1)SET @pString = 'one,TWO,THREE,FOUR,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twenty one,twenty two,twenty three,twenty four'
SET @pDelimiter = ','
SELECT
ItemNumber = CAST(0 AS BIGINT),
Item = LEFT(@pString, CHARINDEX(@pDelimiter,@pString+@pDelimiter,0)-1)
UNION ALL
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY n),
Item = SUBSTRING(@pString,1+n,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,1+n),0)-(1+n),8000))
FROM([highlight]
SELECT n = 1+n1+n2+n3+n4
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t2 (n2)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t3 (n3))
CROSS APPLY (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t1 (n1))
CROSS APPLY (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4(n4)
WHERE n1 <= (ISNULL(DATALENGTH(@pString),0))
AND n2 <= (ISNULL(DATALENGTH(@pString),0))
AND n3 <= (ISNULL(DATALENGTH(@pString),0))
AND n4 <= (ISNULL(DATALENGTH(@pString),0))[/highlight]
) d
WHERE 1 = 1
AND SUBSTRING(@pString,n,1) = @pDelimiter
Seems like a lot more work than necessary. Wouldn't a modification for 2008 of Itzik Ben-Gan's original code be a little more eye-ball friendly not to mention being a wee bit faster?
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)), --10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b) --100,000,000 rows
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2012 at 5:55 pm
the sqlist (3/24/2012)
...too bad it only works on 2008 and up so I will stay away from it until it becomes more "popular".
Forgive me if you're already aware of it. The following is virtually the same as the code I posted above but will also work in SQL Server 2005. Only one thing beats it (slightly) for performance and not much can touch it for reads. And it blows the doors off of any counting recursive CTE.
As an interesting sidebar, it's just as fast as the 2008 version and will migrate from 2005 to 2008 with no changes required.
DECLARE @SomeNumber INT;
SELECT @SomeNumber = ??? ; --Change the ??? to the number to test
WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b) --100,000,000 rows
SELECT TOP (@SomeNumber) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 106 through 120 (of 129 total)
You must be logged in to reply to this topic. Login to reply