January 31, 2022 at 9:53 pm
Yeah, it's the "why" really, for the most part.
Look, code any way you want to. It's not my place to dictate or declare anyone wrong, or evil or anything like that.
However, as I said in the editorial, I think we should model appropriate behavior with our code. SELECT * only where it makes sense. Statement terminators as, just that, statement terminators. Good naming standards in the sample code. Format it neatly. Add comments where appropriate. Everything. The whole thing. It's not just about the semi-colon. That's just an example.
The issue was, the person was hitting problems because they were, for want of a better term, a cargo-cult programmer. They were just copying what they saw others do without understanding what they were doing and why. So when it came to the CTE, they just put more semi-colons in because, you do that right?
Personally, as I see more and more requirements for the semi-colon, and as I work in PostgreSQL sometimes, I'm just in the habit of putting it everywhere. I don't have to guess where it should go and where it doesn't have to go, I just do it. Helps that Redgate SQL Prompt will put it in places where I forget.
However, don't get hung on that one thing. It's all of it. Heck, using Extended Events in modern systems instead of Trace. All that sort of thing.
Do you, as an individual have to listen to me? Nope. I'm just some schmoe.
Should you, as a leader, lead by example? Again, you don't have to listen to me at all, but I'd say, yes. And part of that, is making darned sure you're doing things in an appropriate manner so that others learn through your example.
That's the point.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 31, 2022 at 9:53 pm
It would be placed after the End...
I have seen other methods - for example, some will put the semi-colon as the only item on the line:
IF 1 = 0
BEGIN
PRINT 'Oops, something is wrong with the universe.';
END
;
WITH SomeCTE AS
(
SELECt 1 AS Number1
)
SELECT Number1 FROM SomeCTE
I tend to avoid this kind of structure - I would normally have something between the IF and the WITH anyways so wouldn't run into this situation.
And - you forgot the terminating semi-colon after the final select 🙂
BTW - there is nothing in the standard that says the statement terminator cannot be on a line by itself following the statement it is terminating. Although that does appear a bit unusual - for me it would be much better than placing it before the WITH.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2022 at 11:07 pm
Hi Grant,
I'm going to respectfully disagree. I have plenty of experience with programming languages that require trailing semi-colons, but I learned SQL a long time ago and never used semi-colons, so it looks weird to me. Since SQL doesn't require it anywhere (except before a WITH on a CTE) it hasn't been at the top of my adoption list.
I agree that ;WITH looks a little awkward, but it does not warrant comparison to SELECT *. That usually has a real impact on performance. A leading semi-colon on a CTE is just syntax for convenience, not unlike leading commas on projected fields.
February 1, 2022 at 2:15 pm
I'm not sure what my point really was, but I guess it wasn't really arguing about where to put the semicolon. My issue is more with the lack of consistency and lack of guidance. I don't know if the person who put that semicolon in front of the WITH is a "cargo cult programmer". For all I know, that's the standard his/her shop has adopted -- it seems wrong to me but who am I to judge? I'm going to judge, of course, but my opinion isn't worth that much.
I guess what's important is education and sharing of ideas. You putting this out there is just that, and it helps, and I appreciate it -- don't stop banging that drum. Hopefully it'll help folks (including me) learn, and to be more consistent (and "correct") with their code.
February 1, 2022 at 8:32 pm
If the semicolon is required for some (new) statements, consistency would mean using it throughout. But we have a mixture and I can't see that changing.
I know that the semicolon is part of the SQL standard - but why? Many programming languages use it but not all. Guessing there's a long history to the semicolon.
February 1, 2022 at 8:58 pm
dmbaker, I agree. I know the purpose of the semicolon and I always put it in front of the WITH on purpose.
I frequently cut and paste and reuse code, so putting the semi-colon before the WITH keeps me from constantly fixing prior statements it may fall after.
So this behavior is the result of two things
Tried of people stating it doesn't belong there, but totally understand people should know its purpose.
February 1, 2022 at 9:14 pm
It looks like the WITH syntax may be ANSI SQL-99 standard.
Joe Celko may be able to confirm/clarify.
February 2, 2022 at 3:12 pm
I'm not sure what my point really was, but I guess it wasn't really arguing about where to put the semicolon. My issue is more with the lack of consistency and lack of guidance. I don't know if the person who put that semicolon in front of the WITH is a "cargo cult programmer". For all I know, that's the standard his/her shop has adopted -- it seems wrong to me but who am I to judge? I'm going to judge, of course, but my opinion isn't worth that much.
I guess what's important is education and sharing of ideas. You putting this out there is just that, and it helps, and I appreciate it -- don't stop banging that drum. Hopefully it'll help folks (including me) learn, and to be more consistent (and "correct") with their code.
They put it in front of multiple CTE definitions, you know, after the comma when defining another CTE. They were mirroring the behaviors without knowing why the behaviors were done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2022 at 3:38 pm
As someone who has been around too long, I know the purpose of the semi-colon. It is to define the end of a statement. It is possible to put several statements on one line if they are separated by semicolons. MS decided to save everyone a keystroke at the end of single line statements by not requiring a semi-colon, assuming the carriage return at the end of the line would define the end of the statement. This is true unless a line continuation character is used, that is the back slash. In SQLServer there are two cases where the previous statement must end with a semi-colon, WITH and TROW. For those of us who work in SQLServer and Oracle it is a good practice to end all statements with a semi-colon since it is required in Oracle SQL and PL/SQL. I would recommend it as the standard for shops where multiple databases are used.
February 2, 2022 at 5:04 pm
PostgreSQL also sort of, but sort of doesn't, require semi-colon's.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2022 at 9:25 am
How does the parser separate statements when semicolons are not used?
February 3, 2022 at 12:50 pm
Carriage return and line feed acts as a secondary terminator. The one most people use most of the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2022 at 12:57 pm
SELECT 1 SELECT 2
This parses and executes in SSMS with only a space as separator.
February 7, 2022 at 4:49 pm
We used a popular commercial telephony library to implement an IVR system. This library comes with extensive example code. One of the common complaints in the reviews of the software is that it is full of memory leaks. All the functional classes in the library are IDisposable, but none of the example code shows how to properly dispose of the objects. When all the objects are disposed properly, the system can run for a year with no measurable memory leak.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply