Showing posts with label Procedure. Show all posts
Showing posts with label Procedure. Show all posts

Friday, 12 October 2018

Purging SQL Table without impacting the users

This happens very rarely but sometime I have been asked to conditional purge the SQL table without impacting the Users (So we can not use the truncate which is quick and harsh  :) ), These kind of request is pain when client says this table is business critical and used by different application 24/7 so can not take offline and delete the data. So I came up with SQL script or Stored Procedure which will purge the data without impacting the users, though this Proc can run slow or fast based on the Batch Size you have selected, Smaller (not Smallest) the batch size lower the deadlock risk. You can select the Batch and Total deleted row count based on your need. 

Hope, This proc will be useful for you and let me know if required any improvement. I have written this for SQL Server, you can rewrite for any other DB as well after small tweaking


Like the below page to get update  
Facebook Page      Facebook Group      Twitter Feed      Google+ Feed      Telegram Group     

Thursday, 27 August 2015

DB2 Column Compare Tool

            Whenever, we are working in Datawarehouse / Database (Oracle / MSSQL / DB2) projects, we often need to compare to tables column wise. When such requirement comes, we might end up in confusion putting the columns list in the excel sheet and comparing each column by column.

              Here’s procedure you can create in your local DB and run it feeding it with Tables names as input, and in one run you get three outputs: Matched columns, Table1 unmatched columns, table2 unmatched columns. You might need to give schema name of Table if there are two tables in SYSCOLUMNS with the same name.

You can modify or re-write the same procedure for your respective DBs.