How to track changes on your table without using triggers? Something rousing!!! Correct?

Most of the time we hear a best practice word “Don’t use trigger” , off course it is true, triggers are tightly coupled with DML statement (insert/update/delete) and add overhead to sql server in case of DML execution. But then without using trigger how to track the changes that has made to the data..

Yes there are ways like the OUTPUT clause, but there is one very intresting way to track the changes made to the data i.e. through change tracking.   

Change Tracking enables an application to keep track of all changes made by the user or the called stored procedure.

Let’s see how to enable Change Tracking on the database.  

alter database testingDB

set change_tracking = on

(change_retention = 2 days, auto_cleanup = on);

Command(s) completed successfully.

Once the change tracking is enabled, it will keep the change with the version number for the number of days defined in change_retention parameter. Like here above we have defined change_retention = 2 this means the changes will be there for 2 days.

Once the CT is turned on, on the database level it does not enables the CT of all the tables of the database. The CT must to be enabled on the table

Let’s see how to enable Change Tracking on the Table.  

alter table testTable

enable change_tracking with ( track_columns_updated = on)

Command(s) completed successfully.

The above code will enable CT to the testable table. Now onward whatever update performed on this table, CT will track all those changes

select * from testTable

id          name

----------- ------------

1           Akansha

2           Gunjal

3           Abhiroop

(3 row(s) affected)

Now we firing the update statement where we are updating the name of id =2 to ‘Jubi R’

update testTable

set name = 'Jubi R'

where id=2

(1 row(s) affected)

Table is updated with new data that the ‘Jubi R’ for id=2

select * from testTable

id          name

----------- ------------

1           Akansha

2           Jubi R

3           Abhiroop

(3 row(s) affected)

Now let’s find whether our change has been tracked by change tracker, by firing the below query.

SELECT pn.id

from changetable(changes testTable, 1) ct,testTable pn

where pn.id= ct.id

and SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'

id

-----------

2

(1 row(s) affected)

As you can see in the above, the CT is able to give you the id of the modified records in the testTable table.

Now lets modify the above query to include few more column

SELECT ct.sys_change_version, ct.id,pn.name,ct.sys_change_operation

from changetable(changes testTable, 1) ct,testTable pn

where pn.id= ct.id

and SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'

sys_change_version   id          name         sys_change_operation

-------------------- ----------- ------------ --------------------

3                    2           Jubi R       U

(1 row(s) affected

The SYS_CHANGE_OPERATION column provides the information what happened to this record “U” stats that it was modified.

Let do an insertion to the table and see the change track resultant…

insert into testTable values ('Silpa')

select * from testTable

id          name

----------- ------------

1           Akansha

2           Jubi R

3           Abhiroop

4           Silpa

(4 row(s) affected)

Now after insertion lets fire the Change Track query.

SELECT ct.sys_change_version, ct.id,pn.name,ct.sys_change_operation

from changetable(changes testTable, 1) ct,testTable pn

where pn.id= ct.id

and SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'

sys_change_version   id          name         sys_change_operation

-------------------- ----------- ------------ --------------------

3                    2           Jubi R       U

4                    4           Silpa        I

(2 row(s) affected)

 Now here from the output we can see, the version number is 4, the latest operation performed on table ‘testTable’ and the operation is Insert (I)  ie sys_change_operation =1 and the new values entered is Silpa.

Conclusion : we can track the changes of database using CR efficiently without using triggers.

Next Upcoming: Track the changes using output  clause.

Let everything go, when it comes to code  

Post Reference: Vikram Aristocratic Elfin Share

Cluster Index physically stores data… Myth? Whether true, let’s do the practical!!!

I came across many people saying cluster index physically sort the data in the table with the cluster key. Is it correct, let’s see doing a practical session… 

Lets create a table ClusterIndexOrderDemo with cluster index ie with primary key and with two column sno and fname.

create table ClusterIndexOrderDemo

(

      sno int not null primary key,

      fname varchar(10) not null

)

Command(s) completed successfully.

Now we will be inserting few rows… see the data for sno field, it is not in order.

insert into ClusterIndexOrderDemo

select 15, 'Vaidik' UNION ALL

select 25, 'Aakshat' UNION ALL

select 05, 'Dhanya'

Trace Flag 3604 forces the output to the client (in our case SSMS result pane) and DBCC Page gives you the page details

dbcc TraceOn(3604)

DBCC execution completed.

Now we will be firing DBCC IND – used to know the page details of tables & indexes.

Syntax :

 DBCC IND ( {dbname}, {table_name},{index_id} )Were,

·         dbname : your database;

·         table_name: your table name

·         index_id: for cluster index 1 , for non cluster index its 2

dbcc IND ('testDemo', 'ClusterIndexOrderDemo', 1)

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID

------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------

1       71          NULL   NULL        309576141   1           1               72057594039828480    In-row data          10       NULL       0           0           0           0

1       70          1      71          309576141   1           1               72057594039828480    In-row data          1        0          0           0           0           0

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You will be getting two rows, check for Page Type column where it is 1 see and note the corresponding page number and file number. Here in our case I am getting  page number as 70 and file id as 1.

Now I can run DBCC Page command. So whats the syntax ?

Dbcc page ({'dbname'|dbid}, filenum, pagenum [,printopt={0|1|2|3}]

Here in our case the database is testDemo, fileId is 1, page id is 70 and print option we are providing as 2 which means all page info dump.

DBCC PAGE ('testDemo',1,70,2)

Pick the offset table from output

OFFSET TABLE:

Row - Offset                        

2 (0x2) - 117 (0x75) (Akshat)               

1 (0x1) - 96 (0x60)  (Vaidik)               

0 (0x0) - 139 (0x8b) (Dhanya)  

Here the 117, 96, 139 are location where as the 2,1,0 are offset.            

Now we will be entering few more record and then will check the location and the offset.

insert into ClusterIndexOrderDemo

select 20, 'Radhika' UNION ALL

select 30, 'Silpa' UNION ALL

select 10, 'Sanjana'

dbcc page ('testDemo',1,70,2)

OFFSET TABLE:

Row - Offset                        

5 (0x5) - 182 (0xb6)  (Silpa)              

4 (0x4) - 117 (0x75)  (Akshat)              

3 (0x3) - 160 (0xa0)  (Radhika)              

2 (0x2) - 96 (0x60)   (Vaidik)              

1 (0x1) - 202 (0xca)  (Sanjana)              

0 (0x0) - 139 (0x8b)  (Dhanya)

Conclusion: So we can see here that the place where record got added that location is not changed at all. Instead based on the clustered index key it maintains the correct order within this Row Offset table.

So Clustered Index physically orders the data within a table? NO

Code award energy, No boost up drink required 

Post Reference: Vikram Aristocratic Elfin Share

Creating Primary key without cluster index!!!

Is there a way to create Primary Key without Cluster Index

We all know, when we create a column with primary key, SQL Server automatically create cluster index on primary key field. I have been frequently asked by my friends as well as interviewer; Is it possible to create primary key field without cluster index?

Yes it is quite possible to create primary key field without cluster index in it. Let’s see

1. First we are creating a simple table without any constraint and will see whether any index exist

create table testIndex

(id int,

name varchar(20))

Command(s) completed successfully.

select OBJECT_NAME(object_id) as [Table], name as [IndexName],type_desc as [IndexType] from sys.indexes

where object_id = (select object_id from sys.objects where name = 'testIndex')

Table              IndexName   IndexType

------------------------------------------

testIndex           NULL       HEAP

(1 row(s) affected)

As we can see there is no index present in the above table

2. Now let’s try to create a table tableIndex2 with primary key and then will check for index present in the table.

create table testIndex2

(id int primary key,

name varchar(20))

select OBJECT_NAME(object_id) as [Table], name as [IndexName],type_desc as [IndexType] from sys.indexes

where object_id = (select object_id from sys.objects where name = 'testIndex2')

Table        IndexName                        IndexType

---------------------------------------------------------

testIndex2   PK__testInde__3213E83F07F6335A   CLUSTERED

(1 row(s) affected)

3. Now we will be trying to create a primary key field without clustered index.

create table testIndex3

(id int primary key nonclustered,

name varchar(20))

select OBJECT_NAME(object_id) as [Table], name as [IndexName],type_desc as [IndexType] from sys.indexes

where object_id = (select object_id from sys.objects where name = 'testIndex3')

Table        IndexName                       IndexType

----------------------------------------------------------

testIndex3   NULL                            HEAP

testIndex3   PK__testInde__3213E83E0BC6C43E  NONCLUSTERED

(2 row(s) affected)

Conclusion: So from this example we can see, that it is quite possible to create table with Primary Key without cluster index.

Put out of your mind and do code- it’s very effortless 

Post Reference: Vikram Aristocratic Elfin Share

Intersect, Inner Join sounds similar…but technicallyyy, is it.. hmm??? let’s find; (Series-I Intersect n Innerjoin)

Intersect operator: it retrieves the common record among the tables, most of time you will see the result of intersect operation is same as inner join query, but we are going to explore where it differs from inner join.

Note: while using Intersect operator the number and order of column must be same in all query as well as the datatype of both right and left query of intersect operator must be compatible.

Lets take an example to see the difference between intersect and inner join.

Here we will be creating two table ServiceProviderContact and ServiceProviderAddress and populate with some dummy data..

create table ServiceProviderContact

(serviceProviderId int identity(1,1) primary key,

serverProviderName varchar(10),

serverProviderCompany varchar(10),

modifiedDate datetime)

create table ServiceProviderAddress

(serviceProviderAddressId int identity(1,1) primary key,

serviceProviderId int references ServiceProviderContact(serviceProviderId),

serviceProviderAddress varchar(15),

modifiedDate datetime)

Now our table is ready, lets populate it with some data..

insert into ServiceProviderContact

values('akshita','ABC Ltd',getdate())

insert into ServiceProviderContact

values('Riddhi','ABC Ltd',getdate())

insert into ServiceProviderContact0

values('Samprada','ABC Ltd',getdate())

insert into ServiceProviderAddress

values(1,'ADI-Paldi', getdate())

insert into ServiceProviderAddress

values(1,'ADI-Sahibaug', getdate())

insert into ServiceProviderAddress

values(2,'ADI-Navragpura', getdate())

select * from ServiceProviderContacts

serviceProviderId serverProviderName serverProviderCompany modifiedDate

----------------- ------------------ --------------------- -----------------------

1                 akshita            ABC Ltd               2012-12-22 12:15:30.843

2                 Riddhi             ABC Ltd               2012-12-22 12:15:30.860

3                 Samprada           ABC Ltd               2012-12-22 12:15:30.860

(3 row(s) affected)

select * from ServiceProviderAddress

serviceProviderAddressId serviceProviderId serviceProviderAddress modifiedDate

------------------------ ----------------- ---------------------- -----------------------

4                        1                 ADI-Paldi              2012-12-22 12:21:10.513

5                        1                 ADI-Sahibaug           2012-12-22 12:21:10.513

6                        2                 ADI-Navragpura         2012-12-22 12:21:10.590

(3 row(s) affected)

Now lets fire intersect operation

select serviceProviderId, convert(varchar(12),modifiedDate,106) from  ServiceProviderAddress

intersect

select serviceProviderId, convert(varchar(12),modifiedDate,106) from  ServiceProviderContact

serviceProviderId

----------------- ------------

1                 22 Dec 2012

2                 22 Dec 2012

(2 row(s) affected)

The output shows the records that are common in both tables. It show only two in the above example.

Now lets try using Inner Join

select c.serviceProviderId, convert(varchar(12),c.modifiedDate,106)

from  ServiceProviderAddress a,ServiceProviderContact c

where  c.serviceProviderId = a.serviceProviderId and convert(varchar(12),c.modifiedDate,106) = convert(varchar(12),a.modifiedDate,106)

serviceProviderId

----------------- ------------

1                 22 Dec 2012

1                 22 Dec 2012

2                 22 Dec 2012

(3 row(s) affected)

The result are different as compared to Intersect Operator, since inner join first perform cartisen product then filter the data according to join condition.

Conclusion : So what we found inner join is similar to interest except it result in duplicate data where as intersect give the common record among left and right table of intersect operator.

Next Article would be: Stimulate Intersect operator using Inner join

Play through code and enjoy…

Post Reference: Vikram Aristocratic Elfin Share