December 2, 2015 at 6:25 am
Can't stress semi-colons enough. I didn't have it in my sample, but it should be at the end of each statement. Don't forget to put it at the beginning of each CTE too.
December 2, 2015 at 6:29 am
xsevensinzx (12/2/2015)
Don't forget to put it at the beginning of each CTE too.
!Gah !No
.A semicolon is a statement *terminator* .It is not something that gets placed at the beginning of statements .A CTE requires that the previous statement is terminated with a semicolon !Not that it starts with a semicolon !It does not
.Correctly terminate all your statements with semicolons ,and you're done
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2015 at 7:01 am
xsevensinzx (12/2/2015)
Can't stress semi-colons enough. I didn't have it in my sample, but it should be at the end of each statement. Don't forget to put it at the beginning of each CTE too.
Or...
Just have each statement terminated appropriately with a semi-colon.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 2, 2015 at 7:25 am
GilaMonster (12/2/2015)
xsevensinzx (12/2/2015)
Don't forget to put it at the beginning of each CTE too.!Gah !No
.A semicolon is a statement *terminator* .It is not something that gets placed at the beginning of statements .A CTE requires that the previous statement is terminated with a semicolon !Not that it starts with a semicolon !It does not
.Correctly terminate all your statements with semicolons ,and you're done
Yeah, but it's still good to ensure it happens. Normal statements will not cause conflict without a semicolon. CTE's will. It's an insurance policy. Why not take out life insurance so your loved ones are taken care of when you pass on? :w00t:
December 2, 2015 at 7:31 am
xsevensinzx (12/2/2015)
GilaMonster (12/2/2015)
xsevensinzx (12/2/2015)
Don't forget to put it at the beginning of each CTE too.!Gah !No
.A semicolon is a statement *terminator* .It is not something that gets placed at the beginning of statements .A CTE requires that the previous statement is terminated with a semicolon !Not that it starts with a semicolon !It does not
.Correctly terminate all your statements with semicolons ,and you're done
Yeah, but it's still good to ensure it happens. Normal statements will not cause conflict without a semicolon. CTE's will. It's an insurance policy. Why not take out life insurance so your loved ones are taken care of when you pass on? :w00t:
Because...
CREATE VIEW MyView
AS
;WITH CTE AS(
SELECT OneColumn AS TheColumn
FROM MyTable
)
SELECT The Column
FROM CTE;
It goes the same way with inline table-valued functions.
December 2, 2015 at 8:00 am
The thing is, not everyone ends with a semicolon. So, when you have things like:
WITH Test AS
(
SELECT TOP 1
*
FROM [DW].[dbo].[SomeTable]
)
SELECT * FROM Test;
SELECT TOP 1
*
FROM [DW].[dbo].[SomeTable] -- Missing semicolon
WITH Test AS
(
SELECT TOP 1
*
FROM [DW].[dbo].[SomeTable]
)
SELECT * FROM Test;
That causes an error because someone did not correctly terminate their statement. We can ensure we pick up the slack by...
;WITH Test AS
(
SELECT TOP 1
*
FROM [DW].[dbo].[SomeTable]
)
SELECT * FROM Test
SELECT TOP 1
*
FROM [DW].[dbo].[SomeTable] -- Missing semicolon
;WITH Test AS
(
SELECT TOP 1
*
FROM [DW].[dbo].[SomeTable]
)
SELECT * FROM Test
Otherwise, this would be fine.
WITH Test AS
(
SELECT TOP 1
*
FROM [DW].[dbo].[SomeTable]
)
SELECT * FROM Test;
SELECT TOP 1
*
FROM [DW].[dbo].[SomeTable];
WITH Test AS
(
SELECT TOP 1
*
FROM [DW].[dbo].[SomeTable]
)
SELECT * FROM Test;
December 2, 2015 at 8:12 am
We can compensate for badly written code with more badly written code. Excellent plan.
Correctly terminate all statements with a semicolon (they're statement terminators). The end.
If someone did not correctly terminate their statement with a semicolon, we can ensure we pick up the slack by correctly terminating the statement and then carrying on with the code that we're adding and taking the time later to teach them to correctly terminate their statements.
Confusing the issue by scattering semicolons where they don't need to be does not make code clearer. It just makes it harder for people to understand what the semicolon is used for in T-SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2015 at 8:15 am
GilaMonster (12/2/2015)
We can compensate for badly written code with more badly written code. Excellent plan.Correctly terminate all statements with a semicolon (they're statement terminators). The end.
If someone did not correctly terminate their statement with a semicolon, we can ensure we pick up the slack by correctly terminating the statement and then carrying on with the code that we're adding and taking the time later to teach them to correctly terminate their statements.
Confusing the issue by scattering semicolons where they don't need to be does not make code clearer. It just makes it harder for people to understand what the semicolon is used for in T-SQL.
+1000
December 2, 2015 at 8:17 am
Luis Cazares (12/2/2015)
GilaMonster (12/2/2015)
We can compensate for badly written code with more badly written code. Excellent plan.Correctly terminate all statements with a semicolon (they're statement terminators). The end.
If someone did not correctly terminate their statement with a semicolon, we can ensure we pick up the slack by correctly terminating the statement and then carrying on with the code that we're adding and taking the time later to teach them to correctly terminate their statements.
Confusing the issue by scattering semicolons where they don't need to be does not make code clearer. It just makes it harder for people to understand what the semicolon is used for in T-SQL.
+1000
+1000^2
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 2, 2015 at 8:28 am
Oh, and also...
MERGE must be terminated with a ;
THROW requires that the previous statement be terminated with a ; (although there's no error in most cases if you don't, try and see if you can see why)
Several Service Broker statements must have the previous statement terminated with a semicolon.
Some DDL statements have similar requirements
I think there's a couple of others too. So must I start every statement with a ;, just in case someone writes a MERGE before it and doesn't realise it must be semi-colon terminated?
Plus, not terminating statements has been officially deprecated since 2008.
http://www.dbdelta.com/always-use-semicolon-statement-terminators/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2015 at 8:29 am
GilaMonster (12/2/2015)
We can compensate for badly written code with more badly written code. Excellent plan.Correctly terminate all statements with a semicolon (they're statement terminators). The end.
If someone did not correctly terminate their statement with a semicolon, we can ensure we pick up the slack by correctly terminating the statement and then carrying on with the code that we're adding and taking the time later to teach them to correctly terminate their statements.
Confusing the issue by scattering semicolons where they don't need to be does not make code clearer. It just makes it harder for people to understand what the semicolon is used for in T-SQL.
But, you're really splitting hairs here. You're confusing badly written code with human error. Not correctly terminating a statement can happen to anyone just as it does in all languages where you may forgot to close a statement or correctly return something in your function. It's bugs most of the time and you're not going sit down with everyone on every bug they make. You're going to flag to get fixed later or fix it yourself and log the change.
And I'm sorry, the one time where a semicolon may be used at the front of the statement is not going to fog the code to where it's not readable just as putting commas after a field as opposed to in front of field doesn't either.
December 2, 2015 at 8:35 am
xsevensinzx (12/2/2015)
But, you're really splitting hairs here. You're confusing badly written code with human error. Not correctly terminating a statement can happen to anyone just as it does in all languages where you may forgot to close a statement
Sure, but no one's going to suggest starting statements in C# with a semicolon, just in case someone forgets one on a line of code they're writing.
And no, I'm no confusing badly written code with human error. Code with incorrect placement of statement terminators (or other elements) is badly written. It's not wrong, it's not buggy, it's badly written
Would anyone call this good?
;IF NOT EXISTS (SELECT 1 FROM SomeTable)
BEGIN INSERT INTO SomeTable (Col1, Col2)
VALUES ('a', 1) ;END
It's syntactically valid and bug free, but the block and statement terminators are in odd places.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2015 at 8:39 am
xsevensinzx (12/2/2015)
The thing is, not everyone ends with a semicolon.
But they should.
More and more, it's a requirement for new functionality within SQL Server. Ultimately it's going to be a requirement of the language (at least according to Microsoft).
Instead of trying to prop up poor coding practices, let's fix them.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 2, 2015 at 8:47 am
; is a statement terminator. It belongs at the end of a statement, not the beginning!
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 2, 2015 at 8:50 am
GilaMonster (12/2/2015)
xsevensinzx (12/2/2015)
But, you're really splitting hairs here. You're confusing badly written code with human error. Not correctly terminating a statement can happen to anyone just as it does in all languages where you may forgot to close a statementSure, but no one's going to suggest starting statements in C# with a semicolon, just in case someone forgets one on a line of code they're writing.
And no, I'm no confusing badly written code with human error. Code with incorrect placement of statement terminators (or other elements) is badly written. It's not wrong, it's not buggy, it's badly written
Would anyone call this good?
;IF NOT EXISTS (SELECT 1 FROM SomeTable)
BEGIN INSERT INTO SomeTable (Col1, Col2)
VALUES ('a', 1) ;END
It's syntactically valid and bug free, but the block and statement terminators are in odd places.
You're not doing a fair comparison here though. You're going to the extreme to justify your stance now to say, "You wouldn't put semicolons at the beginning of every statement would you?"
That's not what I'm saying here. I'm saying the one statement where not having a properly terminated statement above it could cause the code to error as a bug, could benefit from always ensuring it starts with a semicolon where appropriate.
I strongly disagree that having a semicolon at the front of a CTE statement is going to cause mass confusion and chaos in reading the rest of the code and promoting horrid bad practices. It's a minuscule instance versus hundreds of others that should always end in a semicolon. There is a lot worse bad practices out there that actually cause real confusion, real performance issues, real time sinks that deserve a lot more attention than splitting hairs about a little semicolon at the front of a CTE statement...
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply