Transaction Isolation levels in SQL Server

Isolation levels are most helpful part in transactions when you are dealing with deadlocks. Other way when READ COMMITED isolation level is not helpful for your transactions.

We can say we have 5 major isolation levels in sql server.

READ COMMITED: It's a default isolation level. Other transactions are not able to perform any DML operations on the source which is being used by your transaction. Best Example opening account. You are not able to do any transaction until you open your account.

READ UNCOMMITED: You can set your transaction to READ UNCOMMITED when Other transaction are using your source. There are maximum possibilities of Dirty read in this isolation level.

REPEATABLE READ: Other Transactions can only insert new record (Phantom rows) to your tables but not able to update that records which are used by your transaction. Next transaction has to wait until your transaction is got finished. Best Example for this is Airline reservation system.

SERIALIZABLE: No operations are allowed by other transactions while your transaction is running under this isolation level. One of example you can see in wire transfer between accounts.

SNAPSHOT: When your transaction starts it takes snapshot of your resource. For this feature you must have enabled your database with Snapshot Feature in SQL SERVER engine.

    One more Type is included in SNAPSHOT feature is READ COMMITED SNAPSHOT.

    READ COMMITED SNAPSHOT: It takes Snapshot of resource from last commited transactions.

There are Down for Snapshot because it requires storage for taking snapshot.

CDC at column level in sql server uing T-SQL

CDC is one of the best feature to track about insert, update and delete records with values. This feature  is most helpful in DW incremental load. Same feature is also created using Audit tables and firing triggers but this feature is more faster to implement.

To demonstrate on cdc how it works in sql database we will first create sample table A

Create table A(
    AId int NOT NULL,
    Aname nvarchar(255) NOT NULL,
    Adds int NOT NULL,
    City nvarchar(255)
)

We require primary key constraint on first column Aid.

Alter table A
    Add Constraint PK_A
    Primary Key Clustered (
        Aid ASC )

If you Primary Key in table it must be included in column level CDC.

Inserting records in table

Insert into A
    values (100,'L1',2,'P'),
            (101,'L2',20,'F'),
            (102,'L3',12,'P')

Now for using CDC we first need to enable database

EXEC sys.sp_cdc_enable_db

Above statement will enable you current database with CDC

select name, is_cdc_enabled from sys.databases

Now we will enable CDC only for 2 columns Aid and AName in above table A

exec sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'A' ,
    @role_name = 'CDCRole',
    @captured_column_list= '[AId],[AName]'

When you enable CDC you can see CDCRole enble in you server.

select name, type, type_desc, is_tracked_by_cdc from sys.tables

Above sql statement will show you your table is enabled or not in your database?

insert into dbo.A values (3,'L7',22,'P')
insert into dbo.A values (4,'L8',32,'F')

Lets insert few records

declare @Start_lsn binary(10), @end_lsn binary(10)
select @start_lsn = sys.fn_cdc_get_min_lsn('dbo_A')
select @end_lsn = sys.fn_cdc_get_max_lsn()
select * from cdc.fn_cdc_get_net_changes_dbo_A(@start_lsn, @end_lsn, 'all');
select * from cdc.fn_cdc_get_all_changes_dbo_A(@start_lsn, @end_lsn, 'all');

Above statement will show you all records that are inserted, deleted or updated after enabling CDC in sql server.

You can disable you table A using below query

exec sys.sp_cdc_disable_table
    @source_schema = 'dbo',
    @source_name = 'A' ,
    @capture_instance = 'dbo_A'

And can disable database using SQL

EXEC sys.sp_cdc_disable_db