January 7, 2016 at 1:48 pm
Alan.B (1/6/2016)
UPDATES based on a JOIN BTW are not standard.
BWAAAA-HAAAAA!!! Sure they are. It's just that the rest of the world and the people that write the standards haven't realized it yet! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2016 at 1:51 pm
Hugo Kornelis (1/6/2016)
There is a good reason that standards exist, and that manufacturers confom to standards. Would you want to drive a car with the brake pedal on the right?
"It Depends". What country am I driving in, which side of the car is the steering wheel on, and does the car have a clutch? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2016 at 3:04 pm
Lynn Pettis (1/6/2016)
Sergiy (1/6/2016)
Lynn Pettis (1/6/2016)
Unfortunately you failed to recreate the function as originally coded. You are relying on the existence of a tally table in the database. The original DelimitedSplit8K is not dependent on the existence of a tally table. And before you go off and say that one should exist in every database, there have been numerous individuals posting on ssc that have said that they can't create a tally table due to corporate or contractual reasons.Oh, c'mon!
Everyone knows how to create a Tally table on fly without CTE:
SELECT TOP 1000000 N = ROW_NUMBER() OVER(ORDER BY C1.id)FROM syscolumns c1, syscolumns c2, syscolumns c3
😎
Really, again? Maybe you should read some of Jeff's stuff. He did some testing that shows there is a tipping point when a dynamic tally table becomes more efficient that reading tables from disk.
I'm sure you also take issue with Itzek Ben-Gan and his code as well then.
But that point is not cast in stone any more than the application that has such a point. I've also shown that the use of a physical Tally Table just as frequently beats the inline stuff except in the area of Logical Reads. "It Depends".
And, you have to trust me on this, Sergiy has not only read some of my stuff, he's had a hand in making some of that stuff better either directly or indirectly, as have you and many other people.
That being said, let's please avoid a "Banker's Rounding" discussion on this thread. Thanks guys.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2016 at 3:29 pm
Alan.B (1/7/2016)
Using a CTE and keeping the code compatible with SQL 2005, we could re-write the code like this:
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
),
E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E3;
Huge, Huge improvement!!! Irrefutable evidence of the usefulness of a CTE.
Compare to this code:
declare @pString VARCHAR(8000), @pDelimiter CHAR(1) select @pString = 'aaaa,bbbbb,,dddd,', @pDelimiter = ','
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM dbo.TallyGenerator(1, DATALENGTH(@pString), DEFAULT, DEFAULT) tg
That's really a huge improvement!
3 times less of lines of code!
Just imagine trying to re-write Adam Machanic's make_parallel function without a CTE :hehe::hehe::hehe::hehe:
Can you actually re-write it using CTE?
I bet you never did it in your life.
You always used copy-paste and never wrote the code yourself.
Such things must be made into views or ITF's, which stored pre-compiled do not need to be parsed every time they run.
And guess what - no matter how long will be the code of make_parallel function without using CTE it will be compiled into exactly the same machine code as the CTE version.
No advantage of any of the approaches whatsoever.
But this brings us to the main problem with CTE, as I see it.
It allows developers, lets them, no, even pushes them towards putting "everything into one query".
Which brings us to the practices of those primitive programming languages which existed on the early ages of computing, when structured programming was not yet introduced and the word "subroutine" was not yet known.
Huge blocks of code get copy-pasted from query to query, from procedure to procedure, instead of being placed into a standard well optimised and tested subroutine (like a view or ITF) and being reused (remember this word from the programming courses?) everywhere in the code base.
Somebody mentioned company policies which do not allow creating new objects on the databases.
I wonder - do the same policies ban the use of the keyword "include"? Because "include" is way more dangerous - is pulls some 3rd party modules into your application, and you cannot see how it is written, what it actually does and what bugs it introduces into the system.
How come a manager or a lead developer who introduces a policy banning structured programming being able to stay on his/her job till the end of the day?
It seems like the movie "Idiocracy" was not that far from reality.
It all looks like a de-volution in programming.
And CTE are a major part of this de-volution.
Therefore I believe CTE's must be removed from common practices and be used only "by permission from the superior".
_____________
Code for TallyGenerator
January 7, 2016 at 3:48 pm
Jeff Moden (1/7/2016)
Jason A. Long (12/24/2015)
Unless you're using recursion, a CTE can be rewritten as a derived table... Considering that derived tables (and CTEs) are processed before the outer query, CTEs can allow the code to laid out in an order that more closely resembles the actual processing order.That said, if there isn't a logical reason to use a CTE, don't...
I'm pretty sure that CTEs aren't necessarily processed before anything else any more than a view would be processed first.
Perhaps not physically (internally, SQL Server can execute the plan however it deems to be most efficient) but logically... That's a different story.
If you consider the logical order of operations:
1) FROM
2) WHERE
3) GROUP BY
4) HAVING
5) SELECT
6) ORDER BY
Any table expression (derived tables, CTEs, views & TVPs) would have to be evaluated in full prior to the outer query, so that it's available when the "FROM operation" of the outer query takes place. In the referenced post, I only mentioned derived tables & CTEs because VIEWS & TVFs weren't focus of the conversation.
Edit: As a side note... When I included TVFs in that statement, I was referring to the "parameterized view" type of TVF... Not a TVF that's added at the row level with an APPLY operator... I guess we could call that a "correlated sub-query" type of TVF???
January 7, 2016 at 4:02 pm
Sergiy (1/7/2016)
Alan.B (1/7/2016)
Using a CTE and keeping the code compatible with SQL 2005, we could re-write the code like this:
...
Huge, Huge improvement!!! Irrefutable evidence of the usefulness of a CTE.
Compare to this code:
...
That's really a huge improvement!
3 times less of lines of code!
Just imagine trying to re-write Adam Machanic's make_parallel function without a CTE :hehe::hehe::hehe::hehe:
Can you actually re-write it using CTE?
I bet you never did it in your life.
You always used copy-paste and never wrote the code yourself.
Such things must be made into views or ITF's, which stored pre-compiled do not need to be parsed every time they run.
And guess what - no matter how long will be the code of make_parallel function without using CTE it will be compiled into exactly the same machine code as the CTE version.
No advantage of any of the approaches whatsoever.
But this brings us to the main problem with CTE, as I see it.
It allows developers, lets them, no, even pushes them towards putting "everything into one query".
Which brings us to the practices of those primitive programming languages which existed on the early ages of computing, when structured programming was not yet introduced and the word "subroutine" was not yet known.
Huge blocks of code get copy-pasted from query to query, from procedure to procedure, instead of being placed into a standard well optimised and tested subroutine (like a view or ITF) and being reused (remember this word from the programming courses?) everywhere in the code base.
Somebody mentioned company policies which do not allow creating new objects on the databases.
I wonder - do the same policies ban the use of the keyword "include"? Because "include" is way more dangerous - is pulls some 3rd party modules into your application, and you cannot see how it is written, what it actually does and what bugs it introduces into the system.
How come a manager or a lead developer who introduces a policy banning structured programming being able to stay on his/her job till the end of the day?
It seems like the movie "Idiocracy" was not that far from reality.
It all looks like a de-volution in programming.
And CTE are a major part of this de-volution.
Therefore I believe CTE's must be removed from common practices and be used only "by permission from the superior".
Then we should ban the use of views as well. How many times have we seen queries written with views nested in views, nested in views such that the performance of the main query is abysmal.
Should ban the use of UDFs as well as they can cause serious performance issues as well.
Where does it end?
A better way is to teach the proper use of the various constructs available in what ever flavor of SQL you are using.
Yes, I have done cut and paste with code, but then I usually also end up modifying it to suit the purpose at hand. I also make sure I fully understand what the code is doing so that I can support it and modify it as requirements change.
I am not stupid and I don't appreciate being called such explicitly or implicitly.
Sergiy, you have a lot to offer the SQL Community, too bad your no better online than Mr. Celko.
January 7, 2016 at 4:10 pm
I kinda miss Celko... 🙁
January 7, 2016 at 4:30 pm
Jeff Moden (1/7/2016)
That being said, let's please avoid a "Banker's Rounding" discussion on this thread. Thanks guys.
Too late, mate.
Sorry. 😛
But honestly, there is nothing wrong with using CTE's - in those rear cases when they may be useful.
It's only wrong to make them a mainstream in T-SQL programming.
At the end of the day, SQL queries have their own structure, their order of execution, and CTE's do not fit that structure.
They are more like a hack in SQL.
And that's how they should be treated.
INHO.
_____________
Code for TallyGenerator
January 7, 2016 at 4:39 pm
Lynn Pettis (1/7/2016)
Then we should ban the use of views as well. How many times have we seen queries written with views nested in views, nested in views such that the performance of the main query is abysmal.Should ban the use of UDFs as well as they can cause serious performance issues as well.
Where does it end?
I've misread my post.
I actually did not favour any bans. I did oppose bans.
In particular - on creating views and ITF's when they are needed.
I only merely suggested a ban for the profession for those who make up such bans. :hehe:
BTW, I use views on views a lot.
One time I even reached the nesting limit.
Did not notice any performance issues caused by that.
Yes, I have done cut and paste with code
Me too.
Last time - 7 minutes ago.
🙂
_____________
Code for TallyGenerator
January 7, 2016 at 4:53 pm
Lynn Pettis (1/7/2016)
Sergiy (1/7/2016)
Alan.B (1/7/2016)
Using a CTE and keeping the code compatible with SQL 2005, we could re-write the code like this:
...
Huge, Huge improvement!!! Irrefutable evidence of the usefulness of a CTE.
Compare to this code:
...
That's really a huge improvement!
3 times less of lines of code!
Just imagine trying to re-write Adam Machanic's make_parallel function without a CTE :hehe::hehe::hehe::hehe:
Can you actually re-write it using CTE?
I bet you never did it in your life.
You always used copy-paste and never wrote the code yourself.
Such things must be made into views or ITF's, which stored pre-compiled do not need to be parsed every time they run.
And guess what - no matter how long will be the code of make_parallel function without using CTE it will be compiled into exactly the same machine code as the CTE version.
No advantage of any of the approaches whatsoever.
But this brings us to the main problem with CTE, as I see it.
It allows developers, lets them, no, even pushes them towards putting "everything into one query".
Which brings us to the practices of those primitive programming languages which existed on the early ages of computing, when structured programming was not yet introduced and the word "subroutine" was not yet known.
Huge blocks of code get copy-pasted from query to query, from procedure to procedure, instead of being placed into a standard well optimised and tested subroutine (like a view or ITF) and being reused (remember this word from the programming courses?) everywhere in the code base.
Somebody mentioned company policies which do not allow creating new objects on the databases.
I wonder - do the same policies ban the use of the keyword "include"? Because "include" is way more dangerous - is pulls some 3rd party modules into your application, and you cannot see how it is written, what it actually does and what bugs it introduces into the system.
How come a manager or a lead developer who introduces a policy banning structured programming being able to stay on his/her job till the end of the day?
It seems like the movie "Idiocracy" was not that far from reality.
It all looks like a de-volution in programming.
And CTE are a major part of this de-volution.
Therefore I believe CTE's must be removed from common practices and be used only "by permission from the superior".
Then we should ban the use of views as well. How many times have we seen queries written with views nested in views, nested in views such that the performance of the main query is abysmal.
Should ban the use of UDFs as well as they can cause serious performance issues as well.
Where does it end?
A better way is to teach the proper use of the various constructs available in what ever flavor of SQL you are using.
Yes, I have done cut and paste with code, but then I usually also end up modifying it to suit the purpose at hand. I also make sure I fully understand what the code is doing so that I can support it and modify it as requirements change.
I am not stupid and I don't appreciate being called such explicitly or implicitly.
Sergiy, you have a lot to offer the SQL Community, too bad your no better online than Mr. Celko.
Amen
-- Itzik Ben-Gan 2001
January 7, 2016 at 5:26 pm
Lynn Pettis (1/7/2016)
I am not stupid
Don't you sense a bit of Catch 22 here? 😉
and I don't appreciate being called such explicitly or implicitly.
You should not worry about such things. At all.
"The words of Peter about Paul tell us more about Peter than about Paul".
I never suggested that anyone who ever used CTE is stupid.
That would be pretty much everyone - right?
My point is - CTE allows primitive programming practices into SQL and lows the barrier for those, let's say, uneducated developers to enter the workplace.
That leads to overall degradation of database solutions, continuous issues with performance, scalability, deadlocks, explosive growth of tempdb files, etc.
As a result - it's a common knowledge nowadays that SQL Server is for small data sets only, if you need to scale - go for a NoSQL solution.
We all know it's not true. But try to silence those masses of MS certified developers who design horrible solutions, use inappropriate practices and make business managers believe that SQL Server is a hopeless platform.
_____________
Code for TallyGenerator
January 7, 2016 at 6:25 pm
Sergiy (1/7/2016)
Jeff Moden (1/7/2016)
That being said, let's please avoid a "Banker's Rounding" discussion on this thread. Thanks guys.Too late, mate.
Sorry. 😛
BWAAA-HAAA!!!! Only 300 or 400 more posts to go! I'll get the popcorn! 😀
But honestly, there is nothing wrong with using CTE's - in those rear cases when they may be useful.
It's only wrong to make them a mainstream in T-SQL programming.
At the end of the day, SQL queries have their own structure, their order of execution, and CTE's do not fit that structure.
They are more like a hack in SQL.
And that's how they should be treated.
INHO.
Heh... you should see the post I wrote for this thread about two hours ago but decided to delete, instead. Much longer but similar.
Ok... popcorn's ready. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2016 at 6:32 pm
Jason A. Long (1/7/2016)
Jeff Moden (1/7/2016)
Jason A. Long (12/24/2015)
Unless you're using recursion, a CTE can be rewritten as a derived table... Considering that derived tables (and CTEs) are processed before the outer query, CTEs can allow the code to laid out in an order that more closely resembles the actual processing order.That said, if there isn't a logical reason to use a CTE, don't...
I'm pretty sure that CTEs aren't necessarily processed before anything else any more than a view would be processed first.
Perhaps not physically (internally, SQL Server can execute the plan however it deems to be most efficient) but logically... That's a different story.
If you consider the logical order of operations:
1) FROM
2) WHERE
3) GROUP BY
4) HAVING
5) SELECT
6) ORDER BY
Any table expression (derived tables, CTEs, views & TVPs) would have to be evaluated in full prior to the outer query, so that it's available when the "FROM operation" of the outer query takes place. In the referenced post, I only mentioned derived tables & CTEs because VIEWS & TVFs weren't focus of the conversation.
Edit: As a side note... When I included TVFs in that statement, I was referring to the "parameterized view" type of TVF... Not a TVF that's added at the row level with an APPLY operator... I guess we could call that a "correlated sub-query" type of TVF???
Can't argue that without an example. I'll have to check on some of the execution plans on my prod system. I'm pretty sure I've seen where the underlying tables were not the rightmost tables in the execution plan. I'm normally concerned with the overall performance than whose on stage when so don't have such an example off the top of my head. I'm also not agreeing with you yet because you can't argue that without an example, either. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2016 at 8:40 pm
Jeff Moden (1/7/2016)
Can't argue that without an example. I'll have to check on some of the execution plans on my prod system. I'm pretty sure I've seen where the underlying tables were not the rightmost tables in the execution plan. I'm normally concerned with the overall performance than whose on stage when so don't have such an example off the top of my head. I'm also not agreeing with you yet because you can't argue that without an example, either. 😀
Well said and all fair points... This quick, little test actually appears to throw it into your favorite category... "IT DEPENDS"
USE AdventureWorks;
GO
SELECT
CustomerID = p.BusinessEntityID,
CustomerName = CONCAT(p.FirstName + ' ', p.MiddleName + '. ', p.LastName),
so.OrderCount,
so.OrderQty,
so.LineTotal
FROM
Person.Person p
JOIN (
SELECT
soh.CustomerID,
OrderCount = COUNT(*),
OrderQty = SUM(sd.OrderQty),
LineTotal = SUM(sd.LineTotal)
FROM
Sales.SalesOrderHeader soh
JOIN (
SELECT
sod.SalesOrderID,
OrderQty = SUM(sod.OrderQty),
LineTotal = SUM(sod.LineTotal)
FROM
Sales.SalesOrderDetail sod
GROUP BY
sod.SalesOrderID
) sd
ON soh.SalesOrderID = sd.SalesOrderID
GROUP BY
soh.CustomerID
) so
ON p.BusinessEntityID = so.CustomerID
--WHERE
--p.BusinessEntityID = 12079
;
Without the WHERE clause, all 3 tables are being scanned and all aggregation operators are taking place before the join operators... Indicating that the inner most derived tables are being evaluated first.
But... Add the where clause (predicated on the PK of the outermost table) and that's no longer the case... All 3 tables have index seeks and the details & header table are joined before the aggregations...
This indicates that SQL Server is smart enough to recognize that it can use the outer query predicate to avoid the unnecessary work rolling up values that will be discarded by the outer query.
Not sure if this falls under the category of "logical" or "physical" order of operations... I just know the distinction isn't a hill worth dying over...
Call it a draw? 😀
January 7, 2016 at 8:58 pm
jaime.simancas 27970 (1/7/2016)
Alan.B (1/7/2016)
erics44 (1/7/2016)
Lynn Pettis (1/7/2016)
You have to remember that many times threads go off on tangents, and sometimes the tangents actually impart knowledge that may not have been provided otherwise.I don't believe that this thread was spoilt at all.
maybe it will take off again then
The interesting thing about the tangent is that an important benefit of CTE's was demonstrated. Unlike a subquery, the CTE can be referenced multiple times. If you look at the splitter examples you'll see that the "dummy rows" tables are not re-usable. For example, let's start with the tally table logic for Jason Longs CTE-less splitter:
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
(
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E1 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E2 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E3 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E4 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E5 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E6 (n)
) DummyRows(c);
Using a CTE this could be re-written like this:
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E1 (n)),
E3(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b CROSS JOIN E1 c)
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E3 a CROSS JOIN E3 b
Jason's does not work in SQL 2005 because of the VALUES constructor but Sergiy's does. Let's look at his tally table logic:
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
(
SELECT 1 FROM
(
SELECT 1 FROM
(
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
) a (N) ,
(
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
) b (N)
) a (N),
(
SELECT 1 FROM
(
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
) a (N) ,
(
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
) b (N)
) b (N)
) iTally (N);
Using a CTE and keeping the code compatible with SQL 2005, we could re-write the code like this:
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
),
E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E3;
Huge, Huge improvement!!! Irrefutable evidence of the usefulness of a CTE.
Just imagine trying to re-write Adam Machanic's make_parallel function without a CTE :hehe::hehe::hehe::hehe:
CREATE FUNCTION dbo.make_parallel()
RETURNS TABLE AS
RETURN
(
WITH
a(x) AS
(
SELECT
a0.*
FROM
(
VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
) AS a0(x)
),
b(x) AS
(
SELECT TOP(9223372036854775807) 1
FROM
a AS a1,
a AS a2,
a AS a3,
a AS a4
WHERE
a1.x % 2 = 0
)
SELECT
SUM(b1.x) AS x
FROM
b AS b1
HAVING
SUM(b1.x) IS NULL
)
GO
BRILLIANT!
Thanks.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 121 through 135 (of 161 total)
You must be logged in to reply to this topic. Login to reply