Comment by dspillett | Hacker News Reader

Comment by dspillett | Hacker News Reader

Parent Full thread

> Note that you can still use these words as identifiers by putting them under double quotes (").

Of surrounding by square brackets in SQL Server, like [so] instead of like "so", which despite being non-standard is more commonly used in the MS SQL world because its behaviour is much more consistent than quotes in that environment.

Quoted (rather than bracketed) identifiers are supported but it depends upon a setting which may vary per DB or procedure. It is common to see the option ON these days a some features¹ depend upon it, and many tools like MS's SSMS default it to ON, but this can not at all be relied upon. See https://learn.microsoft.com/en-us/sql/t-sql/statements/set-q...

--

[1] From the documentation: “SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, then CREATE, UPDATE, INSERT, and DELETE statements will fail on tables with indexes on computed columns, or tables with indexed views.”

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

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.

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

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.

Temporal tables, most likely. Although even SQL Server only supports system time versioning, not full bitemporal tables (as per the spec).

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.

> 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 :)

> 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!)

Why won't SQL Server implement the ANSI standard for Persistent Stored Modules (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?

Could you expand on the declarative schema features? Isn't SQL more or less declarative by default?

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/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.

>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.

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.

I really gotta appreciate that someone's willing to say this out loud, rather than just pretending than MSSQL must be horrible because it comes from MS. If MSSQL was OSS Postgres wouldn't stand a chance.

This is ridiculous, Postgres is hands down a superior database at any price point.

Sorry, no. Not even close. Postgres is very good at what it can do and very cheap, but it just doesn’t do very much compared to SQL server or others. I’ve ran both mssql and Postgres in prod in the same project in perhaps not big but still nontrivial scale (low single digit terabytes) and SQL server made some things possible which Postgres simply didn’t have an answer for - column stores being the notable single killer feature if I had to name only one.

I'm currently running SQL Server and Postgres in the same project, and I would love to dump SQL Server. Its geospatial "capabilities" are so laughably bad, they should just stop advertising that they exist at all, and the unpredictable locking behavior alone should disqualify it from being used in any modern software project. And sure, it has column stores, which postgres has in extensions, but even if I needed column stores, SQL Server would still be last on my list, because there are so many better options out there.

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?

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.

Sybase and SQL Server came from the same code until release 4.8, so it would be familiar to you.

That was 30 years ago, though. Things have probably diverged quite a bit.

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!

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

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.

select [select] from [from] where [where] = [equals]

?

Here is my try :

  WITH [WITH] AS (
    SELECT [AS], [IN], [ON]
    FROM [UNION]
    WHERE [AND] = [OR]
  ),
  [OUTER] AS (
    SELECT [LEFT], [RIGHT], [FULL]
    FROM [CROSS]
    WHERE [INNER] = [OUTER]
  ),
  [GROUP] AS (
    SELECT COUNT(*) AS [HAVING]
    FROM [ORDER]
    GROUP BY [GROUP]
    HAVING COUNT(*) > 1
  )
    SELECT 
    [WITH].[AS], 
    [OUTER].[LEFT], 
    [GROUP].[HAVING]
    FROM 
    [WITH]
  JOIN 
    [OUTER]
  ON 
    [WITH].[IN] = [OUTER].[RIGHT]
  LEFT JOIN 
    [GROUP]
  ON 
    [WITH].[ON] = [GROUP].[HAVING]
  WHERE 
    [WITH].[AS] = [OUTER].[LEFT]
  AND 
    ([GROUP].[HAVING] IS NULL OR [GROUP].[HAVING] > 1)
  ORDER BY 
    [WITH].[AS] ASC;

I shouldn't even try. I'm clearly way out of my league here. Have an updoot.

That’s actually pretty clean in terms of being self documenting.

This is the way.

INSERT INTO [FROM] ([SELECT], [WHERE], [EQUALS]) VALUES( 'WHY YES', 'IT DOES', 'WORK')

This is how you get maintenance developers to take out hits on you.

Bonus points if the tables contain highly abstracted data, so that there is no obvious name you could rename the tables and their columns either.

Reply on news.ycombinator.com