Physical files name won't change until you change them manually.

Hi Jeremy

This one was interesting to know and could very well be the reason for locking that happened on my table for 4-5 minutes and ultimately I had to kill...

thanks for reply. I am on enterprise:

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64)

Oct 25 2013 19:04:40

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build...

Thanks for this useful script Eric!

Jeff

replying to your questions:

1.This table is more like your OrderDetail table. There might be some updates daily but very few records (may be less than 50) get updated daily.

2.I have...

ok, so it is finally boiling down to call for a down time.

But I wonder how much pain it must be causing in production environments across world. Usage of varchar(max)...

ok, now the direction of problem changed totally after some findings. The clustered index was actually not rebuild by the maintenance job because table has a ntext column!!

And we...

Joie. I set the values to NULL. Earlier there was long text in that column. Now it has NULLs.So logically the size of table should reduce, but that's not happening...

thanks Jeff.The column , whose values I set to NULL is not part of any index. Infact the table just has one Primary Key Clustered index

and that is on...

Sorry, I felt i didn't present the question correctly there in another thread. So wanted to avoid the confusion in my original question.

I don't want to change the clustered index as that is on primary key. So another option is i make primary key non clustered and create a clustered index on...

This doesn't seem to be true. You can partition on a nonclustered column even when u already have data and a clustered index on other column.

Is there any issue with...

Thanks for your prompt replies. I'll have the impacts of these options.

I was partitioning a table. It has PK as clustered index but the column I want to partition on is in a non clustered index. Table has around 1 billion...

Is it a very uncommon scenario? I am not getting any responses?