> 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.”
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.
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.
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.
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!)
>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.
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.
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!
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.
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;
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.”