As a preface, let me say that SQL Server is great so far. It's rock-solid, and the query planner is wonderful. T-SQL itself is my only real gripe. That said, here are a few things that annoy me:
The major bummer during development was, for me, the lack of a command to drop a schema and all the things in it. I've found the ability to `DROP SCHEMA IF EXISTS foo` to be invaluable when iterating on DB designs. In T-SQL, the alternative is to maintain a "drop-all.sql" file, which is tedious and error-prone when making use of foreign-key relationships. (more generally, postgres' DDL seems more ergonomic)
I miss the simplicity of postgres' upsert capabilities. T-SQL has MERGE, which is vastly more complicated and verbose. I get that it is quite powerful and flexible, but I've never personally needed more than upsert semantics, and the design of MERGE means that I pay (in esoteric syntax) for features that I don't use.
Table-valued params are an adequate substitute in T-SQL for postgtresql array parameters, but driver support outside of .NET is middling at best, so our Java code gets a little dodgy when using them.
Despite the more recent introduction of JSON support in SQL Server, there is no true equal to `json` or `jsonb` types.
There's simply no substitute in Azure SQL for the variety of procedural languages available in postgresql; T-SQL, as a procedural language, is just not very easy to work with. I'd much prefer python, perl, or even plpgsql itself.
Finally, I miss the ability to add comments on tables, columns, procs, etc, directly in SQL.
Those are the things that immediately impact me; others may not be bothered at all.
It's interesting, I've done a lot of work on SQL Server in the past and agree with you (better query planner, better partitioning support etc). But I mostly work on Postgres day to date now though, and have been pretty productive.
I actually prefer T-SQL's MERGE, partly because it's easier to get the ID out of the matched row than with Postgres, which ignores a RETURNING clause if nothing changed, and if you _force_ an UPDATE creates a new row version and increments any sequences involved. So I suppose I've been annoyed by both.
Mostly agree on the rest though. In addition to the language support, just being able to create an aggregate in SQL is a bonus for me inside Postgres, without having to do it on the CLR in SQL Server.
I think if MS are quick to support 10.0 when it's out, I agree this'll be a intriguing platform.
> the alternative is to maintain a "drop-all.sql" file
If you really want to drop a whole schema (i.e. all its constituant objects) and recreate, then you could write a procedure to do so by cursoring through sys.objects (joined to other system tables as needed, sys.schemas to filter by name rather than schema ID for a start) and issuing "DROP <OBJECT> <name>" for each via "EXEC (@VarContainingDropStatement)". Having a built-in might be nice, but writing your own reusable procedure (rather than a more hard-coded script) should not be difficult.
> tedious and error-prone when making use of foreign-key relationships
If following the "cursor through sys.objects" approach that can be managed by making sure FK constraints are dropped first, either by forcing the objects listed out of the cursor into a particular order or by a separate loop first.
> T-SQL has MERGE, which is vastly more complicated and verbose.
I'm with you there. It also has some odd locking issues that you need to be aware of before they bite your rear.
I'd agree generally that T-SQL has some room for improvement. Date and string manipulation functions are sometimes lacking I find, though as I use little other than SQL Server I can't comment on other environments being better/worse. Being able to easily define a new aggregate procedure as I believe you can in postgres is something I could have made good use of in the past.
> I miss the simplicity of postgres' upsert capabilities. T-SQL has MERGE, which is vastly more complicated and verbose. I get that it is quite powerful and flexible, but I've never personally needed more than upsert semantics, and the design of MERGE means that I pay (in esoteric syntax) for features that I don't use.
We've never encountered this kind of MERGE-related race condition before, but I'm not sure that we'd recognize it if we did. Probably it would surface as a failed transaction, but our merges are pretty low-volume so it hasn't happened yet.
We were using merge for a year before load increased and we ended up having deadlocks. After finding this blog and a couple of others, we removed the merge statements, deadlocks went away.
The major bummer during development was, for me, the lack of a command to drop a schema and all the things in it. I've found the ability to `DROP SCHEMA IF EXISTS foo` to be invaluable when iterating on DB designs. In T-SQL, the alternative is to maintain a "drop-all.sql" file, which is tedious and error-prone when making use of foreign-key relationships. (more generally, postgres' DDL seems more ergonomic)
I miss the simplicity of postgres' upsert capabilities. T-SQL has MERGE, which is vastly more complicated and verbose. I get that it is quite powerful and flexible, but I've never personally needed more than upsert semantics, and the design of MERGE means that I pay (in esoteric syntax) for features that I don't use.
Table-valued params are an adequate substitute in T-SQL for postgtresql array parameters, but driver support outside of .NET is middling at best, so our Java code gets a little dodgy when using them.
Despite the more recent introduction of JSON support in SQL Server, there is no true equal to `json` or `jsonb` types.
There's simply no substitute in Azure SQL for the variety of procedural languages available in postgresql; T-SQL, as a procedural language, is just not very easy to work with. I'd much prefer python, perl, or even plpgsql itself.
Finally, I miss the ability to add comments on tables, columns, procs, etc, directly in SQL.
Those are the things that immediately impact me; others may not be bothered at all.