August 13, 2021 at 5:09 am
Just curious if there are people who really write SQL queries sequentially. For example, I often jump directly to 'FROM tables' after writing 'SELECT', and then fill in the columns. I also know people who like to start with JOIN then WHERE then GROUP BY and finish the SELECT at the end.
What is your way of writing SQL queries?
August 13, 2021 at 7:31 am
Usually I start with select top (1) * from table1, adding joins, where's ... later on. Refining * is usually done later on
August 13, 2021 at 8:32 am
A lot of people here use SQL Prompt and to get the most help from it during query writing, it's best to have the FROM clause written, including all JOINs and with whatever aliasing conventions you use.
I often leave a TOP (10) in the SELECT clause for a while too, for speed.
I doubt that anyone of sound mind writes anything more than a one-table SELECT query sequentially.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 18, 2021 at 2:27 am
Take a step back and think Test Driven Development.
Use a framework for EVERY SCRIPT you write. It will help you.
What does that mean? write snippets for things like documentation headers, so you don't forget what you're TRYING to do. Spell out your success criteria, so you know for sure whether you're hitting your target. Break up your data that you're using to search for an answer into logical sets, so you can tell if you're missing something in each step (and use debug variables to spit out messages that tell you whether you are succeeding or not, so you can tell where it breaks).
Then, don't think about writing code in clauses, write simple queries in order to populate each set of data (i.e. to satisfy the Test Driven Development you defined to begin with, your Success Criteria). Use the MINIMUM of columns you need, not *, so if you know your table has a UID and five other columns you usually use, use just the UID until you know you need another one, then go back and add it. If you don't know the columns well, then just use the ones you're joining on to start.
Lastly, combine the data to find what you need. Once you have your answer, format it for the user (this may mean backing up and adding something to your first set of data because they need it, but at least it didn't slow you down along the way).
It's only complicated the first couple of times, after that it's a breeze, and snippets save you 90% of the time and make your code 100% stronger. You'll be sure you have all the data you need, without missing conditions along the way, user will be happier. You will be happier, because when you (or the next poor sap) come back to something six months later and can't remember what the hell you were doing, you don't have to remember to "only highlight that first part", you can just read through the code (you commented it, right?) and pick it right back up, change what you needed, and done.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply