June 3, 2016 at 8:26 am
Terje Hermanseter (6/3/2016)
I don't understand why MS is wavering on the commitment to enforce them as they've been part of the ISO-1999 standard for a very long time.
Too much old code will break.
The prospect of code breaking hasn't stopped Microsoft removing "features" in the past.
For example, until SQL Server 2000 you could include ORDER BY in views. I was fairly new to SQL when SQL 2005 came out, and learnt a hard and very valuable lesson when we upgraded - I had to amend quite a lot of code and reports which were relying on the old feature.
June 3, 2016 at 8:54 am
"Too much existing code to break".
Microsoft did push Visual Basic dot nyet invalidating VB 6 code.
Microsoft did bump Silverlight off the main path - though still supported, the new Edge browser does not tolerate Silverlight plug-ins. Silverlight remains supported on IE for a while, until edged out by the new Win10 Edge browser.
So ...
Finally, many large corporations will not adopt the latest SQL Server version overnight. Will the new "mandatory terminator" version offer downward compatibility as existing versions do ?
But yes, I am getting into the habit of using the terminator semi-colon. In fact, I put two spaces in front of it to make it stand out as something new.
June 3, 2016 at 8:55 am
NO WHERE (except in their inappropriately coded samples) in Books on Line does Microsoft say that a CTE needs to start with a semicolon if the previous statement is not terminated by a semicolon. In fact Books on Line specifically states that the previous statement must be terminated by a semicolon.
It is a crutch used by people who fail to terminate SQL statements with a semicolon. Microsoft has deprecated NOT terminating SQL statements with a semicolon. We all should be using semicolons as terminators not BEGININATORs.
I want my point back.
June 3, 2016 at 9:06 am
Terje Hermanseter (6/3/2016)
I don't understand why MS is wavering on the commitment to enforce them as they've been part of the ISO-1999 standard for a very long time.
Too much old code will break.
And the compiler can obviously handle the code without ;'s. I get that it's harder on the compiler but I don't particularly care as this is a product that we pay a chunk of money for.
June 3, 2016 at 9:26 am
The correct answer is "none of the above".
First, semicolons don't terminate batches, they terminate statements.
From TechNet: "A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. ... Each Transact-SQL statement should be terminated with a semicolon." (https://technet.microsoft.com/en-us/library/ms175502(v=sql.105).aspx)
Batches can be separated in SSMS by "GO" (default) or the configured batch-separator (Tools > Options > Query Execution). Statements do not always need to be terminated with a semicolon, but should be (ibid).
Even a CTE doesn't always need a semicolon on the code before it. For example, try this:
CREATE VIEW dbo.ViewWithCTE
AS
WITH CTE
AS (SELECT
1 AS C
)
SELECT
CTE.C
FROM
CTE;
GO
SELECT * FROM dbo.ViewWithCTE;
Note that the WITH does not have a semicolon before it. This is because it does not have a previous SQL "statement", as per the document.
Semantically, the question is poorly worded and technically incorrect.
(I picked the "right" answer anyway. But the question could be improved.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2016 at 9:40 am
Terje Hermanseter (6/3/2016)
I always use them and encourage others to do the same.
I've been too sloppy. I need to be more consistent and start to use them even when I only writes single statements. That way I think I will enforce the habit of always using them.
I don't always use semicolons, but when I do, I put them on a separate line.
Having them on a separate line serves to both indicate clearly that it's there, and makes it easier to edit the statements on either side (especially since I use the <Ctrl-Enter> (extensively) and <Ctrl-Shift-Enter> (when I remember) keyboard shortcuts to create new lines).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 3, 2016 at 10:41 am
I don't always drink beer, but when I do, I put a semi-colon in front of it. 😀
This is why I like Mladen Prajdic's SSMS Tools[/url]: Ctrl-A, Ctrl-K, Ctrl-G: all statements end in semi-colons.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 3, 2016 at 12:15 pm
Wayne West (6/3/2016)
I don't always drink beer, but when I do, I put a semi-colon in front of it. 😀This is why I like Mladen Prajdic's SSMS Tools[/url]: Ctrl-A, Ctrl-K, Ctrl-G: all statements end in semi-colons.
This is part of the reason that I'm lazy about adding semicolons. I know that I can always use SSMS Tools to add them quickly if necessary.
Another reason is that I like consistency, and much of our code is missing semicolons, but I don't want to just go and add the semicolons, because our source control system will flag them all as changes that need to be reviewed.
Drew
PS: You also don't need the Ctrl-A to select the entire text. Ctrl-K, Ctrl-G will automatically update the entire text unless you've specifically selected a section of text to update.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 3, 2016 at 12:44 pm
I call foul on this one. The only correct answer is "No Never". As Lynn has ranted before "the semi-colon is NOT a statement beginninator, it is a statement terminator". Even the documentation states that the previous statement must be terminated. Nowhere that I can find does it state you need to start a cte with a semicolon.
Happy friday everyone!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 3, 2016 at 12:58 pm
drew.allen (6/3/2016)
Wayne West (6/3/2016)
I don't always drink beer, but when I do, I put a semi-colon in front of it. 😀This is why I like Mladen Prajdic's SSMS Tools[/url]: Ctrl-A, Ctrl-K, Ctrl-G: all statements end in semi-colons.
... PS: You also don't need the Ctrl-A to select the entire text. Ctrl-K, Ctrl-G will automatically update the entire text unless you've specifically selected a section of text to update.
True dat. I did know about that, but old hobbits die hard.
One thing that's cool is Mladen's responsiveness. I found a slight problem with the reformatter that caused an error when you had XML expressions (there is a component that must be upper case, I don't remember what), I emailed him and he fixed it and had a new release in less than a week.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 3, 2016 at 1:00 pm
Wayne West (6/3/2016)
drew.allen (6/3/2016)
Wayne West (6/3/2016)
I don't always drink beer, but when I do, I put a semi-colon in front of it. 😀This is why I like Mladen Prajdic's SSMS Tools[/url]: Ctrl-A, Ctrl-K, Ctrl-G: all statements end in semi-colons.
... PS: You also don't need the Ctrl-A to select the entire text. Ctrl-K, Ctrl-G will automatically update the entire text unless you've specifically selected a section of text to update.
True dat. I did know about that, but old hobbits die hard.
One thing that's cool is Mladen's responsiveness. I found a slight problem with the reformatter that caused an error when you had XML expressions (there is a component that must be upper case, I don't remember what), I emailed him and he fixed it and had a new release in less than a week.
RedGate SQL Prompt does the same thing. It'll add semicolons, expand wildcards, etc., for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2016 at 1:06 pm
GSquared (6/3/2016)
RedGate SQL Prompt does the same thing. It'll add semicolons, expand wildcards, etc., for you.
SQL Prompt is pretty spiffy, but it's also 10x more expensive. And not only am I in a poor-paying part of the country, I'm losing my job at the end of the month, so DIY or free to cheap is definitely what I need out of my tools.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 3, 2016 at 1:07 pm
I have to agree with Toreador and with Lynn. The option claimed to be correct is in fact incorrect. The only thing that can begin with a semicolon is a statement exactly one character long consisting of a single semicolon and that certainly involves no CTE. So it is impossible to begin anything involving a CTE with a semicolon, and possible only to terminate a statement with one. So if it were ever necessary to have a semicolon to begin a CTE it would be impossible to do it so it had better never be necessary!
So the second option, not the third, is correct (although we should probably say "statement beginning with a CTE" rather than just "CTE" - Gus has a point on the wording).
Tom
June 3, 2016 at 1:08 pm
Aaron N. Cutshall (6/3/2016)
Terje Hermanseter (6/3/2016)
I don't understand why MS is wavering on the commitment to enforce them as they've been part of the ISO-1999 standard for a very long time.
Too much old code will break.
That would be true for a number of things due to version upgrades over the years. It's impossible to preserve 100% backward compatibility and yet move forward. If it's a real problem, then MS could offer a tool to insert semicolons where appropriate by using the same parser that is currently used to determine the end of a statement.
Just a thought...
How very true about backward compatibility. I encounter a deep struggle when I think of a tool to insert to insert semicolons where they're missing. I just think of the wonderful code generator of the past - that nasty view designer in SSMS, VB wizards, etc. It doesn't inspire my confidence at all.
June 3, 2016 at 2:51 pm
We can be pedantic if we want. However, a batch consists of 1 or more statements. The option notes that the previous statement is not terminated, so a ; is needed.
Now, are we beginning a CTE or ending the previous statement? We are ending the previous statement, however since there is very, very inconsistent usage of semicolons, often a code line containing a CTE begins with a semicolon.
I knew this question would provoke some comments. However, pedantically, I didn't state this would be part of the CTE statement. However, I did edit the question to this:
Do I always need a semicolon to begin a line that defines a CTE?
Viewing 15 posts - 16 through 30 (of 62 total)
You must be logged in to reply to this topic. Login to reply