Shouldn't there be an index rebuild as well ??

Not sure if the website id is an identity column, if it is then create a clustered index on that

for the common ones create a non-clustered index and don't include...

In this case there was only a HOBT Lock by a session with update query and RID by a session with Insert query. Both with difference partition key values

So easily...

Yes, but in this scenario (Partitioning) I see the locks are not escalated even after crossing the threshold limit of escalation.

Below is the excerpt from MSDN:

Lock Escalation Thresholds

Lock escalation is...

Are you scheduling this operation with Sql Agent Job, if yes then there may not be a direct solution for you however you can use some 3rd party Sql Monitors...

Also from the deadlock graph, I can see that the Insert was requesting a Page Level Lock while the updates acquired HOBT lock.

Also, my doubt is during Select and Updates...

The lock escalation is set to Auto, even when I check the properties of Table and Index, it shows partitioned with the proper partition Scheme

Hello Experts,

One unanswered question from my old post

Thank you , I got your point

Let me try to simplify my question.

Lets assume I am not using any transactions, and there are two cases i.e. for Delete and Insert

Case 1. Delete.

If I am deleting 1...

I was planning to use the temp table as Destination.

But to preserve the metadata while mapping I was thinking of using this expression in SqlCommand Data access mode of OleDB...

hmm,

Looks like I have to use hardcodes table names in sql command in OleDB Destination

I did below workaround for relative path issue:

BIDS 2008 doesn't work on relative path,may be some kind of bug

If you try to enter the relative path , on clicking next...

The older technology, the nonclustered columnstore, was designed to work on heaps and standard clustered indexes in combination with existing indexes. But, functionally, it's similar to the standard clustered...

here is the output of updating column suffix for Person.Person table in adventureworks2008 database

I have modified the index to include Suffix column as shown below

CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]...