If you could give just one piece of advice about writing good queries, what would it be?
I asked Twitter yesterday because I wanted to make sure I didn’t miss anything in a new training course I’m working on, and the replies were fantastic. Here were some of my favorites:
As you’re writing a query, give a play by play in the comments explaining what you’re doing and why. A. You will catch more of your mistakes.
— BD Softley (@BigfootSql) May 3, 2021
Understand execution plans, look at actual ones and analyse.
— Stefán Jökull Sigurðarson – CCP Ghostrider (@stebets) May 3, 2021
anything that makes your life easier makes the optimizer's job harder
— Erik Darling Data (@erikdarlingdata) May 3, 2021
Don’t let perfect be the enemy of good. Write queries up to the point that they do what you want in an acceptable amount of time and using an acceptable amount of resources.
— Mike Scalise (@themikescalise) May 3, 2021
Write two. Pick the better one, if you can work out why it's better.
— Data Quizard (@TimBSpeaks) May 3, 2021
— George Stocker (@gortok) May 3, 2021
It formats the query or it gets the hose again.
— Cody Konior (@codykonior) May 3, 2021
Document your code.
— Dustin Mueller Staying the F home (@sqlcheesecake) May 3, 2021
Write it, give it to the person next to you. If they can understand its intent, then everything else is easy.
(Because now, there is an infinite amount of help out there should you run into problems…But if no-one can understand it…its a tough road ahead)
— Connor McDonald ??? ? lots of ?? (@connor_mc_d) May 3, 2021
this is my second one, couldn’t resist giving one more:
comment the “special tricks” employed in a query
eg why is there an ‘optimize for’, was a redundant filter added intentionally, etc https://t.co/DCcAZDZvmy
— L_ N___ (@sqL_handLe) May 3, 2021
SET STATISTICS IO, TIME ON;
— Dave Mason (@BeginTry) May 3, 2021
Understand that your query is only half of what creates the program to fetch your results. The rest is what’s there on the server – statistics, indexes, the capabilities of operators, etc.
— Rob Farley (@rob_farley) May 3, 2021
Share your query online with the title "the best way to write queries”.
Next day you will have a good selection to copy paste.
— ?ister?agoo #Blazor (@mistermag00) May 3, 2021
What about y’all? Leave your most important tip in the comments, and read the Twitter thread for tons of gold nuggets.