December 15, 2012 at 3:42 pm
I cannot find anything that states that it has become mandatory, but I did find someone claiming that the BOL for SQL 2008 (not R2) stated that it was going to become mandatory in future releases.
Reason for my question is my concerns over technical debt in our environment, and trying to convince my technical and management teams that it would be a good thing to implement now (instead of later) to get out DEVS use to it before it becomes mandatory (we currently are on 2005, planning to move to 2008 R2, and I know it is not mandatory in either of those versions/editions, but would like to implement this best practice now, but need to know if it has become mandatory in 2012).
Thank you in advance!
SQL_ME_RICH
December 15, 2012 at 5:04 pm
SQL_ME_RICH (12/15/2012)
I cannot find anything that states that it has become mandatory, but I did find someone claiming that the BOL for SQL 2008 (not R2) stated that it was going to become mandatory in future releases.Reason for my question is my concerns over technical debt in our environment, and trying to convince my technical and management teams that it would be a good thing to implement now (instead of later) to get out DEVS use to it before it becomes mandatory (we currently are on 2005, planning to move to 2008 R2, and I know it is not mandatory in either of those versions/editions, but would like to implement this best practice now, but need to know if it has become mandatory in 2012).
No the ';' is not mandatory in T-SQL 2012 EXCEPT if you have an unterminated statement before the WITH clause of your CTE (but that's nothing new).
On a personal note, I would recommend making the switch. After a PASS Summit session on T-SQL Best Practices a couple of years ago, I've been trying to get into the habit of explicitly specifying my schema (even when dbo) and terminating my statements in ';'.
Adding the ; semicolons now will get you into the habit when it does become mandatory.
Just my two cents,
Rob
December 15, 2012 at 7:02 pm
The use of a semicolon (;) is also required to terminate the MERGE statement.
December 15, 2012 at 7:54 pm
Thanks you two...I have been doing my best to emphasize this practice, but it is often met with the 'we'll worry about it later' attitude. That is one of the things I am trying to overcome, but just wanted to see if I had some additional firepower to take to the table (like saying it is mandatory - I have to correct that now with one of my colleagues that I told it was mandatory in 2012).
Thanks gang!
December 16, 2012 at 5:53 pm
Another place it seems to be required is on the statement before certain DDL like ENABLE/DISABLE TRIGGER.
I would say it is a good practice to start adopting it now rather than wait until later, especially on new systems or anything that needs to be maintained.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 17, 2012 at 9:35 am
I cannot find anything that states that it has become mandatory, but I did find someone claiming that the BOL for SQL 2008 (not R2) stated that it was going to become mandatory in future releases.
I don't have the book handy but, in 2012 TSQL Fundimentals, Itzek Ben-Gan recommends terminating all SQL statements with a semicolon as a best practice. At PASS 2012 there were a couple presenters who suggested this as well.
-- Itzik Ben-Gan 2001
December 17, 2012 at 10:04 am
December 17, 2012 at 10:18 am
I don't see anything on the ALTER TRIGGER syntax, but here's Merge: http://msdn.microsoft.com/en-us/library/bb510625%28v=SQL.100%29.aspx
The idea was that all statements would require a semi-colon, which might help the language mature and enable easier parser coding in the future, but I'm not sure when this will be required. The rules about when/where seem to be inconsistent and with a ton of legacy code, I'm not sure if MS will ever enforce this.
December 17, 2012 at 11:58 am
In case anyone was curious - the line in the Ben Gan book I was referring to is:
The SQL Server documentation indicates that not terminating T-SQL statements with a semicolon is a depreciated feature. This means that the long-term goal is to enforce use of the semicolon in a future version of the product. That's one more reason to get into the the habit of terminating all of your statements, even where it's currentl not required.
Whatever "future version" means.... :unsure:
Other database platforms require this. For those who aspire to write more portable SQL code may want to consider getting into the habit.
Edit: Typo...
-- Itzik Ben-Gan 2001
December 17, 2012 at 5:43 pm
Steve Jones - SSC Editor (12/17/2012)
I don't see anything on the ALTER TRIGGER syntax...
I can't point you to a reference on it but I have run into it in practice.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 18, 2012 at 10:54 am
I saw an example only today of over-zealous use of the semi-colon terminator, with something like this:
Begin try;
--do stuff
End try
Begin Catch
--error handling stuff here
End Catch;
This does not generate an error - but surely the first semi-colon should?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 18, 2012 at 11:21 am
Phil Parkin (12/18/2012)
I saw an example only today of over-zealous use of the semi-colon terminator, with something like this:
Begin try;
--do stuff
End try
Begin Catch
--error handling stuff here
End Catch;
This does not generate an error - but surely the first semi-colon should?
It does get weird.
IF blah = blah
BEGIN;
do stuff
END;
ELSE
BEGIN;
do stuff
END;
The If statement doesn't get one, but Begin does.
I've been terminating SQL statements with semicolons for years. Not necessary, but I find it helpful.
But the rules for it are definitely funky.
- 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
December 18, 2012 at 11:27 am
The rules are a mess. You can place them (almost) anywhere, which is crazy.
I don't like the idea of them after BEGIN-like statements. I'd see rules as:
Begin try
--do stuff
End try
Begin Catch
--error handling stuff here
End Catch;
and
IF xx
then
begin
---
end
else
begin
--
end
;
That makes sense, to me, but I can see where it falls apart in the wide variety of code we write.
I've been trying to add them to demo code to show a good example, but I'm not sure how appropriate all my terminators are.
December 18, 2012 at 11:51 am
Steve Jones - SSC Editor (12/18/2012)
The rules are a mess. You can place them (almost) anywhere, which is crazy.I don't like the idea of them after BEGIN-like statements. I'd see rules as:
Begin try
--do stuff
End try
Begin Catch
--error handling stuff here
End Catch;
and
IF xx
then
begin
---
end
else
begin
--
end
;
That makes sense, to me, but I can see where it falls apart in the wide variety of code we write.
I've been trying to add them to demo code to show a good example, but I'm not sure how appropriate all my terminators are.
Immediately we have a disagreement:-) I would also terminate the inner Begin...End blocks.
It would not be difficult to get a set of rules together, following the same sort of logic as, say, C#. But enforcing them - that's another story.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 18, 2012 at 11:54 am
Steve Jones - SSC Editor (12/18/2012)
... but I'm not sure how appropriate all my terminators are.
Sarah Conner thinks they're all inappropriate. (Yes, this week is that boring.)
- 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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply