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
 

Working with UDFs in SQL JOINs:

CREATE
TABLE TableOne
(

Col1 INT,
Col2
CHAR(5)

)

GO

INSERT TableOne VALUES (1, 'One')
INSERT TableOne VALUES (2, 'Two')
INSERT TableOne VALUES (3, 'Three')

GO
SELECT

* FROM TableOne

CREATE
FUNCTION dbo.udfTableTwo (@Id INT)

RETURNS
@tbl TABLE (TID INT, TChar CHAR(1))

AS
 
BEGIN
 
DECLARE @test INT

SET @test = @Id

WHILE @test >= 0

BEGIN

INSERT @tbl VALUES (@Id, CHAR(65+@test))

set @test = @test - 1

END
RETURN

END

 SELECT * FROM dbo.udfTableTwo(2)

SELECT a.*, b.* FROM TableOne a join dbo.udfTableTwo(2) b

ON a.col1=b.TID

DROP TABLE TableOne

GO

DROP FUNCTION dbo.udfTableTwo

--- Example for Sample Functions

GO

CREATE
FUNCTION [dbo].[ufn_GetDaysInMonth](

@CurrentDate DATETIME )

RETURNS INT

AS

BEGIN

DECLARE @ReturnDays INT

SET @ReturnDays =

CASE

WHEN MONTH(@CurrentDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN MONTH(@CurrentDate) IN (4, 6, 9, 11) THEN 30

ELSE

CASE WHEN (YEAR(@CurrentDate) % 4 = 0 AND YEAR(@CurrentDate) % 100 != 0) OR (YEAR(@CurrentDate) % 400 = 0) THEN 29

ELSE 28

END

END

RETURN @ReturnDays

END

 -- ExecutingSELECT

dbo.ufn_GetDaysInMonth('02/10/2012') No_Of_Days_In_Month

GO

Debugging Stored Procedures in SQL Server 2008 r2:

In SQL Server 2008 + versions, we have option to debug stored procedures so that we can track our code and variables values in output window.

Example: Create a sample stored procedure

ISNULL(expressions)

In SQL 2008 r2, we have ISNULL() T-SQL predefined function to validate null values. If the retruning value is null then we can replace the variable with our predefined value.

Syntax: ISNULL(check_expression , replacement_value )

Eg:

DECLARE@IsPhoneNumber INT

SELECT @IsPhoneNumber = ISNULL(PhoneNumber,0) FROM dbo.tblAddress WHERE CustomerID= 1001

SELECT@IsPhoneNumber

The Result will be:

• Prints '0' if the PhoneNumber value is NULL for given customer ID.
• Prints 'PhoneNumber' if the given customerID holds phone number in that row.