September 25, 2013 at 1:00 pm
Carlo Romagnano (9/25/2013)
With complicated CTE, the optimizer does not choose the best plan.
What is your source for this opinion about the optimizer?
I would be good to share this information Carlo so we can all learn from it.... 😎
PS - Kudos to L' Eomot for giving a very detailed explanation and set of examples and references for his opinion.
PPS - Super Double Chocolate Kudos to Lynn for his ultra factual and super cool response.
Can't wait until that future day when I can make using that semicolon a code rule for reasons other than "because". 😎
September 25, 2013 at 1:02 pm
Carlo Romagnano (9/25/2013)
John Mitchell-245523 (9/25/2013)
Carlo Romagnano (9/25/2013)
CTE is useless and complicated!Clearly not useless, even if your personal preference is not to use them. And no more or less complicated, in my opinion, than your example.
The same result is reachable in a standard way:
CTEs are part of the ANSI 99 standard.
... and you do not need semicolon.
Semicolons will become mandatory for all statements in a future version, so we may as well get used to using them.
With complicated CTE, the optimizer does not choose the best plan.
Do you have any references to support that, please?
John
CTE is usefull only in recursive query for tally table:
with tally (Num)AS(
select 0 AS Num
UNION ALL
select 1+ tally.Num AS Num
from tally
)
select top(20000) * from tally
OPTION (MAXRECURSION 20000);
No recursion needed to create a dynamic tally table with a CTE:
-- Code below is SQL Server 2008 or newer
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 cross join e1 b), -- 100 rows
e4(n) as (select 1 from e2 a cross join e2 b), -- 10,000 rows
eTally(n) as (select row_number() over (order by (select null)) from e4 a cross join e2 b) -- 1,000,000 rows
select n from eTally;
Also, this scales better than a recursive counting routine.
September 25, 2013 at 1:27 pm
This is really smart, Lynn.
BTW, will get back to you offline later today.
September 26, 2013 at 1:10 am
No recursion needed to create a dynamic tally table with a CTE:
-- Code below is SQL Server 2008 or newer
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 cross join e1 b), -- 100 rows
e4(n) as (select 1 from e2 a cross join e2 b), -- 10,000 rows
eTally(n) as (select row_number() over (order by (select null)) from e4 a cross join e2 b) -- 1,000,000 rows
select n from eTally;
Also, this scales better than a recursive counting routine.
But my script is simpler and more efficient:
with tally (Num)AS
(
select 1 AS Num
UNION ALL
select 1+ tally.Num AS Num
from tally
WHERE tally.Num < 100
)
select ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
from tally T100
,tally T10000
,tally T1000000
,tally T100000000
To add more numbers add another CROSS JOIN: ,tally T1000000
September 26, 2013 at 2:04 am
But my script is simpler
I know it's a matter of preference, but I'd rather have any complexity tucked away in the CTE definition than in the body of the query, something like this:
WITH eTally AS (...)
SELECT LEFT(@MyString,n)
FROM eTally;
... as opposed to this:
WITH tally AS (...)
SELECT LEFT(@MyString,n)
FROM tally T100
,tally T10000
,tally T1000000
,tally T100000000;
and more efficient
I'd be interested to see any figures you have on that. Not a scientific test, I admit, but Lynn's ran in three seconds on my computer, whereas yours ran for about a minute and then crashed with an out of memory error.
John
September 26, 2013 at 2:37 am
John Mitchell-245523 (9/26/2013)
But my script is simpler
I know it's a matter of preference, but I'd rather have any complexity tucked away in the CTE definition than in the body of the query, something like this:
WITH eTally AS (...)
SELECT LEFT(@MyString,n)
FROM eTally;
... as opposed to this:
WITH tally AS (...)SELECT LEFT(@MyString,n)
FROM tally T100
,tally T10000
,tally T1000000
,tally T100000000;
and more efficient
I'd be interested to see any figures you have on that. Not a scientific test, I admit, but Lynn's ran in three seconds on my computer, whereas yours ran for about a minute and then crashed with an out of memory error.
John
On my server sql2008 r2 my script runs in 2 seconds vs 6 secs of Lynn's.
September 26, 2013 at 3:23 am
excellent .. question?
September 26, 2013 at 4:19 am
Carlo Romagnano (9/26/2013)
On my server sql2008 r2 my script runs in 2 seconds vs 6 secs of Lynn's.
The difference is because you've added an extra join so are returning more rows. Presumably the version you are running doesn't have this.
ie instead of
SELECT LEFT(@MyString,n)
FROM tally T100
,tally T10000
,tally T1000000
,tally T100000000;
it should be
SELECT LEFT(@MyString,n)
FROM tally T100
,tally T10000
,tally T1000000;
Even like this though, for me Lynn's takes 4 seconds and yours takes 5.
September 26, 2013 at 4:21 pm
Mr. Kapsicum (9/24/2013)
but what about ;(semi colon) of WITH ?i guess, it should generate an error? :discuss:
semi-colon ( ; ) is a statement terminator.
It is not required to begin a CTE with a semi-colon. The placement of a semi-colon prior to with is a bad practice.
If a CTE is the first statement in the batch no semi-colon is needed.
Also consider this example:
create table #temp_test
(id int);
insert into #temp_test
values(1)
insert into #temp_test
values(2)
insert into #temp_test
values(3)
insert into #temp_test
values(5)
BEGIN
with ABC_CTE as
(select * from #temp_test)
delete from ABC_CTE where id = 1;
SELECT *
FROM #temp_test
END
This will also work without problem and there is no semi-colon preceding the with of the CTE.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2013 at 12:19 am
SQLRNNR (9/26/2013)
Mr. Kapsicum (9/24/2013)
but what about ;(semi colon) of WITH ?i guess, it should generate an error? :discuss:
semi-colon ( ; ) is a statement terminator.
It is not required to begin a CTE with a semi-colon. The placement of a semi-colon prior to with is a bad practice.
If a CTE is the first statement in the batch no semi-colon is needed.
Also consider this example:
create table #temp_test
(id int);
insert into #temp_test
values(1)
insert into #temp_test
values(2)
insert into #temp_test
values(3)
insert into #temp_test
values(5)
BEGIN
with ABC_CTE as
(select * from #temp_test)
delete from ABC_CTE where id = 1;
SELECT *
FROM #temp_test
END
This will also work without problem and there is no semi-colon preceding the with of the CTE.
While all you write is true and the code will work, it does not really follow best practices. All statements(*) should be terminated with a semicolon. This has been optional for a long time, but that is now deprecated. Properly terminating a MERGE statement as well as any statement before a CTE is already required; one day this same requirement may hold for all statements. Everyone should get into the habit of terminating all statements.
(*) There is a lot of confusion on BEGIN and END keywords. Some people (like me) consider them similar to statements and like to terminate them. Others equate them to the { and } marks in C# code and don't terminate them. Both groups grudgingly have to accept some exceptions to their rule, as the T-SQL parser is inconsistent in this. For the point you are trying to illustrate with your message, I understand that you need to omit the semicolons after BEGIN and END.
Here is how I would have written your code:
create table #temp_test
(id int);
insert into #temp_test
values(1);
insert into #temp_test
values(2);
insert into #temp_test
values(3);
insert into #temp_test
values(5);
BEGIN
with ABC_CTE as
(select * from #temp_test)
delete from ABC_CTE where id = 1;
SELECT *
FROM #temp_test;
END
September 27, 2013 at 10:06 am
Hugo Kornelis (9/27/2013)
(*) There is a lot of confusion on BEGIN and END keywords. Some people (like me) consider them similar to statements and like to terminate them. Others equate them to the { and } marks in C# code and don't terminate them. Both groups grudgingly have to accept some exceptions to their rule, as the T-SQL parser is inconsistent in this.
Well, the syntax definition in BOL
BEGIN{ sql_statement | statement_block }
END
seems to make it quite clear that if you write a semicolon after BEGIN you are inserting a statement consisting of no characters. However, that's presumably harmless - a statement consisting of no characters is ignore in some places and after BEGIN is one of them; the handling of empty statements after else or while is different - there's a real inconsistency. I haven't seen any difficulties with ";" after END either, and if we want statements terminated by ";" it would make sense to define that syntax so that END has to be followed by ";".
However, that stuff in BOL is a pretty rotten syntax definition - incidentally it shows up in at least two forms, but that's not the problem as they differ only in layout (white space, including line breaks); the real problem is that it appears to say you can have either an sql_statement or a statement_block, but nowhere is the syntax of a statement_block defined; nor is the syntax of an sql_statement defined; nor is what's being defined here named.
However, I don't see MS deciding to have a complete BNF definition of T-SQL syntax; if they had one, they would surely have published it in BOL; and if they don't have one, they already have a parser which doesn't rely on having it, so why should they bother?
Tom
September 27, 2013 at 11:25 am
L' Eomot Inversé (9/27/2013)
Well, the syntax definition in BOL
BEGIN{ sql_statement | statement_block }
END
seems to make it quite clear that if you write a semicolon after BEGIN you are inserting a statement consisting of no characters.
Unfortunately, there are statements that will throw an error if you put them immediately after BEGIN or END, but work fine if you add a semicolon.
I seem to recall running into this with a CTE after BEGIN, but I cannot reproduce that. Maybe they fixed that somewhere between SQL 2005 and SQL 2012? I think this still happens with other statements, though - but I cannot remember what statement(s) at the time.
However, using a CTE directly after END does still throw an error, even on SQL2012. And it works okay if you add the semicolon. So apparently, no semicolon after BEGIN in the syntax diagram is not equivalent to no semicolon after END.
September 27, 2013 at 1:42 pm
Hugo Kornelis (9/27/2013)
L' Eomot Inversé (9/27/2013)
Well, the syntax definition in BOL
BEGIN{ sql_statement | statement_block }
END
seems to make it quite clear that if you write a semicolon after BEGIN you are inserting a statement consisting of no characters.
Unfortunately, there are statements that will throw an error if you put them immediately after BEGIN or END, but work fine if you add a semicolon.
I seem to recall running into this with a CTE after BEGIN, but I cannot reproduce that. Maybe they fixed that somewhere between SQL 2005 and SQL 2012? I think this still happens with other statements, though - but I cannot remember what statement(s) at the time.
However, using a CTE directly after END does still throw an error, even on SQL2012. And it works okay if you add the semicolon. So apparently, no semicolon after BEGIN in the syntax diagram is not equivalent to no semicolon after END.
Yes, but the function of that syntax diagram is to define the BEGIN...END unit, so it covers BEGIN, and END, and everything in between but not anything before BEGIN or after END.
Anyway it looks like the right had side of a production but since it has has no left hand side it would be unreasonable to assume that it's a production for sql_statement, so we can't deduce anything from the absence of a semicolon after END in this diagram.
Incidentally, I still regard introducing ; as a statement terminator while not introducing a closing keyword for an if statement is just plain silly; it perpetuates to the situation where an if - if - else sequence has to have the second if statement in a begin-end block if the else belongs to the second if; but that makes end effectively a satement terminator, unless we write end;, but then it appears that the ; statement terminator can't terminate an if statement - because if - if - ;;else still assigns the else to the second if, not the first. The first ; of the ;; sequence terminates the statement in the then branch of the second if, leaving the second if statement open; logically the second ; in that ;; sequence would then terminate the second if statement, leaving the first if statement to pick up the else, but that's not what happens: the second ; is ignored. So end is currently a statement terminator, at least some of the time, and functions as one in places where ; doesn't.
Also if <condition>; is an error, so there's another place an empty statement can't occur, and replacing that semicolon by end doesn't affect the outcome provided there's a begin still open at the position of the if - same error. So here again is end being a statement terminator.
It all seems a bit of a pigs ear. I wish they would get the introduction the ";" mandatory statement terminator over with so that we can know what the language will actually be, rather that looking at how ; and end behave in current systems and trying to exrapolate from there.
Tom
September 28, 2013 at 2:05 am
Thanx for the easy question & easy point; didn`t have to run the code this time 😉
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 28, 2013 at 6:32 am
Nice question.. Smith..
Viewing 15 posts - 46 through 60 (of 68 total)
You must be logged in to reply to this topic. Login to reply