Sorting table separately while using UNION All, How to??

There are times when developer want top and bottom query of the UNION ALL resultset sorted independently, like this

select Columns from table1 order by Columns

union all

select Columns from tabbe2 order by Columns

However the above query will fail and give the output as

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'union'.

NOTE :It is not possible to have two different order by in the UNION statement because UNION returns single resultant.

However if your requirement is saying to sort top and bottom query independently then how to do?

Let’s create a scenario, by taking two tables TABLE1 and TABLE2

create table table1

(id int,

name varchar(15)

)

Command(s) completed successfully.

create table table2

(id int,

name varchar(15)

)

Command(s) completed successfully.

insert into table1(id,name)

select 1,'Ananya_Tab1'

union all

select 2,'Abhiroop_Tab1'

union all

select 3,'Gunjal_Tab1'

(3 row(s) affected)

insert into table2(id,name)

select 3,'Bikshapati_Tab2'

union all

select 2,'Sanjana_Tab2'

union all

select 1,'Akshit_Tab3'

(3 row(s) affected)

Now our tables are in place let’s do proceed to stimulate our requirement, we want like this  

select id,name, 'tab1' as odrCol from table1

order by id

union all

select id,name, 'tab2' as odr1 from table2

order by id

But firing the above script on ssms, gives error                          

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'union'.

And if we execute the select UNION without order by

select id,name from table1

union all

select id,name from table2

id          name

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

1           Ananya_Tab1

2           Abhiroop_Tab1

3           Gunjal_Tab1

3           Bikshapati_Tab2

2           Sanjana_Tab2

1           Akshit_Tab3

(6 row(s) affected)

However our requirement is like this

id          name          

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

1           Ananya_Tab1    

2           Abhiroop_Tab1  

3           Gunjal_Tab1    

1           Akshit_Tab3    

2           Sanjana_Tab2   

3           Bikshapati_Tab2

So to get the output as required, lets add a additional column ordCol and use it in order by clause

select id,name, 'tab1' as odrCol from table1

union all

select id,name, 'tab2' as odrCol from table2

order by odrCol,id

id          name            odrCol

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

1           Ananya_Tab1     tab1

2           Abhiroop_Tab1   tab1

3           Gunjal_Tab1     tab1

1           Akshit_Tab3     tab2

2           Sanjana_Tab2    tab2

3           Bikshapati_Tab2 tab2

(6 row(s) affected)

Now we can see we achieved our desired requirement of independent sorting of select query in UNION ALL.

Code exercises your creative instincts; do code n enjy J   

Post Reference: Vikram Aristocratic Elfin Share

Track changes on your table with OUTPUT Clause no need for trigger

SQL Server provides a simplified method of auditing changes using DML statements without having to use Triggers. With the invent of OUTPUT clause as a part of DML statement helps us in tracking changes made during any DML operations.

The OUTPUT clause can save the result-set in a table or table variable. Let stimulate the change tracking with OUTPUT clause

Let’s create a table testOutputClause with id and name as a column and insert few data.

create table testOutputClause

(id int identity(1,1),

name varchar(15))

insert into testOutputClause values('Purushottam')

insert into testOutputClause values('Vrishab')

insert into testOutputClause values('Palash')

select * from testOutputClause

id          name

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

1           Purushottam

2           Vrishab

3           Palash

(3 row(s) affected)

Now we are creating audit table tracker which will record all the changes that will be performed on testOutputClause table.

create table tracker

(tableName varchar(18),

changesDone varchar(100))   

Now we going to fire update statement on testOutputClause and at the same time we will be recording the changes using OUTPUT Clause.

update testOutputClause

set name = 'Ipsita'

output 'tracker','the name'+ deleted.name +'is changed to'+ inserted.name

into tracker

where id=1

(1 row(s) affected)

Lets see the result of update statement on testOutputClause, so records are now updated with name = Ipsita where the id was 1.

                                                             

select * from testOutputClause

id          name

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

1           Ipsita

2           Vrishab

3           Palash

(3 row(s) affected)

Now let’s see the result of tracker (the audit table).

select * from tracker

tableName          changesDone

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

tracker            the name Purushottam is changed to Ipsita

(1 row(s) affected)

We can see the update is tracked successfully in audit table.

NOTE: You can use SQL Server magic table i.e INERTED and DELETED with OUTPUT Clause and records the changes in DML.

Code until you loses yourself

 Post Reference: Vikram Aristocratic Elfin Share

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