February 8, 2023 at 12:58 pm
Looks like I woke everyone up this morning. Heeeheee.
@=)
February 8, 2023 at 12:59 pm
For someone that does provide as much content as he does/has, I can completely understand how that can wear away your sanity. ๐
Exactly. I totally understand why he does it and, as I said, I wouldn't damage a friendship over such trivial crap. However, I certainly do poke the bear on occasion. Meanest Canadian I know. Ha!
"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 8, 2023 at 3:18 pm
Brandie Tarvin wrote:Forget leading commas. Let's debate leading semi-colons. @=)
.Please no >_< .Leading commas I accept as a preference, but leading semicolons are another matter entirely, lol .Those are like starting every sentence with a full stop, and not ending your last sentence with one
/shrug I blame the interpreter for this.ย SQL Server is perfectly happy with some statements not being terminated by a semi colon, including the very statements that require the previous statement to be terminated by a semi colon.ย This effectively makes semi colons a statement beginner in some cases and not a true statement terminator.
February 8, 2023 at 3:20 pm
I use
;WITH
just to help prevent possible errors.ย Sure, I could be technically correct and blame someone else when there was a problem with the code, but I prefer to avoid the problem altogether.ย Someone making a quick fix in a proc sometimes just writes a line of code without fully examining all the other code around it -- because they don't need to do that to add their particular code fix.ย Should they still use a ;?ย Sure, but it's not always required so most people don't routinely code them.ย I don't want a major client to have a problem in production because of something that trivial.ย The time to parse an extra ; is so small it can't even be measured.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 8, 2023 at 3:33 pm
I prefer:
;
-- ^
-- |
-- |
-- semi colon above because most of you don't use them
WITH mycte ....
February 8, 2023 at 3:48 pm
I will admit, and though I know it shouldn't, it does amuse me when I see code that has something like this:
SELECT *
FROM dbo.MyTable MT ;WITH (NOLOCK)
JOIN dbo.YourTable YT ;WITH (NOLOCK) ON MT.ID = YT.MTID
Or even "better":
SELECT *
FROM (;WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY SomeCol ORDER BY ID) AS RN
FROM dbo.MyTable)
SELECT *
FROM CTE
WHERE RN = 1) DT
WHERE SomeCol > 5
It's that some people seem to think it's a ;WITH
clause, rather than just WITH
with a statement "beginningator" that really sells me against the syntax.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 8, 2023 at 3:51 pm
What I find extremely frustrating is when someone writes SQL code that joins multiple tables without prefixing the column names with their originating table or alias using dot notation. This adds a significant amount of time to the task of determining which columns belong to which tables.
February 8, 2023 at 4:13 pm
Jeff Moden wrote:David Burrows wrote:Jeff Moden wrote:David Burrows wrote:Thatโs why I like you Jeff, youโre such a smart guy ย ๐๐ฅณ
Heh... if I were really smart, I'd just up and retire.ย That would probably kill me though.
Just like me ๐, glad I didnโt. Youโll out live us all ๐ youโre an institution you know ๐
I think most folks would say your confused, David.ย They rather think I should be IN an institution. ๐ ๐ ๐
Second!!
All in favor?
And you know I'm kidding.
Kidding??? Why??? We were going to invite you to join us! ๐ ๐ ๐
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2023 at 4:18 pm
Grant Fritchey wrote:Jeff Moden wrote:David Burrows wrote:Jeff Moden wrote:David Burrows wrote:Thatโs why I like you Jeff, youโre such a smart guy ย ๐๐ฅณ
Heh... if I were really smart, I'd just up and retire.ย That would probably kill me though.
Just like me ๐, glad I didnโt. Youโll out live us all ๐ youโre an institution you know ๐
I think most folks would say your confused, David.ย They rather think I should be IN an institution. ๐ ๐ ๐
Second!!
All in favor?
And you know I'm kidding.
Kidding??? Why??? We were going to invite you to join us! ๐ ๐ ๐
by the looks of it WE are ALL in one already (SSC)
February 8, 2023 at 4:20 pm
Brandie Tarvin wrote:Forget leading commas. Let's debate leading semi-colons. @=)
This could start a flame war! I've seen instances of otherwise-seasoned coders on here using the dreaded
;WITH CTE AS (pattern, as if it's the right thing to do.
Heh... leading or trailing semi-colons... I solved that problem a really long time ago (it's why they pay me the big bucks ๐ )...
I don't end code with a semi-colon nor do I start it with one... Instead, and with some exceptions (too long to explain here but there's actually a method to my madness), I put semi-colons on their own line at the beginning of a line instead of using a blank line between queries.
That does 2 things... it's real easy to see that I remembered to add them and it satisfies both the leading and training semi-colon likes, dislikes, and eventual flame wars. ๐
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2023 at 4:26 pm
Not dropping temp tables at the end of stored procedures is one of the pre-requisites to take advantage of the tempdb improvements made to limit metadata contention in tempdb, https://learn.microsoft.com/en-us/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my#metadata-contention.ย I've seen this contention at a couple of clients in the last year and have had to recommend code changes so that they could get those benefits.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 8, 2023 at 4:30 pm
Not dropping temp tables at the end of stored procedures is one of the pre-requisites to take advantage of the tempdb improvements made to limit metadata contention in tempdb, https://learn.microsoft.com/en-us/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my#metadata-contention.ย I've seen this contention at a couple of clients in the last year and have had to recommend code changes so that they could get those benefits.
Oops, this wasย reply to Lynn's comment about dropping/not dropping temp tables at the end of stored procedures and it has been so long since I've posted I forgot that you have to do Quote not Reply to include the post you are replying in your post.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 8, 2023 at 4:36 pm
What I find extremely frustrating is when someone writes SQL code that joins multiple tables without prefixing the column names with their originating table or alias using dot notation. This adds a significant amount of time to the task of determining which columns belong to which tables.
Gail's No, this is not a bug in T-SQL is such a great example of why it's such a bad habit. I've seen multiple instances of people saying the data engine is bugged, and it's the exact problem she outlines.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 8, 2023 at 4:37 pm
What I find extremely frustrating is when someone writes SQL code that joins multiple tables without prefixing the column names with their originating table or alias using dot notation. This adds a significant amount of time to the task of determining which columns belong to which tables.
Gail's No, this is not a bug in T-SQL is such a great example of why it's such a bad habit. I've seen multiple instances of people saying the data engine is bugged, and it's the exact problem she outlines.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 8, 2023 at 5:19 pm
Jeff Moden wrote:Grant Fritchey wrote:Jeff Moden wrote:David Burrows wrote:Jeff Moden wrote:David Burrows wrote:Thatโs why I like you Jeff, youโre such a smart guy ย ๐๐ฅณ
Heh... if I were really smart, I'd just up and retire.ย That would probably kill me though.
Just like me ๐, glad I didnโt. Youโll out live us all ๐ youโre an institution you know ๐
I think most folks would say your confused, David.ย They rather think I should be IN an institution. ๐ ๐ ๐
Second!!
All in favor?
And you know I'm kidding.
Kidding??? Why??? We were going to invite you to join us! ๐ ๐ ๐
by the looks of it WE are ALL in one already (SSC)
I think we could get a group rate at a sanitarium.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 66,151 through 66,165 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply