4 ms·

> I think everyone already knows my opinions about MERGE not everyone is stuck on a 15-year-old version of SQL Server. odds are you can go ahead and use modern

by gfody 6y ago

> I think everyone already knows my opinions about MERGE not everyone is stuck on a 15-year-old version of SQL Server. odds are you can go ahead and use modern language features. I can understand why a DBA consultant that frequently encounters production issues with old bugs would feel this way though.

teh_klev 6y ago
SQL Server didn't introduce MERGE until 2008, so not quite 15 years. Also SQL Server bugs can take a long time to be fixed, if at all: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... In my own experience of working with MS SQL Server since 1998 (6.5), Microsoft are fairly conservative about changes to the core database engine, despite seeing new releases every year or so. New features tend to be stuff built on top of the engine. In real life you're far more likely to encounter older versions of SQL running mission critical apps because upgrades are that one time where you're likely to screw up the crown jewels (your data), and so therefore many organisations tend to be conservative about that too. Now it's nice to get to work on a greenfield application where you can kick off on a shiny new version of SQL, but there's a good chance those bugs and behaviours are still likely to be present because the core engine is pretty much the same.
- gfody 6y ago
  with all due respect to Aaron, I've had this link shown to me by developers not wanting to write a simple merge statement instead of some block of semantically weird shit full of lock hints enough times for it to be annoying. be careful, of course, but don't be superstitious - start with straight forward code and test.
  - cogman10 6y ago
    The problem with merge is that it fails under contention, not under simple testing. We've had multiple issues with SQL Server caused by the usage of MERGE statements. It's been bad enough that we've ended up banning them as an organization in favor of the block of "semantically weird shit". Why? Because that block has become common, clear, and most importantly, doesn't tend to cause weird deadlocks, race conditions, etc.
    - gfody 6y ago
      > The problem with merge is that it fails under contention, not under simple testing. that depends on so much else going on with your database - fine policy but when I said "test" I didn't mean whatever you mean by "simple testing". > caused by the usage of MERGE statements "exacerbated by" is probably more accurate - just because replacing a merge statement with something else alleviated an issue doesn't mean that the root cause was some bug in SQL Server.
masklinn 6y ago
It's certainly not jus sql server. The implementor of INSERT…ON CONFLICT UPDATE in Postgres considered and rejected MERGE[0] on grounds that it's simply not specified to do what people generally want > MERGE, as implemented within other major systems and as described by the SQL standard does not require an atomic insert-or-update for even the simplest possible case of OLTP style insert-or-update. > SQL MERGE as implemented within other systems doesn't require that a unique index be defined on the column or columns of the MERGE statement's outer join. I am not enthusiastic about the idea of falling back to table-level locking when an appropriate index isn't available. That could be a foot-gun for the large majority of users that just want to solve their basic insert-or-update problem. A MERGE patch was submitted for pg11, it got bumped multiple time and returned with comments early 2019 with no visible activity since. [0] https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDG...
- gfody 6y ago
  this has nothing to do with SQL Server. on SQL Server MERGE is atomic, if the source has no indexes it's probably a temp table, of course evaluating "not matched" requires joining the source to the target, on SQL Server you should be thinking and working in sets and avoiding single row (RBAR) whenever possible. if you're doing highly concurrent single-row upserts you're already SQL Servering wrong and I'll concede that's a case where you probably need a bunch of lock hints (regardless if it's a merge statement or an update followed by an insert)

by gfody 6y ago

teh_klev 6y ago

SQL Server didn't introduce MERGE until 2008, so not quite 15 years. Also SQL Server bugs can take a long time to be fixed, if at all: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... In my own experience of working with MS SQL Server since 1998 (6.5), Microsoft are fairly conservative about changes to the core database engine, despite seeing new releases every year or so. New features tend to be stuff built on top of the engine. In real life you're far more likely to encounter older versions of SQL running mission critical apps because upgrades are that one time where you're likely to screw up the crown jewels (your data), and so therefore many organisations tend to be conservative about that too. Now it's nice to get to work on a greenfield application where you can kick off on a shiny new version of SQL, but there's a good chance those bugs and behaviours are still likely to be present because the core engine is pretty much the same.

gfody 6y ago
with all due respect to Aaron, I've had this link shown to me by developers not wanting to write a simple merge statement instead of some block of semantically weird shit full of lock hints enough times for it to be annoying. be careful, of course, but don't be superstitious - start with straight forward code and test.
- cogman10 6y ago
  The problem with merge is that it fails under contention, not under simple testing. We've had multiple issues with SQL Server caused by the usage of MERGE statements. It's been bad enough that we've ended up banning them as an organization in favor of the block of "semantically weird shit". Why? Because that block has become common, clear, and most importantly, doesn't tend to cause weird deadlocks, race conditions, etc.
  - gfody 6y ago
    > The problem with merge is that it fails under contention, not under simple testing. that depends on so much else going on with your database - fine policy but when I said "test" I didn't mean whatever you mean by "simple testing". > caused by the usage of MERGE statements "exacerbated by" is probably more accurate - just because replacing a merge statement with something else alleviated an issue doesn't mean that the root cause was some bug in SQL Server.

masklinn 6y ago

It's certainly not jus sql server. The implementor of INSERT…ON CONFLICT UPDATE in Postgres considered and rejected MERGE[0] on grounds that it's simply not specified to do what people generally want > MERGE, as implemented within other major systems and as described by the SQL standard does not require an atomic insert-or-update for even the simplest possible case of OLTP style insert-or-update. > SQL MERGE as implemented within other systems doesn't require that a unique index be defined on the column or columns of the MERGE statement's outer join. I am not enthusiastic about the idea of falling back to table-level locking when an appropriate index isn't available. That could be a foot-gun for the large majority of users that just want to solve their basic insert-or-update problem. A MERGE patch was submitted for pg11, it got bumped multiple time and returned with comments early 2019 with no visible activity since. [0] https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDG...

gfody 6y ago
this has nothing to do with SQL Server. on SQL Server MERGE is atomic, if the source has no indexes it's probably a temp table, of course evaluating "not matched" requires joining the source to the target, on SQL Server you should be thinking and working in sets and avoiding single row (RBAR) whenever possible. if you're doing highly concurrent single-row upserts you're already SQL Servering wrong and I'll concede that's a case where you probably need a bunch of lock hints (regardless if it's a merge statement or an update followed by an insert)