3 ms·

I used PostgreSQL as student and during research and had to switch to Microsoft SQL Server as my company uses that since forever. For features I used on both,

by oerpli 7y ago

I used PostgreSQL as student and during research and had to switch to Microsoft SQL Server as my company uses that since forever. For features I used on both, the average quality of the SQL Server implementation is between middling and pathetic (mostly the latter). I cannot recall a single instance where I thought "that's nice" or anything positive about how SQL Server does something. At best, it has been "this is not awful". Things that sucked especially hard: - Datetime API: In postgresql it is a joy to use and most features one would like to have to analyze data are directly there and work as they should. The analysis for my master's thesis was completely done in postgresql [1] (including outputting CSV files that could then be used by the LaTeX document) - without ever feeling constrained by the language or its features. Meanwhile, the way to store a Timespan from C# via EF is to convert it to a string and back or convert it to ticks and store it as long. - The documentation: The pgsql documentation is the best of any project I have ever seen, the one for SQL Server is horrible and doesn't seem to have any redeeming qualities. - Management: SMSS is a usability nightmare and still the recommended choice for doing anything. I assume there are better ways but I haven't found them and if I have, they usually didn't work. - The query language: In psql you can do things like select a < b as ab, b < c as bc , count(*) from t group by 1,2; \crosstabview To get even half of that in SQL Server one would do select case when a < b then 1 else 0 end as ab, case when b < c then 1 else 0 end as bc, count(*) as count group by case when a < b then 1 else 0 end, case when b < c then 1 else 0 end I think crosstab queries are unsupported (there are PIVOTs but I haven't yet looked into them a lot). Maybe my use cases are a bit weird and I haven't encountered the better features of SQL Server yet. Would appreciate if someone could point out what I am doing wrong. [1]: https://github.com/oerpli/MONitERO/blob/master/sql/queries/ringsize_flow.sql https://github.com/oerpli/MONitERO/blob/master/sql/queries/r...

dangerbird2 7y ago
Interesting how mssql has bad documentation. Judging by my experiences with docs for azure and various windows and dotnet APIs, Microsoft's documentation is usually pretty solid. Not that it's surpising, since proprietary rdbs'es tend to prefer "vendor lock-in" over "making the product pleasant to work with so the customer doesn't jump ship"
philliphaydon 7y ago
SQL Server has a ternary. So your example would be written like: select iif(a < b, 1, 0) as ab, iif(b < c, 1, 0) bc... Unsure what crosstabview is as I've never used it. I hate sql server tho. Basic things like indexable arrays in postgresql make it amazing.
- oerpli 7y ago
  This makes it somewhat easier on the eyes but is still 80% too verbose. Crosstabview converts a table from: |a x 0 |a y 1 |a z 2 |b x 1 |b z 5 to | x y z |a 0 1 2 |b 1 5

by oerpli 7y ago

dangerbird2 7y ago

Interesting how mssql has bad documentation. Judging by my experiences with docs for azure and various windows and dotnet APIs, Microsoft's documentation is usually pretty solid. Not that it's surpising, since proprietary rdbs'es tend to prefer "vendor lock-in" over "making the product pleasant to work with so the customer doesn't jump ship"

philliphaydon 7y ago

SQL Server has a ternary. So your example would be written like: select iif(a < b, 1, 0) as ab, iif(b < c, 1, 0) bc... Unsure what crosstabview is as I've never used it. I hate sql server tho. Basic things like indexable arrays in postgresql make it amazing.

oerpli 7y ago
This makes it somewhat easier on the eyes but is still 80% too verbose. Crosstabview converts a table from: |a x 0 |a y 1 |a z 2 |b x 1 |b z 5 to | x y z |a 0 1 2 |b 1 5