7 ms·

Thank you for the regularly scheduled reminder as to why nobody should ever use MS SQL for any reason whatsoever.

rjbwork 3y ago
Until any other RDBMS has the combination of now-basic features like SQL:2011, declarative schema design with terraform-like plan/apply migrations (ssdt, azure data studio, DACPAC, etc.), easily configurable hosted cloud databases, actual good MERGE syntax, and more, I'll keep using it. Attempting to use Maria was a nightmare. I'd like Postgres more (and really want to) if they would at least implement more basic stuff like SQL:2011 and/or there was a good hosted deploy story - RDS/Aurora is fairly complex.
- Varriount 3y ago
  Could you expand on the declarative schema features? Isn't SQL more or less declarative by default?
  - LikesPwsh 3y ago
    You can compare the database schema in source control against what's been deployed, and automatically generate deployment scripts. https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpa... The APIs were historically part of Visual Studio, and only split out into dacfx recently. Someone braver than me could consider automated deployment from github actions etc. The hard part is that a lot of database schema changes cause data truncation (e.g. reducing the size of a column or re-ordering columns). You still need someone to review the generated code if it throws an error for potential data loss.
    - rjbwork 3y ago
      >You still need someone to review the generated code if it throws an error for potential data loss. Yeah, exactly what I meant by the terraform-like plan/apply stages. You generate the change script and save it as your "plan", and then have an apply step that takes a backup and runs the script.
- MarkusWinand 3y ago
  I wonder why you are so focused on SQL:2011? Since then there was SQL:2016 and now there is SQL:2023. Also, which features in particular are you missing in PostgreSQL? Merge was added with PostgreSQL 15 a year ago: https://modern-sql.com/caniuse/merge https://modern-sql.com/caniuse/merge
  - refset 3y ago
    Temporal tables, most likely. Although even SQL Server only supports system time versioning, not full bitemporal tables (as per the spec).
    - MarkusWinand 3y ago
      The existing implementations (Oracle DB, SQL Server, MariaDB, Big Query) come with their problems too. I was a big fan of the new features when it came out in 2011, but pratically there is an unsolved elephant in the room: It doesn't cover schema changes.
      - refset 3y ago
        > there is an unsolved elephant in the room: It doesn't cover schema changes. 100% agreed. It's remarkable how Datomic also arrived on the scene in the same era (2012) but actually managed to solve a lot of these hard issues of immutable versioning + schema evolution via a clean EAV-based information model and an emphasis on accrete-only schema changes. I'm a big fan of your work by the way :)
  - rjbwork 3y ago
    2016/2023 got pretty widespread support across databases for the functionality I care about, both before and after adoption - namely the JSON stuff. MSSQL also already had pretty good graph support AFAIK, although I haven't used it that much. I find the temporal table stuff really useful and they drastically simplify a number of requirements, so it's annoying that the only non-proprietary DB that supports it is maria.
- chasil 3y ago
  Why won't SQL Server implement the ANSI standard for Persistent Stored Modules (PSM)? https://en.wikipedia.org/wiki/SQL/PSM https://en.wikipedia.org/wiki/SQL/PSM This is so pervasive in both free and commercial databases, that it is a gaping hole in SQL Server where obvious functionality should be. I have thousands of lines of this stuff that will never see the light of day in a conversion because of Sybase Transact-SQL. Why is Microsoft addicted to this very much not standard language, that they did not even originate?
- dspillett 3y ago
  > actual good MERGE syntax While not the shit-show of bugs it was upon introduction in SQL Server 2008, there are still reasons to be careful with MERGE in SQL Server: it can still deadlock with itself in some circumstances, has issues with filtered indexes, can cause problems with CDC (wrong operation(s) get logged), … (I actually like SQL Server, but the implementation of MERGE found there-in is certainly not one of the things I like about it!)
vore 3y ago
Having slightly different syntax seems like a poor reason to not use MS SQL.
- sumtechguy 3y ago
  It is one of the better ones out there. I shy away from it because of cost. If it was similar in cost to the other ones then I would probably use it a lot more. Been using it from when it was watcom (for about 50 to 250 bucks a copy). Then MS took over and the cost went crazy not oracle crazy but crazy enough.
baq 3y ago
MSSQL is one of the best rdbms you can buy, the absolute best for some use cases. Its only downside is that it’s expensive. T-SQL idiosyncrasies are inconsequential.
- chasil 3y ago
  Would not some variants of Sybase be better, as the architecture is the same, and it has supported far more platforms which allow it to scale in ways that Microsoft SQL Server cannot?
  - baq 3y ago
    I’d been running couple TB mixed olap + oltp on 2016 a few years ago and a single cluster handled what otherwise today would be 3 or 4 different data stores today. Performance of properly tuned column stores was literally mind blowing. Haven’t even needed memory optimized tables. Can’t comment on Sybase. Didn’t need any other platforms.
    - chasil 3y ago
      Sybase and SQL Server came from the same code until release 4.8, so it would be familiar to you.
      - jeroen 3y ago
        That was 30 years ago, though. Things have probably diverged quite a bit.
        dspillett 3y ago
        There were significant under-the-hood rewrites for SQL Server 2005 at least, and no doubt many smaller changes throughout the versions even where not adding new features, though there is still plenty that remains the same or at least very similar. I can name one bug from way-back-then which will never be fixed because maybe something somewhere “relies” on the odd behaviour!

Until any other RDBMS has the combination of now-basic features like SQL:2011, declarative schema design with terraform-like plan/apply migrations (ssdt, azure data studio, DACPAC, etc.), easily configurable hosted cloud databases, actual good MERGE syntax, and more, I'll keep using it. Attempting to use Maria was a nightmare. I'd like Postgres more (and really want to) if they would at least implement more basic stuff like SQL:2011 and/or there was a good hosted deploy story - RDS/Aurora is fairly complex.

Varriount 3y ago
Could you expand on the declarative schema features? Isn't SQL more or less declarative by default?
- LikesPwsh 3y ago
  You can compare the database schema in source control against what's been deployed, and automatically generate deployment scripts. https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpa... The APIs were historically part of Visual Studio, and only split out into dacfx recently. Someone braver than me could consider automated deployment from github actions etc. The hard part is that a lot of database schema changes cause data truncation (e.g. reducing the size of a column or re-ordering columns). You still need someone to review the generated code if it throws an error for potential data loss.
  - rjbwork 3y ago
    >You still need someone to review the generated code if it throws an error for potential data loss. Yeah, exactly what I meant by the terraform-like plan/apply stages. You generate the change script and save it as your "plan", and then have an apply step that takes a backup and runs the script.
MarkusWinand 3y ago
I wonder why you are so focused on SQL:2011? Since then there was SQL:2016 and now there is SQL:2023. Also, which features in particular are you missing in PostgreSQL? Merge was added with PostgreSQL 15 a year ago: https://modern-sql.com/caniuse/merge https://modern-sql.com/caniuse/merge
- refset 3y ago
  Temporal tables, most likely. Although even SQL Server only supports system time versioning, not full bitemporal tables (as per the spec).
  - MarkusWinand 3y ago
    The existing implementations (Oracle DB, SQL Server, MariaDB, Big Query) come with their problems too. I was a big fan of the new features when it came out in 2011, but pratically there is an unsolved elephant in the room: It doesn't cover schema changes.
    - refset 3y ago
      > there is an unsolved elephant in the room: It doesn't cover schema changes. 100% agreed. It's remarkable how Datomic also arrived on the scene in the same era (2012) but actually managed to solve a lot of these hard issues of immutable versioning + schema evolution via a clean EAV-based information model and an emphasis on accrete-only schema changes. I'm a big fan of your work by the way :)
- rjbwork 3y ago
  2016/2023 got pretty widespread support across databases for the functionality I care about, both before and after adoption - namely the JSON stuff. MSSQL also already had pretty good graph support AFAIK, although I haven't used it that much. I find the temporal table stuff really useful and they drastically simplify a number of requirements, so it's annoying that the only non-proprietary DB that supports it is maria.
chasil 3y ago
Why won't SQL Server implement the ANSI standard for Persistent Stored Modules (PSM)? https://en.wikipedia.org/wiki/SQL/PSM https://en.wikipedia.org/wiki/SQL/PSM This is so pervasive in both free and commercial databases, that it is a gaping hole in SQL Server where obvious functionality should be. I have thousands of lines of this stuff that will never see the light of day in a conversion because of Sybase Transact-SQL. Why is Microsoft addicted to this very much not standard language, that they did not even originate?
dspillett 3y ago
> actual good MERGE syntax While not the shit-show of bugs it was upon introduction in SQL Server 2008, there are still reasons to be careful with MERGE in SQL Server: it can still deadlock with itself in some circumstances, has issues with filtered indexes, can cause problems with CDC (wrong operation(s) get logged), … (I actually like SQL Server, but the implementation of MERGE found there-in is certainly not one of the things I like about it!)

vore 3y ago

Having slightly different syntax seems like a poor reason to not use MS SQL.

sumtechguy 3y ago
It is one of the better ones out there. I shy away from it because of cost. If it was similar in cost to the other ones then I would probably use it a lot more. Been using it from when it was watcom (for about 50 to 250 bucks a copy). Then MS took over and the cost went crazy not oracle crazy but crazy enough.

baq 3y ago

MSSQL is one of the best rdbms you can buy, the absolute best for some use cases. Its only downside is that it’s expensive. T-SQL idiosyncrasies are inconsequential.

chasil 3y ago
Would not some variants of Sybase be better, as the architecture is the same, and it has supported far more platforms which allow it to scale in ways that Microsoft SQL Server cannot?
- baq 3y ago
  I’d been running couple TB mixed olap + oltp on 2016 a few years ago and a single cluster handled what otherwise today would be 3 or 4 different data stores today. Performance of properly tuned column stores was literally mind blowing. Haven’t even needed memory optimized tables. Can’t comment on Sybase. Didn’t need any other platforms.
  - chasil 3y ago
    Sybase and SQL Server came from the same code until release 4.8, so it would be familiar to you.
    - jeroen 3y ago
      That was 30 years ago, though. Things have probably diverged quite a bit.
      - dspillett 3y ago
        There were significant under-the-hood rewrites for SQL Server 2005 at least, and no doubt many smaller changes throughout the versions even where not adding new features, though there is still plenty that remains the same or at least very similar. I can name one bug from way-back-then which will never be fixed because maybe something somewhere “relies” on the odd behaviour!