You know what we could do better at? Crappy explains from database engines. Crappy rate limiting capabilities. Poor feedback on keep cache pipelines fed during scans. Poor feedback on column size effects on reading stripes from disk and size alignments between the filesystem and database.
I use it daily in a business that is heavy on SPs and while I get by and am improving the jump from inner joins and selects to CTEs and the other wizardry is massive.
I want to be better at SQL but so many problems I hit up against and think “well that’s a 2 minute job in js/swift/php”
The thing about SQL is that it's the fastest way to read&write data in a relational database. Maybe writing the code is faster in js/swift/php, but the code will run faster in SQL. If you need to do something to 100M pieces of data you can do a lot worse than SQL.
(Of course if you apply some double standard where editing your "source code" requires multiple approvals whereas changing your "database" may be done at will in production then you'll find SQL logic easier to adjust, but that's a reflection of your policies rather than any fundamental reality.)
Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
Which is to say that you have little control over deployment. Adding a new foreign key constraint might block queries for an indeterminate amount of time while a new index is built, but you have no way to introspect or predict that at the SQL level (if you're lucky there might be a specific way to find out via the internals of your particular datastore implementation). PHP fans used to tout being able to just edit the code on the production server as an advantage, but most of us recognise it as the opposite.
> Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
Either add it to the application and push out a new version. Or handle it within the stored procedure.
Additionally, there would be the option of updating the database when pushing a new software update out.
> If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
I think I formulated it badly before. In this case I meant that we have one stored procedure for each thing we do. So that, if the underlying data changes for example, we only need to change the procedures directly acessing it. We can then call those procedures from multiple different parts of the code. And the procedures are all managed within one application, mssms in our case, where you can have a much cleaner structure than within code.
In my experience, in PHP for example, you tend to write VERY similar/same queries in multiple locations, which can cause you to find it harder to all instances.
It's trivial to write some code verifying "Is this value one of this list of values", but writing such code not ensure that this constraint will actually be met in 100% of your past and future data.
It's very difficult to guarantee that a business constraint expressed in your client app is actually enforced - there can be different versions of your app applying the constraint differently, there can be multiple apps accessing the datastore, there could be manual interventions in various ways to the datastore, the app could have code paths that may cause data insertion or alterations without running that verification in certain conditions, etc; so for all intents and purposes you can't really rely on that constraint.
If you have business logic in your datastore then that puts you in much the same position though. If you have a trigger you might have data written before it was introduced, or queries that ran with it disabled. If you're gradually rolling out a new version then you might have some data that follows a constraint and some that doesn't. And so on.
When gradually rolling out, you can have adjusted stored procedures that deal with the different versions, and turn off the old one when it is no longer in use.
So, I fail to see the problems you mentioned.
It was informational that I saw your viewpoint, even though I disagree. In the future I will definitely try and reevaluate my viewpoints to see if maybe I am wrong after all.
That is approximately never the case. Current widespread practices are moving away from it, with multi-services, and old fashioned practices of mixing customized and off the shelf tools basically forbid it.
For downstream derived computations, basically you either make the causal relationship explicit, or accept that you have eventual consistency. The only case where you can have inconsistency is where you have a "diamond" in your computations (i.e. you compute B that's derived from A and C that's also derived from A, and you compute D that's derived from B and C). So you figure out the business implications and either accept it or eliminate the diamond (by computing (A, B) from A and (B,C) from (A, B) instead of computing B and C separately from A). You will also get inconsistency if you do some totally ad-hoc query that's not part of your existing pipelines, but usually that's the kind of reporting query that doesn't need to be 100% consistent; if you do need a consistent version of that then the best approach is to take a regular snapshot, which can be consistent.
Basically you have a lot more precise control, you have causal relationships where you have explicit dependencies, so you have the level of consistency that you need for all your operational stuff, but you don't have a globally consistent realtime view of everything. It may take more work up front, but IME the notion of that kind of global consistency is a lie in a distributed world; even if you just have a basic webapp then you can't actually achieve the kind of consistency that that model pretends you have, because what the user is viewing in their browser (and then potentially making changes based on) at any given time is not necessarily the same as what's in the central database.
To get back to the question, this means that if inconsistency is due to an actual bug, it's pretty easy to solve: fix the bug and then regenerate from the original events. If you don't understand why data is inconsistent then you can always look back to the source events and figure out what it should be.
You only ever have one user in your web applications at a time?
> A different kind of datastore could offer you better performance and easier querying.
Citation needed.
The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code.
No, but database-level transactions don't help you deal with concurrent users. You don't keep a transaction open between showing the user an edit page and applying their changes (at least I hope you don't), your transactions can last at most through the web request-response cycle. So if you want actual transactional behaviour (e.g. a wiki with "another user is editing this page") you have to reimplement it yourself "in userspace".
> Citation needed.
SQL datastores are very open about the tradeoffs that they make for the sake of ACID isolation - the fact that the transaction isolation level is tuneable shows that, as does the fact that all serious SQL databases use MVCC.
Also MySQL had benchmarks showing that 75% of the time for a query by primary key was spent on parsing the SQL.
> The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code.
Not my experience, because the overwhelming majority of the time the code doesn't make any use of the complicated SQL functionality. I've literally never seen a cross join in use. I can count the number of times I've seen nontrivial aggregations in live code on one hand. The few times I've written recursive queries I found poor driver support, incompatibilities between different databases. So you pay the cost of flattening your data into the SQL table model, but most of the time you get no benefit from it.
There are pretty much three different kinds of queries: indexed lookups into raw data, indexed lookups into derived data (secondary indexes being a special case of this) and aggregations over a full table (table scans being a special case of this). A query model that represents those cases separately is easier to understand and work with, especially when it comes to understanding the performance. If your datastore supports server-side map-reduce style aggregations then you can literally execute arbitrary code in a "query", but you'll have a clear understanding that you're doing something different (with performance implications) from looking up a value by its indexed key.
When you are crafting good SQL query (crafting is the right word as each non-trivial SQL query is a little puzzle that may take you few days to solve because of the constraints) you need to stay within the bounds of the this fast db world.
Whenever you are forced to open a cursor or use CTE for recursion or even have a full table scan you already left the fast land and landed in the world that all general purpose language inhibit, where you have to iterate and recurse and everything takes ages. And in that land any other language beats SQL because any other language has the syntax designed to make things easier in this world while SQL has the syntax that's just good enough for the fast world where operations are highly restricted and when it ventures into the slow world it's just a horrible mess.
If you "just learn SQL" without understanding the abstraction below it, it'll be difficult to be successful, much the same with anything else.