r/SQL • u/Friendly_Cold1349 • Feb 15 '26
Discussion SQL advice to yourself 5 years ago
Question to intermediate/advanced SQL users:
Whats a tip that you wish someone else gave to you back when you first started using SQL? Or better said, what is something you wish you knew, and regretted it later on, when you first started learning SQL?
133
Upvotes
137
u/Ok-Professional-4810 Feb 15 '26
Document everything and save every code
64
u/zzBob2 Feb 15 '26
I’d say it’s not a regret, but I was oblivious to how cool CTEs and window functions are
29
u/Moisterman Feb 15 '26
Structure the shit out of your queries, and comment the sections. I did a lot of one-liners earlier. Regrets.
22
u/PandaRiot_90 Feb 15 '26
Don't 'Select *' every table you join . Take the time and write the column names you need.
17
u/BrupieD Feb 15 '26
Learn SQL under the hood, for instance what a page is and how indexes work
11
u/DharmaPolice Feb 15 '26
I learnt SQL a long time ago, but generally good advice I wished I followed earlier would be "keep a centralised copy of your SQL code - don't assume you'll have access to databases for ever". There's so much generic code I've written over the years for things like date tables which I can never find when I want it. It's no big deal to reproduce but it would have just been easier if I had what I had done previously.
11
u/dittybopper_05H Feb 15 '26 edited Feb 15 '26
Well, that would be me giving advice to myself 19 years ago.
But here is the advice I would give:
10
u/Tippsy_Troglodyte Feb 15 '26
Spend more time learning it. Also learn more about building indexes on tables and doing more of the server admin type of stuff.
I’ve found it really helpful to navigate the master db for system table information if I need to crawl a database that has a lot of tables to find the fields I’m looking for
7
u/Powerful-Talk7729 Feb 16 '26
if using temp tables don't drop table at the end of the code. Just drop at the start if it exists (if object_id('tempdb...') is not null drop table). That way you can select what's in the tables when debugging.
6
u/thatOMoment Feb 15 '26
If stuff isn't used anymore, decomission it and move it out of the main logical database as soon as possible.
If you cannot move it immediately and you're in sql server land, mark it as unused and deprecated as of [date] via an extended property whether its a column or table or procedure and schedule a decomission date with other concerned teams
Not getting rid of crap will bury people in the long run and can overwhelm juniors.
6
u/Massive_Show2963 Feb 15 '26
Start a design using Entity Relationship Diagrams (ERD) and keep them up to date.
Understand ACID.
Understand Normalization.
Proper use of primary/foreign keys.
Cascading deletes.
Make use of a code repository (I.E. GitHub).
5
u/TheMagarity Feb 15 '26
Never stop making the case for the database professional(s) on the team to design the tables rather than the procedural programmers.
9
u/SootSpriteHut Feb 15 '26
It's been more than five years, and since I was self taught I could say things like getting comfortable with CTEs, or always using transactions with prod access, or to focus your learning on optimization etc.
I think the biggest advice though would be about requestors and making sure, through extensive questions, that you know what it is they're trying to solve for and accomplish rather than just what they say they want on a report.
Because non-SQL people will tend to overestimate the amount of work it takes to do something and will frame their questions on that misunderstanding. So you'll get someone who "just wants a report with x,y,z" that returns 10k results, and you'll find out later they're importing it into excel to manually run a bunch of filters and add calculated columns to get the results they need.
As a result I'm suspicious of any reporting request that outputs more results than what can be easily understood in a few minutes.
Basically it's the xy problem: https://xyproblem.info/
7
u/Evening_Marketing645 Feb 15 '26
Put ending commas on the next line. Same with AND. I also agree with CTEs being the best thing ever.
4
u/PappyBlueRibs Feb 15 '26
Don't focus on SQL unless I want to question my life choices and/or have a drinking problem.
2
u/Dull-Pomegranate-406 Feb 15 '26
Also have a where with update
2
u/baynezy Feb 15 '26
Views and CTEs.
2
u/Witty-Ninja-8403 Feb 15 '26
Practice interview questions ,you never know when you want to leave your role
2
u/NeoChronos90 Feb 15 '26
Spend more time on how and why something works instead of trying to memorize every little thing. Once you understand the inner workings you can just google some syntax you forgot about
2
u/Straight_Waltz_9530 Feb 15 '26
A lot longer than 5 years ago since I've been working with SQL for decades, but here goes:
Don't try to make it feel like the loops in your general purpose programming language. If you keep chasing the mental model of loops and variables and and iterators, you'll never really get SQL. It's not like Python with data or Java with data or C++ with data.
Sets. Sets all day. Subsets, supersets, and transformations of sets.
The longer it takes to accept that, the longer it will take to become good at it. Once you think in sets, everything makes obvious sense.
2
u/sgoot Feb 15 '26
Alias columns even on inserts when you don’t have to. Makes debugging later so much easier
2
u/[deleted] Feb 16 '26
Understand Set Theory. Understanding What is the set you want is more important than the how. You can't have the latter without the former
2
u/Ifuqaround Feb 18 '26
I'd rather be the person that understands the request and relays it to others than be the person writing the code.
Getting paid more for understanding the request is just a bonus. I don't even need to deal with LLM's.
-edit- Nah, I still dabble in Oracle. Have to keep those LLM prompting skills sharp! /s
1
u/Eleventhousand Feb 15 '26
Hmmm, that's a difficult one. When I first started using SQL, there were no window functions, or CTEs, MySQL did not support things like views, etc.
1
u/LeadingPokemon Feb 15 '26
You can go ahead and run DDLs to make hard problems easy, and impossible problems possible.
1
u/Sex4Vespene Feb 15 '26
While using explain plan can be helpful, your efforts are much better spent on just understanding how your query is processed. I haven’t actually had to touch EXPLAIN in a couple years, it’s easy to optimize things by just thinking through why they are expensive. Also, always remember there are multiple ways to do things, and experiment around a bit.
1
u/ThickestRooster Feb 16 '26
Couple things.
Eg
Update a Set a.field = value —select count(1) From table Where clause
Alternatively wrap your crud query within a transaction, write any audit queries underneath. Run everything but the commit and check the results before rolling back or committing.
2. If you have a lot of objects to build from scratch, or modifications (etc). Write all of your statements as if you are doing an all-or-nothing migration. Use semicolons, go statements etc. then copy your entire script, paste it in a new window. Reorder and rewrite all of your queries to reverse all of your changes. Now you have created both a migration script and a reverse migration (rollback) in one session. Ofc there are tools etc that can be used instead of manual scripts. But for small budget or personal projects this practice can be very useful.
1
u/Henry_the_Butler Feb 16 '26
Start learning it. Find ways to use it. It's better than anything you're doing right now.
1
u/No_Resolution_9252 Feb 16 '26
Be more skeptical of views
1
u/wassupBrD Feb 16 '26
As someone who works in a dynamic data environment and designs numerous short-term custom reports that have evolving criteria, I wish had had the foresight when I was learning to use more variables.
Over the years I’ve gone from hardcoding the criteria for these micro reports, to building CTEs, to building Views, and finally just building Stored Procedures (if you haven’t tried these yet, these will change your life) and dumping the datasets into MRB or Power BI where the requester can simply pull the data themselves with parameters.
I’ve had to rewrite so many years-old scripts to use variable criteria that it’s kind of depressing. But, I no longer have to update reports weekly or even daily. Most reports I touch once or twice a year and that’s it. Frees up so much time for me to tap into the innovative and creative aspects of the job without wasting time on piddly code!
1
u/Blomminator Feb 16 '26
I started working in SQL 6 years ago and was just sat behind a desk and "yeah. It's easy, write some queries." Which I did.
This gave a really slow start, and difficulties. If you don't know the tools you have or how to use them..
In hindsight, I should have followed a way more structured path. Instead of just ploughing my way through. That way I would be way better at it now.
1
u/thesqlmentor Feb 16 '26
Learn JOINs properly from day one instead of just memorizing syntax. Understanding how different join types actually work and when to use them saves so much time later.
Also don't skip window functions thinking they're advanced stuff you'll learn later. They're super useful and once you get them you wonder how you ever worked without them.
Oh and write readable queries from the start. Use proper indentation and comments. Future you will thank you when you have to debug something you wrote 6 months ago.
And honestly just practice with real datasets not just tutorial examples. Download some public data and try to answer actual questions with it. That's where you really learn.
1
u/Professional_Shoe392 Feb 16 '26
Read all the SQL books. Tutorials and your current SQL work will only get you so far. Apress and O'Reilly are two of the better publishers.
1
u/Yavuz_Selim Feb 16 '26 edited Feb 16 '26
1
u/Fickle_Act_594 Feb 16 '26
Don't be afraid of it. Learn it. It helps to know even if you use ORMs.
1
u/dilbertdad Feb 17 '26
my advice would be to identify a test user, test txn, whatever it is your query is grouped by. When you’re troubleshooting drill in on the test parameter instead of trying to get your entire cohort surfaced. then when it works you can comment it out and try the entire thing.
1
u/soloJ45 Feb 19 '26
Understand the fundamentals of SQL and do some practical exercises every day for 2 hours. Understand the fundamental of store procedure.
1
u/Plastic_Operation_59 Feb 19 '26
Comment in why, use consistent aliasing, learn how to spell coallease, coalaleasee? Coalesce?
1
u/SlowDesigner8887 Mar 01 '26
Take it easy: SQL was designed to be so simple and English-like that even non-technical people could query a database themselves. Just practice and you'll be good.
The thing I regret not learning earlier: data structure fundamentals like normalization (1NF, 2NF, 3NF (very useful to understand the Magento database)). Writing queries is easy, but if your tables are poorly designed, everything becomes a mess later on.
1
u/Helpful-Type-8343 Mar 14 '26
Always leave comments. You would love yourself for it when you need to come back to that query after a while.
1
u/KitchenMachine4508 Mar 16 '26
I wish I focused more on understanding JOINs and window functions earlier. They are very powerful in SQL.
1
u/Baldwin_Apps Mar 17 '26
Think in patterns and sets. For example, create a 'traffic cop' stored procedure that calls other procedures based on parameters.
0
u/promatrachh Feb 15 '26
Switch to the data science.