My invite is about coding standards, or what I now call Linting Rules, for T-SQL. What are the T-SQL coding rules that are appropriate for where you work and what you do? If there are exceptions to those rules, state them too! If this is enough, read the blog party rules below and get started!!
Your post must be published on Tuesday June 14, 2022.
Your post must contain the T-SQL Tuesday logo (see above) and the image must link back to this blog post.
Trackbacks should work, but if not please put a link to your post in the comments section so everyone can see your contribution! (My comments are moderated so please don’t worry if yours doesn’t appear right away, I will make sure it does!)
If you are on twitter include the hash tag #tsql2sday – it helps with RT-s and visibility!!
More on why I picked this topic as below –
When I started out as a DBA two decades ago, I had a list of rules that I would carry with me into every job I went..these are things I look for in T-SQL code and try to enforce as standard. Some examples were casing rules, minimized usage of SELECT STAR, equating the right data types in columns, avoiding NOLOCK hint and so on. Standards ensure quality and consistency in code.
Standards differ for each firm, depending on what is appropriate for an environment..it is even possible to have varying standards in the same company, depending on the environment and what is appropriate for a database. This is an excellent article on what are the different components that comprise coding standards, and why we need them. I am also a big proponent of automated code checking for standards – there are lots of tools available for doing this – SQL Prompt, which is a personal favorite of mine, and many others as listed here.
Several tools currently do linting on many relational platforms, not just SQL Server. Almost all of them though, have rules that the author(s) think are best for the worlds they work in, and do not include other conditions which they have not encountered yet. A common example I like to use is unnamed primary keys on temporary tables. There is nothing inherently wrong with having an inline primary key constraint/index on a temporary table – but if you use Query Store, plan forcing on a plan that uses this temp table will not work simply because the constraint gets named differently each time. When I started to look for a linting tool for where I work – I ran into so many rules that were non-existent or not applicable to my environment with outside tools that I decided to write my own using ScriptDOM – a Microsoft-provided library that was created specifically for this purpose.
It would help greatly if we had a collection of rules that people use to pick from and enforce as appropriate for their environments. It will also help me to code some of these into ScriptDOM and put it out on GitHub, if the rule is one that ScriptDOM can find easily. So, re-stating the call for this month – What are the T-SQL coding rules that are appropriate for where you work and what you do? If there are exceptions to those rules, state them too!
T-SQL Tuesday this month is going back to basics and its all about code. I’d like to know “What are your go to handy short scripts”?
What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.
e.g. I manage a lot of SQL agent jobs. Quite often, I need to find out which job has a certain t-sql string in the command so I’ll run:
SELECT * from msdb..sysjobs sj
JOIN msdb..sysjobsteps sjs
on sj.job_id = sjs.job_id
where sjs.command like 'backup log%'
Of course, there are many other ways to find this out including DBATools commands but sometime I just revert to memory for convenience.
Another one I like is to get the estimated completion rate of a backup or restore. Now there are better scripts than this but sometimes, nothing beats getting a quick estimation back from a couple of lines of memorised t-sql.
SELECT percent_complete pc,*
order by pc desc
My invitation to you for this month’s #tsql2sday is…
I would like you to share with the community what your go to script snippets are and why you find them useful. By sharing these, you will undoubtedly be helping someone who hasn’t thought of doing it that way, and hopefully you’ll pick up some handy hints as well.
Any language is fine, not just t-sql
Please share as many as you wish
Perhaps you never do this and always work off saved scripts or convert your snippets to stored procedures? Tell us why this works for you.
A few years back I started running regular SQL workshops in my
workplace. Teaching beginners the basics of querying databases with SQL,
as well as more advanced topics for the more advanced.
During one session we were discussing the issue of knowledge acquired
being quickly lost when people didn’t get the chance to regularly
practice what they’d learnt. One of the attendees suggested that I
should be assigning them homework.
I could see from the faces of everyone else present that the word
“homework” struck an unpleasant chord. Perhaps reminding them of school
days struggling to get boring bookwork done when they’d rather be at
relaxation or play.
Okay, so homework maybe wasn’t going to go down well, but I figured
everyone likes a good puzzle. So every Friday I started creating and
sharing a puzzle to be solved using SQL. This went on for the best part
of a year, then other things got in the way and gradually I stopped.
This is my invitation to you this T-SQL Tuesday. Write a blog post
combining puzzles and T-SQL. There’s quite a few ways you could approach
this, so hopefully no-one needs be left out for lack of ideas:
Present a puzzle to be solved in SQL and challenge your readers to solve it.
Or give us a puzzle or quiz about SQL or databases.
Show the SQL solution to a classic puzzle or game.
Provide a method for solving a classic sort of querying puzzle people face.
Show how newer features in SQL can be used to solve old puzzles in new ways.
Tell us about a time you solved a problem or overcame a technical challenge that was a real puzzle.
Or just make your own interpretation of “puzzle” and go for it!
There’s some great stuff out there already. Itzik Ben-Gan’s done a
bunch of them. There’s Kenneth Fisher’s crosswords. The SQL Server
Central questions of the day. Pinal Dave’s SQL Puzzles. And there’s a
few on my blog too if you take a look back:
Let’s puzzle together, trying to solve the challenges each other sets, and make it a real puzzle party!
Have you heard the phrase “Automate All the Things”? That seemed to
be the top buzz phrase of 2018 and means different things to different
Kicking off the T-SQL Tuesday season for 2019, I would like to ask,
what does “Automate All the Things” mean to you? Everyone’s environment
is different, everyone’s day-to-day looks different, everyone is a fan
of different technologies and everyone’s environment is of different
size. While I might want to automate checking of my backup success
across my 500 servers, you might want to automate how new servers are
provisioned. This can be a very broad topic, that could include a broad
range of technologies. You might choose one type of technology to
accomplish a task, where I might choose another.
So technically there are two tasks for this month:
What do you want to automate or what automation are you proud of completing?
I’ve been working with SQL Server and T-SQL a long time, and across many jobs, I think I’ve ended up using triggers in 0.01% of my tables or less. They can be a useful and helpful construct, but they can also be problematic and difficult, especially in the age of changing business models and rules.
Since I’ve found triggers to be both helpful and hurtful, I decided to ask you to write about an experience you’ve had with triggers. Either good or bad, but let me know this month what stands out in your mind.
Before modern online programming communities, finding good code samples or sharing your own code was challenging. Forums and email lists (if searchable) were good, but beyond that you had to rely on books, coworkers, and maybe a local meetup of like-minded individuals to help you work through your programming problems.
Today, accessing and using code from the internet is second nature – I almost always first look online to see if a good solution already exists. At the very least, searching blogs, GitHub, and StackOverflow for existing code is a great way to generate ideas.
For this month’s T-SQL Tuesday, I want you to write about code you’ve written that you would hate to live without.
Maybe you built a maintenance script to free up disk space, wrote a query to gather system stats for monitoring, or coded some PowerShell to clean up string data. Your work doesn’t need to be completely original either – maybe you’ve improved the code in some open source project to better solve the problem for your particular situation.
There’s probably someone out there in the world who is experiencing the same problem that you have already solved; let’s make their life a little easier by sharing.
And don’t worry if your code isn’t perfect – just explain how your solution works and if you are aware of any caveats. If it’s not an exact solution for someone else’s problem, at the very least it may help them generate some ideas.
As many of you know already, I decided to declare December 2012 as the month of the JOIN by writing the A Join A Day blog series. So, it should be quite obvious what this month T-SQL Tuesday is about. You guessed correctly — I would like you to join me in talking about joins. Now you might think “If Sebastian is writing 31 posts about JOINs already there is nothing left to write about.” But I can assure you that there is still plenty out there. In my series I am going to cover just the basics. For example, what is a cross join or an anti-semi-join? What is the difference between a hash and a merge join? There are many things I won’t be able to cover, for example how to write efficient join queries.
Your mission – should you accept – is to write about topics like the good and the bad patterns of joining you have seen out there or really anything else that comes to your mind when thinking about joins:
Have you had to deal with a slow monster join that you were able to conquer? Let us know how you did it.
Have you noticed a join pattern in use that is really not good for readability but you come across it time and time again? Tell us how to do it better.
Have you discovered a really cool way of using the APPLY command instead of a JOIN to force the execution engine to utilize the existing CPU resources more effectively? We would like to hear about it.
And if you have a topic that you always wanted to write about but that is only remotely related to joins, feel free to use it anyway and make sure to tell us why you think it is related to joins.
Hope to see you (or at least your post) next week at the party.
For a while, I was in an amateur pool league. No, not the one involving water and swimming, but where you try to sink balls into pockets. It was a lot of fun and is a challenge both for your motor skills as well as your strategy. I still shoot from time to time, as well as hang out with my old pool buddies.
One thing guys would get into is trick shots. Two and three rail bank shots, masse shots, or jumping the cue ball to hit the target. Most of these shots weren’t tournament legal, but they were fun to try and nice to impress the ladies. More than that, they were a tool to teach you the physics of your pool game. You could see how throw and English could affect your shot, or how balls would behave after impact.
Just like so many other things I do in my life, the trick shot lessons translate over to SQL Server. How many times have we built something neat or puzzled out a particular bit of logic that, while it may not have been particularly useful, taught us about how SQL Server behaves. This month’s T-SQL Tuesday is all about this and the assignment is two-fold:
Show us a cool trick or process you developed, maybe a DMV you used or some reporting logic you created. It doesn’t have to be useful, just something that you thought was pretty neat.
Tell us what you learned from this trick. Is it something about an oddity in SSRS? Maybe with the query processor? Whatever you did, tell us how it gave you insight in to how SQL Server works.