Sorry for the late reply guys.

Paul, thanks for this idea. I'm trying your idea and will let you kow after testing.

Here is the result of SET STATISTICS IO ON:

Table '#AAMaxRunVersion____________________________________________________________________________________________________000000026E3A'. Scan count 3005, logical reads 6033, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,...

Not working. Well as you can see the execution plan, the tables within the subquery with alias "e" is taking around 60-70% of time as these are the huge tables....

I already checked the statistics last updated date and these shows as current date so no issues with old statistics at all. Let me know if anything can be done...

Is there a way to write this query more efficiently ?

Before posting this thread, i did checked the table and index health and all looks good. Fragmentation level is almost around 10% for every participating table.

The columns are same. BusinessDate first column of index, PName second, DName third....

Understand but how can we change any clustered index based on just one query. We have created that index based on various facts. This index has been created based on...

Nope. Just this one.

Here it is.

No working. Here is the table structure:

BusinessDate datetime no 8 no (n/a) (n/a) NULL

PName varchar no 30 no no no SQL_Latin1_General_CP1_CS_AS

DName varchar no 30 ...

Yeah I understand, but its not possible to change my requirement. One more thing, the clustered index on that table consists of four columns and my WHERE clause consists of...

Learned few things from this operation.

First, If the column to be altered is a part of Primary contraint, foreign key constraint or uniqu constraint, you need to drop them...

Its giving me error while altering the column after disabling the primary constraint.

Msg 1974, Level 16, State 3, Line 1

Cannot perform the specified operation on table 'temp' because its clustered...

Ok. in case of disable-enable condition, if we have primary key constraint, will that constaint work same way [once enabled after changing the column] as it work before disabling ?...