Can Sql Server 2008 Stored Procedures (or Triggers) manually parallel or background some logic?

Question

Hi folks,

If i have a stored procedure or a trigger in Sql Server 2008, can it do some sql calculations 'in another non-blocking thread'? ie. something in the background

also, can two sql code blocks be ran in parallel? or two stored procs be ran in parallel?

for example. Imagine we are given the job calculating the scores for each Stack Overflow user (and please leave all 'do that elsehwere/service/batch/overnight/etc, elswhere) after a user does some 'action'.

so we have a trigger on the Post table, so when a new post is INSERTED, the trigger fires off and part of that logic, it calculates the user's latest score. Instead of waiting for the stored proc to finish and block the current sql thread / executire, can we ask it to calc the score in the background OR parallel.

cheers!

Answer 1

SQL Server does not have parallel or deferred execution: each block of running code in a connection is serial, one line after the other.

To decouple processing, you usually have to use SQL Server Agent jobs or use Service broker. These start executing in a new connection, new session etc

This makes sense:

• What if you want to rollback your changes? What does the background thread do and how does it know?
• What data does it use? New, Old, lock wait, snapshot?
• What if it gets ahead of the main thread and uses stale data?

Answer 2

No, but you could write the request to a queue. Service Broker, a SQL Server component, provides support for this kind of thing. It's probably the best option available for asynchronous processing.