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/
February 8, 2023 at 6:30 pm
frederico_fonseca wrote: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.
Scratch that... They should pay us for the entertainment value! 😀 😀 D:
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2023 at 6:35 pm
Michael L John wrote:frederico_fonseca wrote: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.
Scratch that... They should pay us for the entertainment value! 😀 😀 D:
Are you saying they can put a fence around us, and charge admission? Like DBA Disneyland?
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/
February 8, 2023 at 6:40 pm
Jeff Moden wrote:Michael L John wrote:frederico_fonseca wrote: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.
Scratch that... They should pay us for the entertainment value! 😀 😀 D:
Are you saying they can put a fence around us, and charge admission? Like DBA Disneyland?
Yes... and you must be at least this tall to enter. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2023 at 9:41 pm
Michael L John wrote:Jeff Moden wrote:Michael L John wrote:frederico_fonseca wrote: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.
Scratch that... They should pay us for the entertainment value! 😀 😀 D:
Are you saying they can put a fence around us, and charge admission? Like DBA Disneyland?
Yes... and you must be at least this tall to enter. 😀
Unless we're already inside, and I think some of us are. Hey, at least we know we're in a box, right? 😀
February 8, 2023 at 10:27 pm
Michael L John wrote:Jeff Moden wrote:Michael L John wrote:frederico_fonseca wrote: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.
Scratch that... They should pay us for the entertainment value! 😀 😀 D:
Are you saying they can put a fence around us, and charge admission? Like DBA Disneyland?
Yes... and you must be at least this tall to enter. 😀
Well, I guess I'm ruled out.
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 10:54 pm
Jack Corbett wrote: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.
Thanks, but the article I read was a blog post that I still can't find.
February 8, 2023 at 11:05 pm
Jack Corbett wrote:Jack Corbett wrote: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.
Thanks, but the article I read was a blog post that I still can't find.
Sure, I was just trying to supply a reason why NOT dropping temp tables in stored procedures can be a performance boost. I've never practiced explicitly dropping temp tables in stored procedures because they are dropped at the end of execution anyway and are execution specific, so I never found a reason to drop.
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 9, 2023 at 12:22 am
Lynn Pettis wrote:Jack Corbett wrote:Jack Corbett wrote: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.
Thanks, but the article I read was a blog post that I still can't find.
Sure, I was just trying to supply a reason why NOT dropping temp tables in stored procedures can be a performance boost. I've never practiced explicitly dropping temp tables in stored procedures because they are dropped at the end of execution anyway and are execution specific, so I never found a reason to drop.
Old programming habits die hard. Just something that carried over when I started working with MS SQL Server way back in the 6.5 days.
Viewing 15 posts - 66,181 through 66,195 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply