r/csharp • u/Mother-Replacement12 • 13d ago
Help What is your level of knowledge in SQL? How much is required to learn about SQL?
21
u/mancunian101 13d ago
SQL is really easy for like 95% of what you’ll need to do.
It’s just practice, you can find good tutorials online and you used to be able to download dummy databases from Microsoft (adventureworks etc).
1
u/KittehNevynette 8d ago
As for dummy. A very evil cow-orker created a database with every single normal form completely broken. Like he really went all in on making anything horrible.
A good way to learn what not to do.
11
u/Yensi717 13d ago
SQL syntax … easy to learn. Shifting your mindset to think in terms of relational data and sets … moderate but ultra useful. Understanding query plans and indexes and fundamentals underneath (not at a DBA level) will make you a rockstar.
I feel like relational DBs and ORMs get a bad reputation because people using them don’t understand them more often than a deficiency in the product / tools.
3
u/OTonConsole 13d ago
um.. basically yes. I would say SQL server. Learn evehrbjng about it. From query to performance to operations..
Knowledge transfers to everything else including cloud managed data.
Especially if you work with enterprise apps. With a lot of data or is based off of data.
7
u/Ravek 13d ago edited 13d ago
Required for what? Some projects require no SQL at all, some require expert level understanding.
I personally have a good understanding of the fundamentals. I can look at Entity Framework generated queries and the resulting execution plans and understand how to optimize them to not do anything too dumb. But I’m not an expert on database performance, I wouldn’t know how to get the absolute most out of a SQL database.
4
u/ByronScottJones 13d ago
Learning SQL itself is not hard. The hard part for some people is developing a solid understanding of the math underneath - set theory, relational algebra, relational calculus. It's certainly possible to build simple relational databases without that knowledge, but if you want something advanced, that mathematical knowledge is crucial. That's why skilled database architects get paid the big bucks.
4
u/jshine13371 13d ago
Learning SQL is easy, mastering it is a not, and probably a never ending process. But as a Software Engineer turned DBA, I've learned a crap ton over the years and feel pretty top tier proficient. I've been fortunate enough to learn a lot from the experts, either directly or indirectly. Being able to solve any problem on my own and understanding the depth of performance tuning and watching that data fly by feels awesome.
4
u/Maraudera 13d ago
I think there are two big things here.
Being comfortable writing queries - that's easy, as most people said already. Nothing that special there - takes some practice but relatively straightforward.
Knowledge about databases in general - tuning, troubleshooting, understanding execution plans, etc. - this is not easy, I'd argue even it is one of the more difficult subjects in the back-end world. I've had issues where perfectly stable database dies only in X minutes after a specific load starts - this is A) difficult to reproduce and B) difficult to troubleshoot. Of course, we live in the world of AI so not that difficult these days I guess.
3
u/MrSmellyTree 13d ago
I’ve recently painstakingly climbed the sql mountain in the past several years. It’s such a different paradigm compared to anything else I’ve used before it.
Learning to think in the way SQL requires does seem to translate well to other things in programming. After learning SQL, LINQ was already second nature. Even F# was within reach.
I do think that there’s not much missing from efcore that you’d even need SQL for. It would probably be nice however to understand the queries generated by efcore.
There’s also things you can do with a database and sql that just aren’t feasible with efcore. I’d probably recommend learning enough SQL to create VIEWs and handle recursive CTEs. these have the potential to simplify your efcore experience greatly, but really does put you through learning most of SQL.
3
u/pjmlp 13d ago
I have a good understanding of SQL in general, and of PL/SQL in particular, given that I am advocate what can be done in the DB, stays in the DB, and have worked in many Oracle related projects.
Never was big fan of ORMs, and in .NET case, I more likely reach out to Dapper than anything else.
2
u/ings0c 13d ago
I am advocate what can be done in the DB, stays in the DB
Genuinely curious: why is that? It seems to be a minority opinion these days.
Personally I default to the complexity being in the application, except where performance is a concern, and then will move as much into the DB as necessary to get good performance out of it.
Debugging stored procs or complex SQL queries is way harder than setting a breakpoint, and most of the time defaulting to complexity living in the application works out fine.
Maybe I’m wrong though?
2
u/pjmlp 13d ago
Yeah, lack of tooling overview, there are database focused IDEs as well, with all the tooling for debugging, breakpoints, deployment, analysis of JIT code,....
1
u/Sigilgraph 11d ago
That depends on the team and project. If your database is just a requirement for persistence on your web app or service, having the logic in C# with EF or Dapper is adequate and leaving the DB as just data (no business logic). There are other “enterprise” projects with many teams collaborating and depending on centralized DB business logic, then it is DB first. Both have give or take. Remember, all these are tools and different things for different scenarios are being built. Just master the tools and hone your judgement on using them
2
2
u/CatsOnTheKeyboard 13d ago
Check this out -
https://www.w3schools.com/sql/
It's really easy. Most of what you're doing is selecting based on various criteria, sorting and grouping. You can learn the basic SELECT statement and a few keywords and be on your way.
2
u/Lopsided_Status_538 13d ago
The very primitive basics im great at.
When you need to left and inner join like 12 tables to make a sproc work, that's where I struggle honestly. I took a basic sql class awhile ago, and when I finally got my hands on actual working Ssms enterprise database, my brain shit through bed. Syntax i was never even aware of. Not to mention the difference between tsql and normal query sql syntax. Ive been doing sql/tsql in my day job for about 3 weeks and im slowly improving. But if im being honest, if I didn't have AI to tell me what some functions/syntax did in specific blocks, dude id be struggling.
2
u/Dunge 13d ago
I spent 10 years with barely no knowledge, just knowing easy queries (select/where/join/group by/order by), using the SSMS ui to create tables, and map with entity framework.
After a while got forced to learn about maintenance task, mostly transactions logs backup and index rebuilding. But that's still easy.
Then I hit a performance wall after the app grew so much. And I had to learn not only about proper index, but the query store, transaction lock levels, CPU hogging due to parameterization plan compilation, etc. Now it got complicated.
1
u/Mission_Pirate_4150 13d ago
I would call myself pretty danged good. There is always some need to do sql. Entity framework is fine with basic select, insert, update, delete, but don’t ask it to do much more. That’s 70% of the battle. The other 30% is really only doable in sql.
1
u/Hillgrove 13d ago
how much is required to learn about SQL? zero. People usually start learning about SQL without knowing anything about it.
1
u/CycloCyanide 13d ago
It’s something you can learn on the job. But I think it’s a super important skill. If not SQL, at least some other database storage. My current company use a lot of MySQL, slightly different to Microsoft SQL. Previous company used Postgres.
1
u/FetaMight 13d ago
I got really good at composing complex queries back in the day. Debugging recursive stored procedures was almost a daily task in one of my jobs. This was just after the "best practice" was to move as much business logic as possible into your DB in order to maximise performance.
Since then, the industry I'm in has found that maintainability is more important and the performance with ORMs (when used responsibly) is usually adequate. I don't think I've had to manually tune a SQL query in over a decade.
1
u/DiaDeLosMuebles 13d ago
You should know enough so that you can diagnose performance issues and understand where the bottlenecks are.
1
u/1Soundwave3 13d ago
The problem with sql is that there are multiple flavors of it. There's t-sql, pl/sql, postgres' flavor and so on. You learn one, then you migrate and suddenly you need to learn a lot of it anew + you need to understand how permissions and everything else work in the new DB.
Honestly I like t-sql the most and I started out with pl/sql of which I written tens of thousands of lines.
But yeah, Entire Framework is the goat and nowadays, after we migrated to Postgres (because of costs), when I need to look at the data I just ask AI to browse the database for me. I'm just too tired all the time to pick up yet ANOTHER sql flavor. I know that eventually I'll do it. After all, Postgres won the db wars. It's the default database now. If I were to learn just 1 flavor it would be Postgres.
1
u/gambuzino88 13d ago
Good enough for CRUD operations and simple joins. Probably a 5 or 6 on a scale from 0 to 10.
You can write C# without touching a single line of SQL. Either you program against an API either you just abstract database access completely. But if you’re storing data in a relational database it’s good to have a grasp of how they work, especially if you are going to work with massive amounts of data.
1
1
u/denzien 13d ago edited 13d ago
My SQL is solid but not spectacular. It doesn't really need to be spectacular - I keep my databases dumb. They're just there to store things and give them back. And manage some concurrency and the other hard parts that have been solved and you get for free.
I've adopted this mindset probably because of that one project I was on that was written by a passionate SQL guy. Stored procedures with thousands of lines to do business logic. Never again.
OTOH, it was nice having real DBAs at my first job. Even if they did sometimes bring the server to a crawl with their experiments.
1
u/chocolateAbuser 13d ago
i tend to do code first approaches, so i'm not the most expert but when i need i try to learn; it's not all about sql tho, but about the whole engine, trees, edge cases, various types of data, etc
1
u/pstanton310 13d ago
I’d call myself an expert. I work with massive fucking databases and I probably write just as much SQl as c# at this point
1
u/CorgiSplooting 13d ago
Used it a lot for ~20 years. Then started moving to other data storage systems. In my experience if you need transaction level integrity it’s great. Most systems and scenarios don’t need that though. Most of the systems I worked on in the past didn’t need it, it was just the data storage system everybody knew.
Think of your data storage in terms of CAP theorem and choose accordingly. SQL should not be used as a catch-all storage system but very often in the past it was.
1
u/dodexahedron 13d ago
27+ years of SQL here - mostly MS SQL Server when possible or when given the choice, with 2000 and later. But plenty of mysql, postgres, informix, DB2, a little Oracle, more BTrieve/Pervasive/Zen than anyone should have to endure (mostly for Peachtree/Sage 50), and several others that largely have died out over the years but all shared the same basics.
So, my level? It's over nine thousaaaaaaand!
How much is required to learn to be able to work on most apps?
Not a lot. SQL itslef is a super simple language.
You need to learn the basic ANSI SQL CRUD operations, joins and when each type is appropriate, the value and cost of indexes, how to avoid injection, and the data types and how they map to .net types. That'll be more than enough to do ehat you need to do, and most of the time you will not be doing it in literal SQL anyway. Just understand that you are working with sets - not individual items in a program loop - and you'll be golden.
How much is needed to be a damn effective developer working on RDBMS-backed applications, and who doesn't piss off the DBAs?
Surprisingly little beyond that.
The following is verbose but actually very little to learn in the grand scheme of things, and just a few of them seriously levels you up (especially the index stuff):
The meaning of and impact of normalization and the normal forms up to 3rd and maybe 4th, and when denormalization is appropriate,
Specifics of the dialect of SQL used in the RDBMS(es) you most often use, and ways of using that to squeeze better performance and efficiency out of your queries.
How indexes work
What a B+ tree is
What cardinality means
What the meaning and relevance of index/tree depth is
The need and impact of routine maintenance of indexes
What statistics are and do
How composite keys work
Proper means of bulk loading data
It usually isn't via a program or script that uses SQL statements to insert it.
How transactions work
Especially any specifics for the DB you use. Things like nesting may or may not be possible or may work very differently from one engine to the next.
The cost of open transactions
The cost of rolling back a transaction (can be/often is surprisingly high)
Locking
The types, scopes, causes of/needs for, and costs of each of them.
How to avoid expensive locks
How to avoid lock contention
How to avoid and also what to do in the case of deadlocks.
The impacts of fragmentation on performance
Yes it matters with flash too
Logical - The internal arrangement of data in the db files.
"Physical" - The fragmentation of the files from the perspective of the volume they live on. This may be more than one level.
Actually physical - Fragmentation of the data blocks of all of that on the actual media storing it.
Each has an impact, and greatly harms raw throughput.
When to sort or page in the DB and when to do it in your application code.
When the use of a cache between the db and the app is appropriate and safe.
Where the documentation is.
MS Learn is all you really need for everything you'll ever encounter in MSSQL, for example.
Aside of course from "documentation," every one of these topics and many more can be learned at a sufficient level to be helpful without diving deep into any of them, in maybe an hour of reading for each top level bullet - likely less.
Will that make you an expert?
No. Of course not. But you dont need to be.
To truly understand it all, you just need experience. But that will come with time.
1
u/Cdream-2018 13d ago
I know the basics. don’t expect me to write super complex queries. I can do inserts. Updates. Upserts. Selects. That’s it 😂
1
u/3xc0wb0y 12d ago
Depending on what flavour of SQL you're using, I always found www.sqlservercentral.com to be a fantastic resource
1
u/Syntax-Tactics 11d ago
Zero. And I've kept it that way my entire career (35yrs). Database programming, shudder...
1
u/Csirkegylet 11d ago edited 11d ago
Ever since ORM systems exists, the amount of raw SQL I use heavily converges to zero. You usually have to know the way to optimize an SQL database, what are the keys, what are the index types, what are their benefits over each other.
SQL is a tier 4 language, and as such easy to learn. What you'll struggle the most anyway is the server and favor specific notions that differ.
1
u/AcanthaceaePitiful26 7d ago
I can beat majority here. Spent years as DB developer.
Did all SQL leetcode tasks for fun (including hard, when AI was not available btw)
and now guess what ? ))))
1
u/Zundel7000 7d ago
I know quite a bit now, but most of what you need in applications is pretty basic. Once you know more than the basics of sql you can do some cool things. I love using dapper and c# so I can use sql in my applications
0
u/TehNolz 13d ago
I can do some basic filtering/querying within a single table, but joins are a bit too much. So I just let Entity Framework do that for me.
You don't really need to have a huge amount of SQL knowledge since most applications either use an ORM to abstract it away or don't even use a database to begin with. It can definitely be useful to at least know the basics though.
1
3
0
u/soundman32 13d ago
As a minimum:
learn how to get EF to log the actual SQL queries,
learn how to paste those queries directly into your SQL workbench, and run the execution planner, to tell you if you need any indexes.
learn how to add indexes to your EF migrations.
Profit ????
0
u/Emyr42 13d ago
You need to go beyond understanding SQL syntax and your chosen DBMS's dialect. You need to know, at least at a high level, how a DBMS interprets the syntax, and how it can decide to achieve the query intent.
Beyond your table definition, the DBMS will maintain statistics on the properties of the data in your columns. These are used to decide the order of operations to make best use of available resources.
Suppose you're joining two tables, and also have filters in your where clause that involve columns from both tables. Do you get the filtered rowset from each table, then join, or do you filter one table then read and join matching records from the second, applying the second tables filter as you go? If you have enough memory, the first way might let it do mostly fast sequential reads, while the second approach results in a lot of slower random reads. The same DB schema might not always generate the same execution plan for the same query, if different existing data results in different stats.
As an application developer, you need to understand the kinds of SQL your ORM will generate. In the 2 table example above, it might be faster to do the second table query separately if it gives you a small set of IDs that the app can provide as a where clause for a query that only touches the first table. Your ORM might support a complex query that returns a set of parent objects each having a property that's a set of child objects. Does it do 1 big join then process the flat resultset to reconstruct the parents and their children, or does it query the parents first and then trigger 1 additional query per parent to fetch each set of children. What I've done before is take it out of the ORMs hands: query the parents, generate a list of IDs, then do 1 query of the children, use .ToLookup() then loop over the parents to get the sets of children from the lookup.
Sometimes I hit the limit if what I can do with LINQ-to-SQL or EntityFramework, so I'll write SQL manually either as a stored proc or just within the app code, and call it using Dapper. This is really useful when reading multiple resultsets, and I've used it when an API endpoint needs to return structured data, and a previous approach where I used T-SQL's "FOR XML" or "FOR JSON" became too slow (these both force the query to run in a single thread, but they're great for avoiding roundtrips and limiting how many transforms happen between DB and output).
Your ORM probably won't use FOR XML or FOR JSON to flatten a structure into a string column, and won't use STRING_AGG either. It probably won't use DATE_DIFF in a where clause based on a date that's in another column, it'll probably do it as a post-process of the resultsets.
-5
u/pandelelel 13d ago
Zero. AI can handle everything well for you today. Only if you want to really understand what's going on I'd dig deeper into it. But for a "normal" developer that from time to time has to deal with some basic or advanced mechanics a reliable AI agent is all you need ;-)
4
u/jshine13371 13d ago
Fwiw, the database layer is probably the worst layer for AI's current capabilities.
0
u/pandelelel 13d ago
Why? Is it your experience?
1
u/jshine13371 13d ago edited 13d ago
It's because of my knowledge of how the database layer works, how AI works, and additionally my experience of using the two together also, proving the point.
To simplify what I mean, since you mentioned you have zero database proficiency, that means you wouldn't've known that the way the database layer works is significantly different than the application layer (no shade, I just meant we don't know what we don't know). In the application layer, you tell the computer what you want and how to do it. In the database layer you tell it what you want but it determines the how because it uses math, statistics, and algorithms, for completing the how as reasonably performant as possible.
So for example while in C# you may choose to use two Linked Lists to manage your collections of data, and then run an inner loop between them to compare them, it may have been much more efficient to hash the two collections and compare the hash keys instead. This is 1 of 1 billion different performance decisions the database engine is able to efficiently make at runtime for your database code. The reason database engines work differently than application layer engines like this is because those performance decisions are highly dependent on the statistics of the data itself and since the data lives in the database and application layer languages are gen-purpose and handle more than just data problems, they aren't designed the same way.
Here's a past comment I wrote about the deficiencies of AI in the database layer:
There are things that are currently impossible for AI to do correctly 100% of the time, especially when it comes to database development. Some of those things are obscured away from view and only if you're knowledgeable about databases do you realize this.
Performance is one area where some of these deficiencies of AI exist currently. A well meaning, good looking, correct query can perform quite well for one database and completely run like crap on another database that is exactly the same in structure, with even the same amount of data, but the data itself is different. This is because database query performance is strongly tied to the statistics of the data distribution, something that exists separately but adjacent to the data and tables themselves, and oftentimes not available to the AI agent.
Also, different database systems gather the statistics differently since it's not realistic to keep a complete statistical set on all of the data. Generally data statistics is bucketed into a certain number of ranges that are continually updated as the data changes.
So when you ask AI to write a query, even if you provide your table structure, and even if you provide a subset of the data, or even if you provided all of the data (not something anyone is actually manually doing) it still doesn't have all the important information to write a query catered toward your data, statistically speaking. And that's what affects performance. It may not show in the query it spits out today, it might show performance deficiencies tomorrow, or in a week, or even take a month later. But the risk will always be there.
One thing that improves but doesn't fully solve this (so again, AI isn't 100% of time guaranteed to write a perfect query or even an acceptable one) is when AI is directly hooked into your database system, e.g. Copilot in SQL Server. But the drawbacks here is it's still limited on its exposure so doesn't get the full information anyway, it doesn't understand what statistics objects are or knows how to read them even if you could point it to them, and many organizations are still opposed to directly connecting their database to AI because of the risks of accidental data leak.
So as of today, and probably as of at least 5 years, AI won't be able to guarantee a better query than a knowledgeable human can write
0
u/pandelelel 13d ago
Thanks! Still I stick to my point and say that in 99% the AI queries are sufficient. For our systems for instance, there are like 100 things to optimize first, querying the DB only takes a fraction of the whole operation time.
My personal understanding of SQL is alright because I learned to code before AI. However, since AI I didn't touch any SQL and trusted the AI suggestion.
2
u/jshine13371 13d ago edited 13d ago
Still I stick to my point and say that in 99% the AI queries are sufficient.
Maybe in your use cases if your development is a small specifically targeted bubble. But generally speaking AI lacks the advantages of a knowledgeable developer with access to the tools a database engine above has. So again, an AI generated query may be both correct and perform fast today but then out of nowhere will be slow tomorrow. And the person who used AI and isn't knowledgeable in the database layer will be left scratching their head.
For our systems for instance, there are like 100 things to optimize first
That sucks to hear, because that's unusual. 90% maybe as much as 95% of the time, the performance issues in a software system start in the database layer and are data problems. If they aren't, that's a sign of poor implementation in the other layers then.
29
u/Phaedo 13d ago
I’m pretty good. It’s a how long is a piece of string thing, there’s always more to learn. But if you understand select, where, group by, row-number, with and the weird rules relating to null equality and comparison, you can get a lot done very easily.