* use LISTEN to be notified of rows that have changed that the backend needs to take action on (so you're not actively polling for new work)
* use NOTIFY from a trigger so all you need to do is INSERT/UPDATE a table to send an event to listeners
Could you explain how that is better than just setting up Event Notifications inside a trigger in SQL Server? Or for that matter just using the Event Notifications system as a queue.
The argument of the OOP I was responding to was about how Postgres was better than other SQL solutions due to 4 reasons, with SQL Server being explicitly named. I was merely wondering whether his reasoning actually considered the abilities of SQL Server.
Admittedly I used SQL Server pretty heavily in the mid-to-late-2000s but haven't kept up with it in recent years so my dig may have been a little unfair.
Agree on READPAST being similar to SKIP_LOCKED, and filtered indexes are equivalent to partial indexes (I remember filtered indexes being in SQL Server 2008 when I used it).
Reading through the docs on Event Notifications they seem to be a little heavier and have different deliver semantics. Correct me if I'm wrong, but Event Notifications seem to be more similar to a consumable queue (where a consumer calling RECEIVE removes events in the queue), whereas LISTEN/NOTIFY is more pubsub, where every client LISTENing to a channel gets every NOTIFY message.
Comments
Could you explain how that is better than just setting up Event Notifications inside a trigger in SQL Server? Or for that matter just using the Event Notifications system as a queue.
https://learn.microsoft.com/en-us/sql/relational-databases/s...
SQL Server can do that as well, using the READPAST table hint.
https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...
SQL Server has filtered indexes, are those not the same?
https://learn.microsoft.com/en-us/sql/relational-databases/i...
It's better because it's not SQL Server
Why? The article wasn't about that. Hear me out here, but there's value in having multiple implementations for the same idea.
The argument of the OOP I was responding to was about how Postgres was better than other SQL solutions due to 4 reasons, with SQL Server being explicitly named. I was merely wondering whether his reasoning actually considered the abilities of SQL Server.
Admittedly I used SQL Server pretty heavily in the mid-to-late-2000s but haven't kept up with it in recent years so my dig may have been a little unfair.
Agree on READPAST being similar to SKIP_LOCKED, and filtered indexes are equivalent to partial indexes (I remember filtered indexes being in SQL Server 2008 when I used it).
Reading through the docs on Event Notifications they seem to be a little heavier and have different deliver semantics. Correct me if I'm wrong, but Event Notifications seem to be more similar to a consumable queue (where a consumer calling RECEIVE removes events in the queue), whereas LISTEN/NOTIFY is more pubsub, where every client LISTENing to a channel gets every NOTIFY message.
I agree that SQL Server has similar functionality but Service Broker is pretty clunky compared to LISTEN.
Thanks for the links, I was wondering if SQL Server supports similar features.