I've been using Sql Server for a long time (~6 years) and I've come out to the conclusion that it's a quality product, but you need to be an expert in it.
And it's not easy to become one. After 6 years of working with it I know I'm not. The data sets that I had to work with were not that large, the largest being
around 25 Gb of data, roughly speaking. But I have come into myriad of problems using it, some of them being:
After some work, googling and pain most of these are solvable, except for slow writing - that's by design.
I like tsql despite it's problems like working with strings and other quirks. I like the functional programming feel it gives. But unfortunately such code has
one problem - query planner will decide how to execute it and it might be slow. And you won't know why, because execution plan will tell you that 50% of time is
spent in some weirdly-named Sql Server operation, but it won't tell you what to do about it. And even having to look at execution plan is already a bad thing, it
has rarely helped me even after googling long about what the problematic operation was.
I've just spent 3 days trying to make one query fast. It was just one select with 10 joins and many where conditions, some quite complicated. The largest
table had 600 000 rows. And it was painfully slow, sometimes. I've tried to dig into why, but failed, it was slow because Sql Server made a bad decision when
executing it and I couldn't influence it. Finally, what I did was to keep temp table of resulting Ids and take one condition at a time intersecting the condition
results with current result set. It worked.
So the functional-like code that is promoted might and likely will be inefficient.
What's the moral of all this? Sql Server is overkill in most cases. Today Sql Server is a success because of sales-people and not because of it's design. First it shoots you in the foot then politely offers some medicine. Sql Server is hard. And things don't have to be hard.
Check out linqdb (https://github.com/ren85/linqdb) where I tried to make everything as simple as possible.
I'm also trying to raise funding for the server version here: https://www.indiegogo.com/projects/linqdb
Feel free to comment and, by the way, mentions work here (@renz).