Starting a CTE

Question

Starting a CTE

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

PurpleLady SSCertifiable Points: 7815 More actions · Answer 1

Actually according to Microsoft it's not required at all. Their own example doesn't use it.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Sean Lange - Thursday, July 12, 2018 8:04 AM
lmalatesta - Thursday, July 12, 2018 7:49 AM
Sean Lange - Thursday, July 12, 2018 7:15 AM
I don't know why the coding style of starting a cte became to be but it causes so much confusion.
It came to be because Microsoft made the decision to not enforce the ANSI SQL-92 standards in SQL Server that require each valid SQL statement to be terminated with a semicolon.
So putting a semicolon at the beginning of any CTE became a bit of defensive programming for those who have been around the block and one of those things you just do for people that haven't.
Even worse than not enforcing it is that they DO enforce it but not for everything. Only some statements require the previous one to be properly terminated and only some statements require that they themselves are terminated. So much sloppy code out there because of the lack of requirements for so long they are really kind of stuck. They want to enforce standards but it would break so much code out there.

And some of it will be there own code.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

PurpleLady - Thursday, July 12, 2018 9:16 AM
Actually according to Microsoft it's not required at all. Their own example doesn't use it.

That is the newer examples. The older examples in Books Online when CTE's first became available had the semicolon right before the WITH even though the documentation said that the previous statement had to be terminated with a semicolon.

lmalatesta SSChasing Mays Points: 637 More actions · Answer 4

Sean Lange - Thursday, July 12, 2018 8:04 AM
Even worse than not enforcing it is that they DO enforce it but not for everything. Only some statements require the previous one to be properly terminated and only some statements require that they themselves are terminated.

Leaving statement terminators out was officially deprecated as of SQL Server 2008. One should expect syntax and functionality introduced or updated since then to be dependent on the previously statement being properly terminated.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

lmalatesta - Thursday, July 12, 2018 9:25 AM
Sean Lange - Thursday, July 12, 2018 8:04 AM
Even worse than not enforcing it is that they DO enforce it but not for everything. Only some statements require the previous one to be properly terminated and only some statements require that they themselves are terminated.
Leaving statement terminators out was officially deprecated as of SQL Server 2008. One should expect syntax and functionality introduced or updated since then to be dependent on the previously statement being properly terminated.

Yea, good luck with that happening. Human nature, apparently, to do just what is necessary and nothing more for many people.

lmalatesta SSChasing Mays Points: 637 More actions · Answer 6

Lynn Pettis - Thursday, July 12, 2018 9:27 AM
Human nature, apparently, to do just what is necessary and nothing more for many people.

I fear you overestimate human nature.

Toreador SSChampion Points: 11358 More actions · Answer 7

Steve Jones - SSC Editor - Thursday, July 12, 2018 7:58 AM
lmalatesta - Thursday, July 12, 2018 7:45 AM
The smart money is on Microsoft never making statement terminators mandatory. The amount of T-SQL code that would break is mind boggling.
Not taking that bet. They'll never require it

If they do then it will be configurable via some sort of database or server option
SET ALLOW_SLOPPY_CODE ON

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 8

lmalatesta - Thursday, July 12, 2018 9:25 AM
Leaving statement terminators out was officially deprecated as of SQL Server 2008. One should expect syntax and functionality introduced or updated since then to be dependent on the previously statement being properly terminated.

One can never expect this in older code. If you are making new code, I'd agree. All statements ought to be terminated, but for people working with T-SQL for more than 5 years, they likely won't.

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 9

Toreador - Thursday, July 12, 2018 10:18 AM
If they do then it will be configurable via some sort of database or server option
SET ALLOW_SLOPPY_CODE ON

Erland Skomerskag had a suggestion in Conect for SET STRICT CHECKS or something like that, to start forcing better habits. Not sure they'll add it.

lmalatesta SSChasing Mays Points: 637 More actions · Answer 10

Steve Jones - SSC Editor - Thursday, July 12, 2018 12:02 PM
lmalatesta - Thursday, July 12, 2018 9:25 AM
Leaving statement terminators out was officially deprecated as of SQL Server 2008. One should expect syntax and functionality introduced or updated since then to be dependent on the previously statement being properly terminated.
One can never expect this in older code. If you are making new code, I'd agree. All statements ought to be terminated, but for people working with T-SQL for more than 5 years, they likely won't.

I think we're talking at cross purposes here. I was talking about new syntax and functionality in SQL Server. For example, Microsoft introducing CTE syntax that requires the previous statement be terminated.

I'd largely agree that no one should expect T-SQL programmers to properly terminate every statement. In my experience only SQL folks from an Oracle or Postgres background even try use semicolons in T-SQL.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

lmalatesta - Thursday, July 12, 2018 12:39 PM
I think we're talking at cross purposes here. I was talking about new syntax and functionality in SQL Server. For example, Microsoft introducing CTE syntax that requires the previous statement be terminated.
I'd largely agree that no one should expect T-SQL programmers to properly terminate every statement. In my experience only SQL folks from an Oracle or Postgres background even try use semicolons in T-SQL.

Nope, I have been writing T-SQL for over 20 years and I have tried very hard to be sure to semicolons in my code. I may fail at times, but that is what I strive for and it made moving to CTE's when they became available easy to work with as it didn't mean I had to change my habits.

sknox SSChampion Points: 12388 More actions · Answer 12

Bob Razumich - Wednesday, July 11, 2018 9:51 PM
I am so looking forward to the arguments about where the semicolon should go exactly.

No arguments. The semicolon should always begin the line immediately following the statement it terminates, and it should be the only thing on the line:

SELECT 1 FROM dbo.MyTable ;

SELECT 2 FROM dbo.YourTable ;

This way it's clear to which statement the semicolon belongs, but it doesn't get in the way of adding more code to the statement:
SELECT 1 FROM dbo.MyTable WHERE A = 1 ;

SELECT 2 FROM dbo.YourTable ;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

sknox - Thursday, July 12, 2018 4:26 PM
Bob Razumich - Wednesday, July 11, 2018 9:51 PM
I am so looking forward to the arguments about where the semicolon should go exactly.
No arguments. The semicolon should always begin the line immediately following the statement it terminates, and it should be the only thing on the line:
SELECT 1 FROM dbo.MyTable ;
SELECT 2 FROM dbo.YourTable ;
This way it's clear to which statement the semicolon belongs, but it doesn't get in the way of adding more code to the statement:
SELECT 1 FROM dbo.MyTable WHERE A = 1 ;
SELECT 2 FROM dbo.YourTable ;

I do that while developing code, but move it to the end of each statement when development is completed.

sknox SSChampion Points: 12388 More actions · Answer 14

Lynn Pettis - Thursday, July 12, 2018 4:40 PM
I do that while developing code, but move it to the end of each statement when development is completed.

"when development is completed."
I wish I had that luxury! ; )

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

sknox - Thursday, July 12, 2018 4:47 PM
"when development is completed."
I wish I had that luxury! ; )

Well, at some point things have to go into production. My problem is that most of the things I work on come from production and I have to fix them. I don't see the poor code during development, test, qa, uat.