5 ms·

This is also my big complaint when it comes to MS SQL -- there has been precious little development on their SQL language. It's almost like they're in a comple

This is also my big complaint when it comes to MS SQL -- there has been precious little development on their SQL language. It's almost like they're in a complete feature-freeze when it comes to the SQL language. MS SQL is missing some very basic SQL functionality.

eterm 11y ago
What in particular do you miss from other SQL languages?
- manigandham 11y ago
  UPSERT (or similar logic, not merge).
  - jklowden 11y ago
    Oh, then that's your problem. MERGE is standard, and has clear semantics. UPSERT is nonstandard, and semantics vary among implementations. Microsoft doesn't need to make SQL Server look like MySQL. Quite the other way around.
    - techdragon 11y ago
      There's not really a standard anymore for UPSERT to be added to. SQL has fragmented and since the last few iterations of the standard allowed for "additional non standard features", adding an UPSERT is perfectly compliant with the SQL standards.
    - manigandham 11y ago
      UPSERT should be a standard, why isn't it? And it's very useful in a ton of situations which is why so many other SQL dialects have added it. It's not like SQL Server is completely standards compliant so I'd rather have the functionality then have to use MERGE because it's "standard".
- tyfon 11y ago
  Date handling, simple things like postgres date_trunc('month', now()) and especially looping over records in stored procedures without using cursors (that are prone to crashes). I work with both on a daily basis and there is a mile of difference. Yes I can accomplish whatever I want in sql server but there are so many small things that could improve. Some of other pet peevees are: You can't use a lot of left joins, it slows down the query dramatically. If there is only one to one relation, use subselects instead. Thou shall not do queries like this: select foo into baz from bar where fooid not in (select fooid from bar) They are the death of the engine. And many "unwritten rules" like that. Postgres just accepts the queries and run them..
  - us0r 11y ago
    SELECT DATEPART(month,GETUTCDATE()) ? Either you are using a very old version or not using SQL Server at all cause I pretty much do everything you are complaining about on a daily basis and on a very large set of data.
    - tyfon 11y ago
      Your query only extracts the month integer from a date, it does not floor it to the month. The equivalent for sql server is this ugly thing: select dateadd(month,datediff(month,0,getdate()),0) We are on SQL server 2014. I have had a query go from 2-3 hours down to 7 minutes by replacing left joins with subselects within the query for instance (and yes all the indices are in place as recommended by sql server query planer) The one with a insert based on a query on the same table I have never managed to run at all and I have to use a temporary table as an intermediary instead.
      - Lozzer 11y ago
        The last one might be heped by trace flag 4199. You can apply it to individual queries with option (querytraceon 4199): https://blogs.msdn.microsoft.com/psssql/2010/09/01/slow-query-performance-because-inaccurate-cardinality-estimate-when-using-anti-semi-join-following-upgrade-from-sql-server-2000/ https://blogs.msdn.microsoft.com/psssql/2010/09/01/slow-quer...
orionblastar 11y ago
SQL Server was based on Sybase SQL Server. https://en.wikipedia.org/wiki/Microsoft_SQL_Server https://en.wikipedia.org/wiki/Microsoft_SQL_Server Microsoft rewrote the code so there was no more Sybase code, but it still followed Transact SQL standards. T-SQL is not as complete as PL/SQL and others. It is just that Microsoft is that 800 pound gorilla so people use their products. My last job I did migration from Excel and MS-Access to SQL Server using VB 6.0 code and ADO recordsets to copy and sync up data. They key was to use a column named date modified that had a time stamp when it was last modified to figure out which record was the newest and back up the old record to a history table in case it needed to be reverted later on.
- rat87 11y ago
  I thought Oracle or even db2 would be the Gorillas for serious corporate/goverment databases, and MySql would be the Gorilla for cheap + web stuff. I suppose Microsoft is pretty popular in beetween those 2 markets.
  - pionar 11y ago
    Oracle IS the gorilla, but with their recent pricing/licensing moves, more large orgs are exploring moving to SQL Server, especially ones that are already a Microsoft shop. My company is doing just that.
    - ryao 11y ago
      Why not explore PostgreSQL? You would be free of licensing/pricing moves.
      - breakingcups 11y ago
        Not GP, but I've found SQL Server significantly easier to both install and manage. Performance-wise I've yet to encounter a significant difference.

eterm 11y ago

What in particular do you miss from other SQL languages?

manigandham 11y ago
UPSERT (or similar logic, not merge).
- jklowden 11y ago
  Oh, then that's your problem. MERGE is standard, and has clear semantics. UPSERT is nonstandard, and semantics vary among implementations. Microsoft doesn't need to make SQL Server look like MySQL. Quite the other way around.
  - techdragon 11y ago
    There's not really a standard anymore for UPSERT to be added to. SQL has fragmented and since the last few iterations of the standard allowed for "additional non standard features", adding an UPSERT is perfectly compliant with the SQL standards.
  - manigandham 11y ago
    UPSERT should be a standard, why isn't it? And it's very useful in a ton of situations which is why so many other SQL dialects have added it. It's not like SQL Server is completely standards compliant so I'd rather have the functionality then have to use MERGE because it's "standard".
tyfon 11y ago
Date handling, simple things like postgres date_trunc('month', now()) and especially looping over records in stored procedures without using cursors (that are prone to crashes). I work with both on a daily basis and there is a mile of difference. Yes I can accomplish whatever I want in sql server but there are so many small things that could improve. Some of other pet peevees are: You can't use a lot of left joins, it slows down the query dramatically. If there is only one to one relation, use subselects instead. Thou shall not do queries like this: select foo into baz from bar where fooid not in (select fooid from bar) They are the death of the engine. And many "unwritten rules" like that. Postgres just accepts the queries and run them..
- us0r 11y ago
  SELECT DATEPART(month,GETUTCDATE()) ? Either you are using a very old version or not using SQL Server at all cause I pretty much do everything you are complaining about on a daily basis and on a very large set of data.
  - tyfon 11y ago
    Your query only extracts the month integer from a date, it does not floor it to the month. The equivalent for sql server is this ugly thing: select dateadd(month,datediff(month,0,getdate()),0) We are on SQL server 2014. I have had a query go from 2-3 hours down to 7 minutes by replacing left joins with subselects within the query for instance (and yes all the indices are in place as recommended by sql server query planer) The one with a insert based on a query on the same table I have never managed to run at all and I have to use a temporary table as an intermediary instead.
    - Lozzer 11y ago
      The last one might be heped by trace flag 4199. You can apply it to individual queries with option (querytraceon 4199): https://blogs.msdn.microsoft.com/psssql/2010/09/01/slow-query-performance-because-inaccurate-cardinality-estimate-when-using-anti-semi-join-following-upgrade-from-sql-server-2000/ https://blogs.msdn.microsoft.com/psssql/2010/09/01/slow-quer...

orionblastar 11y ago

SQL Server was based on Sybase SQL Server. https://en.wikipedia.org/wiki/Microsoft_SQL_Server https://en.wikipedia.org/wiki/Microsoft_SQL_Server Microsoft rewrote the code so there was no more Sybase code, but it still followed Transact SQL standards. T-SQL is not as complete as PL/SQL and others. It is just that Microsoft is that 800 pound gorilla so people use their products. My last job I did migration from Excel and MS-Access to SQL Server using VB 6.0 code and ADO recordsets to copy and sync up data. They key was to use a column named date modified that had a time stamp when it was last modified to figure out which record was the newest and back up the old record to a history table in case it needed to be reverted later on.

rat87 11y ago
I thought Oracle or even db2 would be the Gorillas for serious corporate/goverment databases, and MySql would be the Gorilla for cheap + web stuff. I suppose Microsoft is pretty popular in beetween those 2 markets.
- pionar 11y ago
  Oracle IS the gorilla, but with their recent pricing/licensing moves, more large orgs are exploring moving to SQL Server, especially ones that are already a Microsoft shop. My company is doing just that.
  - ryao 11y ago
    Why not explore PostgreSQL? You would be free of licensing/pricing moves.
    - breakingcups 11y ago
      Not GP, but I've found SQL Server significantly easier to both install and manage. Performance-wise I've yet to encounter a significant difference.