Run T-SQL in Parallel

Posted on by John H6 Comments on Run T-SQL in Parallel

Writing CLR procedures to run T-SQL concurrently is not an extremely new idea. I have seen a lot of implementations and I have written and improved it many times by myself as well. After those coding exercises, I found few important things were not (or just partially) addressed.

  • Termination of launcher session: Either the launcher session get cancelled or killed, running asynchronous workers should be cancelled.
  • Different ways to shut down a batch: Waiting Workers should be abandoned. Executing workers should be either cancelled or waited to be completed.
  • Effective monitoring: People want to see which session is running what.
  • Adjustable maximum threads in the course of execution.

Continue reading “Run T-SQL in Parallel” →

Setup Procedure to Run Automatically When SQL Server Starts

Posted on by John H2 Comments on Setup Procedure to Run Automatically When SQL Server Starts

That’s simple, setup a job and run the procedure and schedule it runs when SQL Server agent started. See the problem here? My title is “when SQL Server Start”, not “When SQL Server Agent”. SQL Server can start without agent. Procedure in SQL Server can be started automatically when SQL Server starts. In order to do that, you have to set “scan for startup procs” through sp_configure function, and then use sys.sp_procoption to set a procedure to be launched as server started. See example below.

Continue reading “Setup Procedure to Run Automatically When SQL Server Starts” →

Merge Statement

Posted on by John HLeave a Comment on Merge Statement

Merge statement was introduced by Microsoft in SQL Server 2005. It allows user to merge one table to another combining different ways of operation, inserting, deleting and updating, on the targeting table based upon the condition specified in the merge statement.
When Matched: It means when the record in the source table and target table matched by key. In this case, you have option of, update target or delete target.
When Not Matched: It means the records in the source table do not exist in the target. The possible operation is to insert record to the target.
When Not Matched By Source: it means the records are in target table but not in the source table. In this case, you can choose to update or remove the record in the target table .

Continue reading “Merge Statement” →