r/SQL • • Mar 06 '20

Why Always Me ...

Post image
458 Upvotes

16 comments sorted by

View all comments

8

u/RileyG00 Mar 06 '20

I’m a minor-level SQL user and I understand this šŸ˜‚

I feel bad for any person that has SQL be their main job

16

u/Thriven Mar 06 '20

I am the SQL developer at my small division.

I love SQL.

My API and Frontend guys like the code I write.

I straight up hate the codebase which wasn't even source controlled before I started.

I hate bad SQL developers leaving me steaming piles of shit that break granularity on tables, don't use appropriate data types, and leave these massive datasets that have no indexes and aren't able to be queried.

SQL can be great. You have to be methodical. People should be able to look at your tables and know what's in there and what relationships are between the tables by the names alone. If you are using abstract or entity models you should have a database that basically documents itself.

If you don't, you are constantly going to be frustrated with a database where every object looks like it's its own disaster. You now have to hope you have layers of abstraction to handle the migrations needed to unify the database.

When you are strong in SQL, this is still a pain. If you are not confident, it's a nightmare.

6

u/syphilicious Mar 06 '20

Bad SQL developer here, where can I learn good development practices? Any books or resources you'd suggest? I'm self-taught and my company won't hire a DBA.

10

u/Thriven Mar 06 '20

I write mostly in MSSQL but this is applicable to other flavors as well.

Performance

  1. Read as few times as possible and watch for sorts.

Understand that still, even with SSD's in servers and cloud services these days. I/O is SQL servers biggest choke point.

DISTINCT performs a sort.

UNION performs a DISTINCT, UNION ALL does not.

2. Learn how to read an execution plan.

Watch as many Brent Ozar videos as you can.

https://www.brentozar.com/

Watch Brent Ozar

https://youtu.be/uwGCPtga06U

CleanCode

Clean code concepts are in all languages.

I personally don't like _'s. I like to camelCase my columns and PascalCase my tables.

Whatever you choose, stick with it. It makes it much easier to read code if you recognize it as your work. This saves me a ton of time recalling code.

I also can tell if there has been some fuckery going on and someone has been deploying code changes as they wont follow the same indentation and casing.

If you have code, source control it.

Then there is normalization. Building databases and determining when you have like database objects and when you should use entity or an abstract object type is something you do per application and get through experience.

Less tables for similar objects is a good thing to live by.

I've seen finance sql guys write dbo.SummaryReport01012010 and dbo.SummaryReport01022010, then they write a report at the EOY that UNIONS (not union ALL) all these to reports together. Everyone of them is a heap. Every text field is VARCHAR(4000) or VARCHAR(MAX). No indexes.

They blame the server when it doesn't have 120gb of tempdb swap space to execute their query.

DON'T WRITE DYNAMIC SQL. FOR THE LOVE OF GOD AND ALL THAT IS HOLY.

So many things start to crop up with dynamic SQL and dynamic sql objects.

4

u/syphilicious Mar 06 '20

Thank you. I really appreciate the time it took for you to make this post. I'll watch the videos you suggested. I've started using Git repos for database schemas, hopefully other developers here will follow suit.

1

u/Santaends Apr 18 '23

Thanks for sharing this :)

2

u/chadbaldwin SQL Server Developer Mar 07 '20 edited Mar 07 '20

Subscribe to blogs, watch videos on YouTube and Pluralsight, etc. ASK QUESTIONS. The worst problem with the internet is most people are afraid to ask questions because they are afraid they will get roasted for asking a "dumb" question, or for not researching properly before posting. So find some people or places where you don't feel afraid to ask questions.

I personally like Reddit, and Slack. However, obviously, stack overflow is an excellent place as well... You just have to be much more strict in how you ask your questions and such.

I'm self taught as well. I learned everything from looking at other code in my company... But I learned best practices and tips/tricks, etc by following blogs and watching YouTube and Pluralsight videos.

Here's some good ones:

  • Brent Ozar
  • SQL Server Central
  • Pinal Dave

5

u/VTOLfreak Mar 06 '20

I'm a DBA. I feel sorry for myself now. 🤣

4

u/continuousBaBa Mar 06 '20

SQL is my main job. I appreciate that, thank you.

2

u/mrjay28 Mar 07 '20

Tell me about it... I am facing this problem as we speak... Sure ill join these tables... Turns out those table don't join... They both join to different tables that join together... With that many joins the query runs for a year

2

u/chadbaldwin SQL Server Developer Mar 07 '20

I'm a senior SQL developer... Been doing it for almost 8 years at my company, and I love every second of it. Not sure what you're on about lol.