Change Data Capture - SQL Server 2012

We may have requirement that application need to keep track of all the changes that have been made to data. And this activity maintaining record history for auditing purposes, or for data security purposes.

Below is the example how do we work with CDC in SQL Server 2012

--User Database   USE TestDatabase --Create sample table to work on CDC CREATE TABLE [dbo].[tblEmployee]( [EmployeeID] [int] NOT NULL, [ManagerID] [int] NULL, [FirstName] [varchar](20) NOT NULL, [LastName] [varchar](20) NOT NULL, [Gender] [bit] NULL, CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED (    [EmployeeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 　  --Enable Change Data Capture (CDC) on Database   EXEC sys.sp_cdc_enable_db --Enable CDC at Table Level   EXEC sys.sp_cdc_enable_table @Source_schema = N'dbo', @source_name = N'tblEmployee', @filegroup_name = N'PRIMARY', @role_name =N'cdc_Admin', @supports_net_changes = 1 GO    　  --Check CDS is enable or not    SELECT Name As DatabaseName, is_cdc_enabled From sys.databases WHERE is_cdc_enabled = 1 SELECT Name As TableName, is_tracked_by_cdc From sys.tables WHERE is_tracked_by_cdc = 1 --Check the data base System Tables ( you can find highlighted objects which are automatically created once you enable CDC) and you find the jobs (capture, cleanup)     ---    SELECT * FROM tblEmployee SELECT * FROM [cdc].[dbo_tblEmployee_CT] /*1 = Delete 2 = Insert 3 = Before Update 4 = After Update */ --Perform the DML and check the data capture   INSERT INTO tblEmployee Values (1, Null, 'Mike', 'Fields', 1) INSERT INTO tblEmployee Values (2, 1, 'John', 'Hopkins', 1) INSERT INTO tblEmployee Values (3, 1, 'Henry', 'III', 1) --perform some actions on table to observe the changes Delete tblEmployee Where EmployeeID = 2 Update tblEmployee Set FirstName = 'Rama', LastName = 'Krishna' Where EmployeeID = 3 DECLARE @BEGIN_LSN BINARY(10), @END_LSN BINARY(10); SET @BEGIN_LSN = (SELECT MIN(start_lsn) FROM cdc.lsn_time_mapping Where CAST(tran_begin_time As DATE) = CAST(GETDATE()-1 AS DATE) and tran_id != 0) SET @END_LSN = (SELECT MAX(start_lsn) FROM cdc.lsn_time_mapping Where CAST(tran_begin_time As DATE) = CAST(GETDATE()-1 AS DATE) and tran_id != 0) SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_tblEmployee(@BEGIN_LSN, @END_LSN, 'all')

SQL Code for multiple delimiters in given string


CREATE TABLE #tableAll (InputString NVARCHAR(20))

CREATE TABLE #tableAllSep(ContactName NVARCHAR(50),RoleName NVARCHAR(50))

DECLARE @InputIds NVARCHAR(2000)

SELECT @InputIds='Ram@NewYaork,Raj@India,Yuvi@Aus'

DECLARE @SQL NVARCHAR(2000)

SELECT @SQL=''

SELECT @SQL ='INSERT INTO #tableAll SELECT '+''''+REPLACE(@InputIds,',',''' UNION ALL SELECT ''' )

SELECT @SQL = @SQL+''''

EXEC (@SQL)



--SELECT * FROM #tableAll

INSERT INTO #tableAllSep

SELECT LTRIM(RTRIM(LEFT(InputString,CHARINDEX('@', InputString)-1))),



SUBSTRING(InputString,CHARINDEX('@', InputString)+1,

LEN(InputString)-LEN(LEFT(InputString,CHARINDEX('@', InputString)-1))) FROM #tableAll

SELECT * FROM #tableAllSep

DROP TABLE #tableAll

DROP TABLE #tableAllSep


  

ORDER BY with REPLACE()

 

We can you Replace() with ORDER BY in SQL Server 2008 R2. Below is the sample example.

 

SELECT
ID,Name FROM

(
SELECT ID,Name FROM tblPerson

)
AS OrderTemp ORDER BY REPLACE (Name,'-',' ')

 

Dates Overlapping logic: 

DECLARE
@d1 DATETIME = '02-10-2012'

DECLARE
@d2 DATETIME = '02-14-2013'

IF
NOT EXISTS (SELECT 1 FROM table1

WHERE (@d1 BETWEEN start_datetime AND end_datetime) OR


(@d2 BETWEEN start_datetime AND end_datetime) OR


(start_datetime BETWEEN @d1 AND @d2) OR


(end_datetime BETWEEN @d1 AND @d2))

BEGIN

INSERT INTO Table1 (start_datetime,end_datetime,Comments)

VALUES (@D1, @d2 , 'Success')

END

SELECT
* FROM Table1