Advertisements

SQL-BOX.COM

–SQL Server Storage

Category Archives: ARTICLES

How to Delete Millions of Rows From SQL Server Table ?

Sometimes there will be the requirement to delete million rows from a multi million table, as it is hard to run a single Delete Statement Like below Query 1 because it could eventually fill up your transaction log  and may not be truncated from log until all the rows have been deleted and the statement is completed because it will be treated as open transaction. Instead we can achieve the same by running the delete in multiple batches in an easy way.  Once we knew the number of rows we need to delete, we will choose the batch size and number of batches we need to run like in Query 2 where I need to Delete to 1 million rows, I chose batch size as 10000 and number of batches to 100 , so that 10000*100 equals to 1 Million rows. Number of batches could be more approximately.

Query 1:  Query to Delete records older than ‘1/1/2015’

Select Count(*) from History Where Date <= ‘1/1/2015’

1000000 – 1 Million rows and we need to delete all 1 million rows.

Delete from History Where Date <= ‘1/1/2015’

Query 2:

DELETE Top (10000) from History Where Date <= ‘1/1/2015’

Go 100

The Tricky part is ‘Go ‘ which is batch separator , the number we pass to the Go is the number of times you want to run that Query and each run time is considered as single batch.

Advertisements

SQL server updates or service pack hung or failed to install!!

        When installing sql server update or service pack, one of the reason to fail if you have invalid registry settings stored from previous installations and may not be valid anymore due to the change in location of the binaries when you installed previously. I recently had an issue while installing sql service pack 3 for sql 2008 r2 and thought it would be useful to share.

When ever your installation hung and does not move forward or failed, you can look into the installation logs that can be found under, C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log and every time you try to install it creates sub folder with logs in it, so check the time stamp for the current installation logs. Search through installation for any errors. Here is the sample of the error I got and the reason for the installation hang and never moves forward.

 

MSI (s) (0C:34) []: SOURCEMGMT: Attempting to use LastUsedSource from source list.

MSI (s) (0C:34) []: SOURCEMGMT: Trying source \\************\sql\Microsoft SQL Server 2008 DEV R2\x64\setup\sql_engine_core_inst_msi\.

MSI (s) (0C:34) []: Note: 1: 1314 2: \\************\sql\Microsoft SQL Server 2008 DEV R2\x64\setup\sql_engine_core_inst_msi\

MSI (s) (0C:34) []: ConnectToSource: CreatePath/CreateFilePath failed with: -2147483648 1314 -2147483648

MSI (s) (0C:34) [] ConnectToSource (con’t): CreatePath/CreateFilePath failed with: -2147483648 -2147483648

MSI (s) (0C:34) []: SOURCEMGMT: net source ‘\\**********\sql\Microsoft SQL Server 2008 DEV R2\x64\setup\sql_engine_core_inst_msi\’ is invalid.

MSI (s) (0C:34) []: Note: 1: 1706 2: -2147483647 3: sql_engine_core_inst.msi

MSI (s) (0C:34) []: SOURCEMGMT: Processing net source list.

MSI (s) (0C:34) []: Note: 1: 1706 2: -2147483647 3: sql_engine_core_inst.msi

MSI (s) (0C:34) []: SOURCEMGMT: Processing media source list.

MSI (s) (0C:34) []: Note: 1: 2203 2: 3: -2147287037

MSI (s) (0C:34) []: SOURCEMGMT: Source is invalid due to missing/inaccessible package.

 

As the error highlited in red, it is looking for the location of the binaries in the location where it was when the last time sql server or updates are installed as it stores this location in registry in location HKEY_CLASSES_ROOT>installer>Products> then we have to scroll down each product list under with some system number and find one for the SQl server Database Engine services and it will be missing for all other components too. So in order to fix this you need edit manually in registry and find the missing packages using a VB script provided from Microsoft as provide in the KB 969052.

 

 

The other easy workaround that I found and worked for me is try repair from programs and features > right click on SQL server > click change/remove >repair and not it will ask for the location of the SQL server binaries, once you pointed to the new binaries location it does repair the setup support files in registry and updates the new location of the missing packages for lastusedsource, once this is done reboot the server and re-install service pack and installed successfully.

If you click on net under the source list it will display your old location and new locations.

 


Finding top 10 large tables in each database in an instance of SQL server

It is a good practice to find and monitor your large SQL server tables, we may surprise in some cases where our large audit or logging tables that are not being cleaned up frequently and fill into multiple GB’s of data and may result in higher backup and restore times. Working with your development team on these large tables to see if they are serving any business else can be archived and reduces huge maintenance over head with backup/restores and also saves disk space available.

Here is the script that finds top 10 large tables that are ordered by reserved disk space.

 

--CREATING STAGING TABLES S
CREATE TABLE MASTER.DBO.SPT_SPACE
(
DBNAME VARCHAR(50) NOT NULL,
OBJID VARCHAR(300) NULL,
ROWS INT NULL,
RESERVED DEC(15) NULL,
DATA DEC(15) NULL,
INDEXP DEC(15) NULL,
UNUSED DEC(15) NULL
)
CREATE TABLE [DBO].[TOP10_LARGE](
[DBNAME] [VARCHAR](50) NOT NULL,
[TABLE_NAME] [SYSNAME] NULL,
[ROWS] [CHAR](11) NULL,
[RESERVED_KB] [VARCHAR](18) NULL,
[DATA_KB] [VARCHAR](18) NULL,
[INDEX_SIZE_KB] [VARCHAR](18) NULL,
[UNUSED_KB] [VARCHAR](18) NULL
) ON [PRIMARY]
EXEC SP_MSFOREACHDB 'USE ?

DECLARE @ID INT
DECLARE @TYPE CHARACTER(2)
DECLARE @PAGES INT
DECLARE @DBNAME SYSNAME
DECLARE @DBSIZE DEC(15,0)
DECLARE @BYTESPERPAGE DEC(15,0)
DECLARE @PAGESPERMB DEC(15,0)

SET NOCOUNT ON

-- CREATE A CURSOR TO LOOP THROUGH THE USER TABLES
DECLARE C_TABLES CURSOR FOR
SELECT ID
FROM SYSOBJECTS
WHERE XTYPE = ''U''

OPEN C_TABLES

FETCH NEXT FROM C_TABLES
INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

/* CODE FROM SP_SPACEUSED */
INSERT INTO MASTER.DBO.SPT_SPACE (DBNAME, OBJID, RESERVED)

SELECT DB_NAME(), OBJID = @ID, SUM(RESERVED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID

SELECT @PAGES = SUM(DPAGES)
FROM SYSINDEXES
WHERE INDID < 2
AND ID = @ID
SELECT @PAGES = @PAGES + ISNULL(SUM(USED), 0)
FROM SYSINDEXES
WHERE INDID = 255
AND ID = @ID
UPDATE MASTER.DBO.SPT_SPACE
SET DATA = @PAGES
WHERE OBJID = @ID
/* INDEX: SUM(USED) WHERE INDID IN (0, 1, 255) - DATA */
UPDATE MASTER.DBO.SPT_SPACE
SET INDEXP = (SELECT SUM(USED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID)
- DATA
WHERE OBJID = @ID

/* UNUSED: SUM(RESERVED) - SUM(USED) WHERE INDID IN (0, 1, 255) */
UPDATE MASTER.DBO.SPT_SPACE
SET UNUSED = RESERVED
- (SELECT SUM(USED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID)
WHERE OBJID = @ID

UPDATE MASTER.DBO.SPT_SPACE
SET ROWS = I.ROWS
FROM SYSINDEXES I
WHERE I.INDID < 2
AND I.ID = @ID
AND OBJID = @ID

FETCH NEXT FROM C_TABLES
INTO @ID
END

CLOSE C_TABLES
DEALLOCATE C_TABLES'

EXEC SP_MSFOREACHDB' USE ?
INSERT INTO MASTER.DBO.TOP10_LARGE
SELECT TOP 10
DBNAME,
TABLE_NAME = (SELECT NAME FROM SYS.SYSOBJECTS WHERE ID = OBJID),
ROWS = CONVERT(CHAR(11), ROWS),
RESERVED_KB = LTRIM(STR(RESERVED * D.LOW / 1024.,15,0) ),
DATA_KB = LTRIM(STR(DATA * D.LOW / 1024.,15,0) ),
INDEX_SIZE_KB = LTRIM(STR(INDEXP * D.LOW / 1024.,15,0)),
UNUSED_KB = LTRIM(STR(UNUSED * D.LOW / 1024.,15,0) )

FROM MASTER.DBO.SPT_SPACE A, MASTER.DBO.SPT_VALUES D
WHERE D.NUMBER = 1 AND D.TYPE = ''E'' AND DBNAME = ''?''ORDER BY RESERVED DESC'
GO

SELECT * FROM MASTER.DBO.TOP10_LARGE WHERE DBNAME NOT IN ('MASTER', 'TEMPDB','MSDB', 'MODEL')

--CLEANING UP ALL TABLES.

-- DROP TABLE MASTER.DBO.TOP10_LARGE

-- DROP TABLE MASTER.DBO.SPT_SPACE

 

 

Always – ON Availability group Alerts

After setting up Always ON , we need to configure SQL server Alerts to know about the availability Group errors and other failover information you may want to know when occurred. Here is the list of error and scripts to create them.

You may don’t want to configure all alerts and flood inboxes when happened, you can choose which one to configure using below query,

SELECT message_id [error_number], severity, text
FROM sys.messages
WHERE text LIKE (‘%availability%’)
AND is_event_logged = 1;


Here is the Script to configure all alerts related to AG’s

————————————————————
— Always – ON HA Error Alerts
————————————————————

USE [msdb]
GO

/****** Object: Alert [HA Error – 35254] Script Date: 10/20/2014 1:55:32 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35254′,
@message_id=35254,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35262] Script Date: 10/20/2014 1:55:43 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35262′,
@message_id=35262,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35273] Script Date: 10/20/2014 1:56:01 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35273′,
@message_id=35273,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35274] Script Date: 10/20/2014 1:56:16 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35274′,
@message_id=35274,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35275] Script Date: 10/20/2014 1:56:34 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35275′,
@message_id=35275,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35276] Script Date: 10/20/2014 1:56:46 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35276′,
@message_id=35276,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35279] Script Date: 10/20/2014 1:57:03 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35279′,
@message_id=35279,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35299] Script Date: 10/20/2014 1:57:23 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35299′,
@message_id=35299,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41048] Script Date: 10/20/2014 1:57:38 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41048′,
@message_id=41048,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41049] Script Date: 10/20/2014 1:57:56 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41049′,
@message_id=41049,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41050] Script Date: 10/20/2014 1:58:13 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41050′,
@message_id=41050,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41051] Script Date: 10/20/2014 1:58:29 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41051′,
@message_id=41051,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41052] Script Date: 10/20/2014 1:58:43 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41052′,
@message_id=41052,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41053] Script Date: 10/20/2014 1:59:06 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41053′,
@message_id=41053,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41054] Script Date: 10/20/2014 1:59:21 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41054′,
@message_id=41054,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41055] Script Date: 10/20/2014 1:59:34 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41055′,
@message_id=41055,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41089] Script Date: 10/20/2014 1:59:47 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41089′,
@message_id=41089,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Data Movement – Resumed] Script Date: 10/20/2014 2:13:37 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Data Movement – Resumed’,
@message_id=35265,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Data Movement – Suspended] Script Date: 10/20/2014 2:13:45 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Data Movement – Suspended’,
@message_id=35264,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Role Change] Script Date: 10/20/2014 2:13:51 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Role Change’,
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

Make sure to enable e-mail operator for each alert after executing above script.

What is Parameter Sniffing in simple words?

I have been asked recently what is parameter sniffing in an interview. Well, we all know how Optimizer in SQL server works to retrieving the data from the tables. We do also know that Optimizer reads the statistics and internal pre-compiled plans instead generating a new plan for parameterized Queries and Store procedures. Here we are, how Optimizer knows we are executing a similar query as it searching for pre-compiled plan for us because it was reading the parameters we passed to the stored procedure or a parameterized query and it is called “sniffing”.

What it counts for Performance, Good or bad?

 “It depends “Yes “It depends” – I know we use this word many times in regards to databases

Good:

By default, Parameter sniffing is enabled and SQL Server caches the plans and reads the parameters those passed to the stored procedures or parameterized SQL Queries. We benefit from it as Optimizer will go and search for the plan it compiled earlier in cache when we execute the same query again. Optimizer will not need to re-create the plan to execute the same query multiple times.

Bad:

This is all good only when there are good statics are maintained and typical parameters are used. What changes day by day is the requirement for using the new parameters with different words and optimizer will tries to use the same old plan in cache and may not be good for the new parameters as data has grown a lot and statistics have not updated yet and may be the plan for new parameter was doing table scans.

Work Around:

  1. Until we see a performance issue with this parameter sniffing it is better to leave it by default and we really benefit from it.
  2. When we see lot of performance issue, we could disable it using a trace flag 4136, this will disable the parameter sniffing at the server level.
  3. We could use it to disable only for specific query then we could use the recompile  query hint

Script to find the fragmentation level of specific table and its indexes in SQL Server

This script is very useful when you have large tables and queries against those table usually runs slow then you can find if that is due to fragmentation of the table and its indexes or not. This script gives detailed information about the fragmentation in percentage.

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = db_id(N'Database_Name');
SET @object_id = OBJECT_ID(N'Table_Name');
BEGIN
   SELECT *
   FROM sys.dm_db_index_physical_stats(@db_Id,@object_ID, NULL, NULL , 'DETAILED') AS IPS;
END
GO

Sample:

 

What is IN_ROW_DATA?

The allocation of data in a row is of three types

Allocation unit type Is used to manage
IN_ROW_DATA Data or index rows that contain all data, except large object (LOB) data.Pages are of type Data or Index.
LOB_DATA Large object data stored in one or more of these data types: text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), or CLR user-defined types (CLR UDT).Pages are of type Text/Image.
ROW_OVERFLOW_DATA Variable length data stored in varchar, nvarchar, varbinary, or sql_variant columns that exceed the 8,060 byte row size limit.Pages are of type Text/Image.

Here is beautiful script that gives the how much of data in you table is allocated into what type of allocation.

SELECT OBJECT_NAME(p.object_id) AS Object_Name
       , i.name AS Index_Name
       , ps.in_row_used_page_count AS IN_ROW_DATA
       , ps.row_overflow_used_page_count AS ROW_OVERFLOW_DATA
       , ps.lob_used_page_count AS LOB_DATA
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
WHERE OBJECTPROPERTY (p.[object_id], 'IsUserTable') = 1
order by IN_ROW_DATA desc,LOB_DATA

By running the above query you can know whether that table is used for Image or text data.

 

 

Track DML Changes Using after Trigger for Update, Delete and Insert rows- Capture Changed data rows using T-sql

Track DML Changes Using after Trigger for Update, Delete and Insert rows

There is out lot many options to track DML changes to data in SQL server like Change data Capture(CDC), Change tracking which are very easy to implement and make use of them but there are scenarios where we cannot use these CDC and change tracking as they were introduced in SQL server 2008. If you want to implement this in before 2008 versions the best way can be using of triggers.

We can track data changes to a SQL server table like update, delete and insert operations individually by creating AFTER Triggers for update , Trigger for Insert and Trigger for Delete. In our scenario I’m going to explain creating a trigger to track the data changes and save into audit table for each individual operation happens on rows in a table.

What is the purpose of this trigger?

  • Track the newly inserted or updated row and save into the audit table
  • Track the deleted rows and save into the audit table by using date stamp column

By implementing this trigger we can have the historical changes to the rows in a table and also we can query the table by using data column to find out the productivity of the table and how much operational is the table?

Note: This trigger captures only the operation occurred and on which row is effected, See below for the snapshot of the result.

Keep in mind that I’m using SQL Server Denali (CTP) Version for creating this trigger but also tested in 2005 and 2008 versions too.

In order to implement this Trigger, I’m creating test database by using the below script

/*Creating a Test Database*/
 Create database TestDB
 Go

 

 

 

 

 

 

Next, Creating test table to implement trigger on

/*Create Test Table to make use for Implementing trigger for DML Changes */

 Create table Test_table(
 id int identity
,Name varchar(50)
,phonenumber varchar(10) )

Next, insert some data into the table to perform testing the trigger using the below script

/*Insert data into Test Table for which we track DML changes*/

 Insert into Test_table(Name,phonenumber) values('Lucky',9191919191)
 Insert into Test_table(Name,phonenumber) values('Priya',0110101101)
 Insert into Test_table(Name,phonenumber) values('Meha',9987979237)
 Insert into Test_table(Name,phonenumber) values('stacy',9178697239)
 Insert into Test_table(Name,phonenumber) values('Nancy',9126827982)

/*select the rows inserted in the above script*/

select * from TestDB.dbo.Test_table

 

 

 

 

 

 

 

 

Next, creating an audit table in the TestDB to save the trigger tracked data from inserted, updated and deleted as below make sure that we are creating timestamp column to query later for historical changes to the table as below

/*Create Table to save Audit Data changes */

Create table TestDB.dbo.Test_table_Audit(
Effective_date datetime -- To get the date and time of the changed row
,Operation char(10) -- To get the operation occured like Insert or Update or Delete
,Id INT 
,Name Varchar(50)
,phonenumber varchar(10)
 )
 

 
 
 
 
 

 

 

 

In the next final step we will see how to create the After Trigger for all DML changes and save into the audit table using the below script which is well explained with comment lines.

Create TRIGGER  [dbo].[Tr_Test_Table_Audit]
ON [dbo].[Test_table] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
-- SET NOCOUNT ON added to prevent extra result sets from query execution

 SET NOCOUNT ON;

-- Determine if this is an INSERT,UPDATE, or DELETE Action

    DECLARE @operation as Varchar(10)
    DECLARE @Count as int
    SET @operation = 'Inserted' -- Setting operation to 'Inserted'
              SELECT @Count = COUNT(*) FROM DELETED
    if @Count > 0
        BEGIN
              SET @operation = 'Deleted' -- Set Operation to 'Deleted'
              SELECT @Count = COUNT(*) FROM INSERTED
              IF @Count > 0
              SET @operation = 'Updated' -- Set Operation to 'Updated'
        END
-- Capturing Delete Operation
    if @operation = 'Deleted'

        BEGIN
            Insert into Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
	        SELECT GETDATE(),'Deleted',ID,Name,phonenumber  from deleted
        END
ELSE
        BEGIN

-- trigger treats insert and update as same, so we can make it clear here 

            SELECT GETDATE(),'inserted',ID,Name,phonenumber from inserted

--Capturing Insert Operation
     if @operation = 'Inserted'

        BEGIN
             Insert into Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
	         SELECT GETDATE(),'inserted',ID,Name,phonenumber from inserted
        END

-- Capture Update Operation
ELSE

        BEGIN
              INSERT INTO Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
	          SELECT GETDATE(),'Updated',ID,Name,phonenumber from inserted

        END
END

Testing

Test Insert Operation – Insert a row into Test_table

/*Testing Insert Operation*/
insert into Test_table(Name,phonenumber) values('Microsoft',9190879979)

Then verify that the row we inserted above is tracked and saved in the audit table.

select * from dbo.Test_table_Audit where Name like ‘Microsoft’

 

 

 

 

 

 

Test Update Operation – Update a row just inserted above using below script

/*Testing Update Operation*/
Update Test_table
Set Name= 'SQLFRNDZ'
Where Name like 'Microsoft'

Then verify that the row we updated is tracked and inserted into audit table

select * from dbo.Test_table_Audit where Name like ‘SQLFRNDZ’

 

 

 

 

 

Test Delete operation- Delete a row we just updated above using below script 

/*Delete a row from the table test_table*/
DELETE FROM Test_table
WHERE Name like 'SQLFRNDZ'

Then verify that the row we deleted is tracked and inserted into audit table

/*Verify the deleted row*/
Select * from dbo.Test_table_Audit where Operation like 'Deleted'
 

 

 

 

 

 

 

 

 

I would to see any other better type of trigger if you have to track the changes in a similar way please reply at the bottom of this page or send it to

admin@sqlfrndz.com

 

 

 

 

 

Compare Two Tables Using T-SQL or Command Line Utility (tablediff.exe)

There is out too many ways to compare two tables in SQL server but I prefer to do this task in an easy way using EXCEPT and INTERSECT introduced before SQL Server 2005 but more enhanced and improved performance since SQL Server 2005.

Where do we make use of this comparison?

For example if you have same database in multiple environments and you would like to verify the data in those tables are also same or not.  Mostly this will be useful to compare the production tables to development server tables

Using EXCEPT and INTERSECT (T-SQL)

Conditions to use EXCEPT and INTERSECT

  • The number and the order of the columns must be the same in all queries or tables you are comparing.
  • EXCEPT and INTERSECT Commands performs based on results of the queries.
  • The data types must be compatible.

I will explain how to use this commands in different cases as below,

Case 1: Same number of columns and structure of the tables.

Comparing tables with same structure in different databases or instances to find matched and unmatched data in those tables

/*Returns all rows in Table_A that do not match exactly in Table_B*/

SELECT * FROM TABLE_A EXCEPT SELECT * FROM TABLE _B
/*Returns all rows in table B that do not match exactly in table B*/

SELECT * FROM TABLE_B EXCEPT SELECT * FROM TABLE_A

/* Returns all matched rows in both table_A and table_B*/

SELECT * FROM TABLE_A INTERSECT SELECT * FROM TABLE_B

Case 2: Unequal number of columns in two tables.

For example Table A has 5 columns and Table B has 10 Columns which 5 out of 10 columns are equal. Even though tables have unequal columns still you can use EXCEPT and INTERSECT to compare those 5 columns data

EX: SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE_A EXCEPT SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE_B

Case 3: Comparing tables in two different remote servers.

In this case you can use linked server as shown below

EX: Select * from Table_A EXCEPT select * from openquery(LinkedServer_Name, ‘Select * from Table_B’)

MSDN Reference: EXCEPT and INTERSECT

Using Command Line Utility (tablediff.exe):

The tablediff utility is used to compare the data in two tables. One luckier thing is it ships with SQL server for other replication purposes but we can make use of it for our purpose. It not only finds the unmatched data in two tables but also generates the script to synchronize those tables in comparison. This utility can be used from the command prompt or in a batch file to perform the following tasks:

  • A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
  • Perform a fast comparison by only comparing row counts and schema.
  • Perform column-level comparisons.
  • Generate a Transact-SQL script to synchronize unmatched rows on target table.
  • Log results to an output file or into a table in the destination database.

This tool can be found under following path in SQL server 2008 and 2011

C:\Program Files\Microsoft SQL Server\100\COM  ‘tablediff.exe’ Or if you cannot find the exact path to the location Open CMD> type dir /s tablediff.exe> then it displays the path to the tablediff.exe application.

Syntax: Open CMD and Use below syntax to run tablediff.exe utility

“C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe” -sourceserver MyServer1

-sourcedatabase MyDatabase1

-sourcetable MyTable1

-destinationserver MyServer1

-destinationdatabase MyDatabase1

-destinationtable MyTable2

-et DiffsTable

 

Thanks for reading this post….. 

%d bloggers like this: