5 ms·

I would have said the same thing if I had never used MSSQL. PG just doesn't have the same quality SQL-dialect or tools as MSSQL though. Some very basic facilit

I would have said the same thing if I had never used MSSQL. PG just doesn't have the same quality SQL-dialect or tools as MSSQL though. Some very basic facilities are missing from PG like snapshot and transaction logs, batched triggers and being able to return multiple resultsets from a single trip to the database server. Even the free tools for SQL Server are better than anything you can get for PG and there's nothing like SQL Server Data Tools.

fish_fan 9y ago
How is the JSON integration and geometry primitives? I had no idea it was competitive with postgres.
- snuxoll 9y ago
  "JSON" in SQL server is a set of functions layered on top of NVARCHAR(*) columns, so - about where PostgreSQL was when they first introduced the 'json' data-type. Can't say I've investigated the geometry types, but at a glance they're functional (if uglier to work with).
jenkstom 9y ago
Then again, T-SQL is a developer's nightmare. Especially when compared to PostgreSQL's version of SQL scripting. And when you add on all the supported languages it makes MSSQL quite painful from a SQL developer standpoint.
- Neckmeats 9y ago
  This sounds like more of a religious issue than anything. All of the dialects of SQL have pros and cons, but if you stick as close to the ANSI standard as possible, the differences are negligible. If today's developers actually cared about relational theory, and spent the time to do it right, they wouldn't really care much of the difference between T-SQL or any other dialect. Like when developers bring up a platform preference based on JSON data type support... you're doing it wrong to begin with.
fulafel 9y ago
You can return multiple result sets using a sproc or a view. Triggers sound strictly less powerful in SQL Server since "for each row" semantics is missing? Meanwhile there are many PG features missing.
- snuxoll 9y ago
  > Triggers sound strictly less powerful in SQL Server since "for each row" semantics is missing? Triggers in MS SQL are batched, you get "for each row" semantics by iterating over the contents of the virtual "inserted/updated/deleted" tables. This is honestly a pretty sane design choice, it helps trigger performance immensely when dealing with bulk data manipulation since you can do things like JOIN's on the whole set of triggered data allowing the execution engine to do one index scan or hash join instead of a bunch of index lookups on individual rows. Ironically, after doing salesforce.com development for a while this is one of the things I really wish PostgreSQL offered.
snuxoll 9y ago
> PG just doesn't have the same quality SQL-dialect or tools as MSSQL though. T-SQL sucks, I don't know how on earth you get the feel of "quality" from it. Every time I have to work with it I cry, pl/pgsql is a hell of a lot nicer and I can feel comfortable stating that as an objective. As far as GUI tooling? Ya got me there, even as terrible as auto-complete is in SSMS it's leaps and bounds better than PGAdmin or DataGrip for pointy-clicky DBA tasks - though I've just learned to use the system tables/views in PostgreSQL like I have MSSQL because I can't stand pointy-clicky stuff. > Some very basic facilities are missing from PG like snapshot and transaction logs What? Snapshot isolation is available by default with PostgreSQL, it ONLY uses MVCC for transaction isolation. You want snapshot, set your transaction isolation level to REPEATABLE READ. And transaction logs? What on earth do you mean? Transaction logs are fundamental to PostgreSQL like they are any other RDBMS, you can ship them off for backups, do PITR with them, etc - just like you can SQL server. > batched triggers Yup, that's true. > being able to return multiple resultsets from a single trip to the database server. Technically doable with stored functions in PostgreSQL, though ill-advised. SQL Server makes this easier if you need it, for sure. > there's nothing like SQL Server Data Tools. Yup, although this is really down to a matter of preference. SQL Server Data Tools is great for warehousing projects, but for application databases using the database migration tools provided by your framework of choice work just fine. And hey, I've got respect for MS SQL Server, it's a well-performing, well-supported enterprise SQL database - not to mention SQL Server Analysis Services kicks some serious ass for business intelligence use-cases (especially considering it's included right in the package, and with the new PowerBI-derived Reporting Services you get a full BI package for a really good deal). But let's make sure we're all educated on tools before we start nitpicking them, eh?
- Neckmeats 9y ago
  How about some features that really matter for an enterprise... compare the differences of HA options between PostgreSQL and SQL Server. Or maybe something a little more simple like point in time recovery. Backup and recovery is probably the most important feature of an enterprise RDBMS, and I'm sorry, but PostgreSQL doesn't cut it. It's really fun to develop on, but it's an absolute nightmare to administer. That makes it a liability.

fish_fan 9y ago

How is the JSON integration and geometry primitives? I had no idea it was competitive with postgres.

snuxoll 9y ago
"JSON" in SQL server is a set of functions layered on top of NVARCHAR(*) columns, so - about where PostgreSQL was when they first introduced the 'json' data-type. Can't say I've investigated the geometry types, but at a glance they're functional (if uglier to work with).

jenkstom 9y ago

Then again, T-SQL is a developer's nightmare. Especially when compared to PostgreSQL's version of SQL scripting. And when you add on all the supported languages it makes MSSQL quite painful from a SQL developer standpoint.

Neckmeats 9y ago
This sounds like more of a religious issue than anything. All of the dialects of SQL have pros and cons, but if you stick as close to the ANSI standard as possible, the differences are negligible. If today's developers actually cared about relational theory, and spent the time to do it right, they wouldn't really care much of the difference between T-SQL or any other dialect. Like when developers bring up a platform preference based on JSON data type support... you're doing it wrong to begin with.

fulafel 9y ago

You can return multiple result sets using a sproc or a view. Triggers sound strictly less powerful in SQL Server since "for each row" semantics is missing? Meanwhile there are many PG features missing.

snuxoll 9y ago
> Triggers sound strictly less powerful in SQL Server since "for each row" semantics is missing? Triggers in MS SQL are batched, you get "for each row" semantics by iterating over the contents of the virtual "inserted/updated/deleted" tables. This is honestly a pretty sane design choice, it helps trigger performance immensely when dealing with bulk data manipulation since you can do things like JOIN's on the whole set of triggered data allowing the execution engine to do one index scan or hash join instead of a bunch of index lookups on individual rows. Ironically, after doing salesforce.com development for a while this is one of the things I really wish PostgreSQL offered.

snuxoll 9y ago

> PG just doesn't have the same quality SQL-dialect or tools as MSSQL though. T-SQL sucks, I don't know how on earth you get the feel of "quality" from it. Every time I have to work with it I cry, pl/pgsql is a hell of a lot nicer and I can feel comfortable stating that as an objective. As far as GUI tooling? Ya got me there, even as terrible as auto-complete is in SSMS it's leaps and bounds better than PGAdmin or DataGrip for pointy-clicky DBA tasks - though I've just learned to use the system tables/views in PostgreSQL like I have MSSQL because I can't stand pointy-clicky stuff. > Some very basic facilities are missing from PG like snapshot and transaction logs What? Snapshot isolation is available by default with PostgreSQL, it ONLY uses MVCC for transaction isolation. You want snapshot, set your transaction isolation level to REPEATABLE READ. And transaction logs? What on earth do you mean? Transaction logs are fundamental to PostgreSQL like they are any other RDBMS, you can ship them off for backups, do PITR with them, etc - just like you can SQL server. > batched triggers Yup, that's true. > being able to return multiple resultsets from a single trip to the database server. Technically doable with stored functions in PostgreSQL, though ill-advised. SQL Server makes this easier if you need it, for sure. > there's nothing like SQL Server Data Tools. Yup, although this is really down to a matter of preference. SQL Server Data Tools is great for warehousing projects, but for application databases using the database migration tools provided by your framework of choice work just fine. And hey, I've got respect for MS SQL Server, it's a well-performing, well-supported enterprise SQL database - not to mention SQL Server Analysis Services kicks some serious ass for business intelligence use-cases (especially considering it's included right in the package, and with the new PowerBI-derived Reporting Services you get a full BI package for a really good deal). But let's make sure we're all educated on tools before we start nitpicking them, eh?

Neckmeats 9y ago
How about some features that really matter for an enterprise... compare the differences of HA options between PostgreSQL and SQL Server. Or maybe something a little more simple like point in time recovery. Backup and recovery is probably the most important feature of an enterprise RDBMS, and I'm sorry, but PostgreSQL doesn't cut it. It's really fun to develop on, but it's an absolute nightmare to administer. That makes it a liability.