3 ms·
SQL Server has columnstores, in-memory tables with compiled procedures (which can be combined with columnstores), graph processing, built-in machine learning ex
by manigandham 8y ago
SQL Server has columnstores, in-memory tables with compiled procedures (which can be combined with columnstores), graph processing, built-in machine learning extensions, AlwaysOn availability/replication groups, Stretch and Polybase to read from external sources, integrated encryption, fantastic tooling, and the best optimizer with batch/vectorized processing.
It still doesn't have proper upsert or JSON columns, so PostgreSQL wins on small usability features, but SQL Server is a serious workhorse when it comes to large scale operational and analytical systems with unmatched performance. PG barely has parallel queries or decent connection management and is a decade away from catching up to SQL server in these features.
- BrentOzar 8y ago> It still doesn't have proper upsert or JSON columns Upsert (MERGE) was added in SQL Server 2008 (although it's chock full of known bugs): https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... JSON was added in SQL Server 2016, although indexing it is still tricky: https://docs.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/js...
- manigandham 8y agoThat's what I meant by "proper". It can do it, but PG does it better. It's one area that I wish they would invest time into considering the large developer impact it would have.
- SigmundA 8y agoMy understanding is they got burned by all the work put into the XML datatype and don't want to repeat it with JSON, hence the more conservative approach.
- fanf2 8y agoHow do Stretch and Polybase compare to Foreign Data Wrappers? Is the integrated encryption comparable to pgcrypto?
- manigandham 8y agoFDWs are much more flexible, as they are basically an endpoint based on the SQL/MED standard that can be implemented by any module and there are dozens of options out there for connections. Stretch and Polybase are much more limited to Azure storage and Hadoop systems with deep integration into T-SQL, and scale-out using clustering.