Ttdfjt

How to forge a multi-part weapon?

How can "научись" mean "take it and keep trying"?

Does Disney no longer produce hand-drawn cartoon films?

Find the limit of a multiplying term function when n tends to infinity.

Share calendar details request from manager's manager

How can electric fields be used to detect cracks in metals?

SQL counting distinct over partition

How does an ordinary object become radioactive?

Winning Strategy for the Magician and his Apprentice

Do simulator games use a realistic trajectory to get into orbit?

Pre-1972 sci-fi short story or novel: alien(?) tunnel where people try new moves and get destroyed if they're not the correct ones

Should I avoid hard-packed crusher dust trails with my hybrid?

Overlapping String-Blocks

How can I tell the difference between unmarked sugar and stevia?

What is the `some` keyword in SwiftUI?

What is the actual quality of machine translations?

Soft question: Examples where lack of mathematical rigour cause security breaches?

How come the nude protesters were not arrested?

1980s live-action movie where individually-coloured nations on clouds fight

Motivation - or how can I get myself to do the work I know I need to?

What makes an item an artifact?

Impedance ratio vs. SWR

Mobile App Appraisal

How to construct an hbox with negative height?

Why is dropping a primary key expensive? [duplicate]

What happens when you delete a Clustered index and Non Clustered Index once createdImplications of using a Unique NonClustered Index with Covering Columns instead of a Primary KeyWhy are timestamps not always increasing with concurrent inserts?Should I mark a composite index as unique if it contains the primary key?How to split a wide table in Production?Speeding up insert in SQL ServerWhat can be the downside of always having a single integer column as primary key?SQL Server: Clustered index, sorting and paginationChanging the datatype of the primary key columns of partitioned tableswhy is update from a join containing zero records taking a minute and 20 seconds?What are the reasons to create a key-less, index-less database?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

3

This question already has an answer here:

• 
  What happens when you delete a Clustered index and Non Clustered Index once created
  
  1 answer
  
  

I have a table with a clustered PK. No foreign keys reference it. When I drop it I expect it to be more or less instantaneous, but it is instead very slow.

My assumption was that we'd just be removing some metadata and not having a constraint or clustering for future updates and inserts.

What is actually happening internally to make this expensive?

sql-server

share|improve this question

edited May 30 at 0:36

Karl Kieninger

asked May 30 at 0:18

Karl KieningerKarl Kieninger

1937

marked as duplicate by mustaccio, Paul White♦ sql-server
Users with the  sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
May 30 at 9:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I admit the answer and the underlying issues are the same in this as in the referenced question. However, I somewhat object to the duplicate flag. The words primary key do not appear in the other question. The presence and importance of the nonclustered index is only revealed the discussion and answer to this one. I searched before posting and did not wind up with at the other answer. I think there is value in this as a standalone question even if the answer is the same.
  
  – Karl Kieninger
  May 30 at 15:44
  

  
  
  
  

add a comment | 

3

This question already has an answer here:

• 
  What happens when you delete a Clustered index and Non Clustered Index once created
  
  1 answer
  
  

I have a table with a clustered PK. No foreign keys reference it. When I drop it I expect it to be more or less instantaneous, but it is instead very slow.

My assumption was that we'd just be removing some metadata and not having a constraint or clustering for future updates and inserts.

What is actually happening internally to make this expensive?

sql-server

share|improve this question

edited May 30 at 0:36

Karl Kieninger

asked May 30 at 0:18

Karl KieningerKarl Kieninger

1937

marked as duplicate by mustaccio, Paul White♦ sql-server
Users with the  sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
May 30 at 9:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I admit the answer and the underlying issues are the same in this as in the referenced question. However, I somewhat object to the duplicate flag. The words primary key do not appear in the other question. The presence and importance of the nonclustered index is only revealed the discussion and answer to this one. I searched before posting and did not wind up with at the other answer. I think there is value in this as a standalone question even if the answer is the same.
  
  – Karl Kieninger
  May 30 at 15:44
  

  
  
  
  

add a comment | 

This question already has an answer here:

• 
  What happens when you delete a Clustered index and Non Clustered Index once created
  
  1 answer
  
  

I have a table with a clustered PK. No foreign keys reference it. When I drop it I expect it to be more or less instantaneous, but it is instead very slow.

My assumption was that we'd just be removing some metadata and not having a constraint or clustering for future updates and inserts.

What is actually happening internally to make this expensive?

sql-server

share|improve this question

edited May 30 at 0:36

Karl Kieninger

asked May 30 at 0:18

Karl KieningerKarl Kieninger

1937

share|improve this question

edited May 30 at 0:36

Karl Kieninger

asked May 30 at 0:18

Karl KieningerKarl Kieninger

1937

share|improve this question

edited May 30 at 0:36

Karl Kieninger

asked May 30 at 0:18

Karl KieningerKarl Kieninger

1937

asked May 30 at 0:18

Karl KieningerKarl Kieninger

1937

asked May 30 at 0:18

Karl KieningerKarl Kieninger

1937

1 Answer
1

active

oldest

votes

11

One of the reasons dropping the PK constraint, and the underlying clustered index, is expensive is that the underlying data is transformed into a HEAP. This affects all of the non-clustered indexes which have to update the pointers back to the row data from the old clustered index key to the new HEAP row. From Docs:

The pointer from an index row in a nonclustered index to a data row is
called a row locator. The structure of the row locator depends on
whether the data pages are stored in a heap or a clustered table. For
a heap, a row locator is a pointer to the row. For a clustered table,
the row locator is the clustered index key.

So once you drop the PK and clustered index, the non-clustered indexes have their row locators updated from the clustered index key to the heap pointer. Depending on the size and number of indexes, there is potentially a substantial cost involved in this update.

This is also explained in this article:

Dropping a clustered index can take time because in addition to
dropping the clustered index, all nonclustered indexes on the table
must be rebuilt to replace the clustered index keys with row pointers
to the heap.

share|improve this answer

answered May 30 at 0:58

HandyDHandyD

2,8161320

add a comment | 

11

One of the reasons dropping the PK constraint, and the underlying clustered index, is expensive is that the underlying data is transformed into a HEAP. This affects all of the non-clustered indexes which have to update the pointers back to the row data from the old clustered index key to the new HEAP row. From Docs:

The pointer from an index row in a nonclustered index to a data row is
called a row locator. The structure of the row locator depends on
whether the data pages are stored in a heap or a clustered table. For
a heap, a row locator is a pointer to the row. For a clustered table,
the row locator is the clustered index key.

So once you drop the PK and clustered index, the non-clustered indexes have their row locators updated from the clustered index key to the heap pointer. Depending on the size and number of indexes, there is potentially a substantial cost involved in this update.

This is also explained in this article:

Dropping a clustered index can take time because in addition to
dropping the clustered index, all nonclustered indexes on the table
must be rebuilt to replace the clustered index keys with row pointers
to the heap.

share|improve this answer

answered May 30 at 0:58

HandyDHandyD

2,8161320

add a comment | 

11

One of the reasons dropping the PK constraint, and the underlying clustered index, is expensive is that the underlying data is transformed into a HEAP. This affects all of the non-clustered indexes which have to update the pointers back to the row data from the old clustered index key to the new HEAP row. From Docs:

The pointer from an index row in a nonclustered index to a data row is
called a row locator. The structure of the row locator depends on
whether the data pages are stored in a heap or a clustered table. For
a heap, a row locator is a pointer to the row. For a clustered table,
the row locator is the clustered index key.

So once you drop the PK and clustered index, the non-clustered indexes have their row locators updated from the clustered index key to the heap pointer. Depending on the size and number of indexes, there is potentially a substantial cost involved in this update.

This is also explained in this article:

Dropping a clustered index can take time because in addition to
dropping the clustered index, all nonclustered indexes on the table
must be rebuilt to replace the clustered index keys with row pointers
to the heap.

share|improve this answer

answered May 30 at 0:58

HandyDHandyD

2,8161320

add a comment | 

One of the reasons dropping the PK constraint, and the underlying clustered index, is expensive is that the underlying data is transformed into a HEAP. This affects all of the non-clustered indexes which have to update the pointers back to the row data from the old clustered index key to the new HEAP row. From Docs:

The pointer from an index row in a nonclustered index to a data row is
called a row locator. The structure of the row locator depends on
whether the data pages are stored in a heap or a clustered table. For
a heap, a row locator is a pointer to the row. For a clustered table,
the row locator is the clustered index key.

So once you drop the PK and clustered index, the non-clustered indexes have their row locators updated from the clustered index key to the heap pointer. Depending on the size and number of indexes, there is potentially a substantial cost involved in this update.

This is also explained in this article:

Dropping a clustered index can take time because in addition to
dropping the clustered index, all nonclustered indexes on the table
must be rebuilt to replace the clustered index keys with row pointers
to the heap.

share|improve this answer

answered May 30 at 0:58

HandyDHandyD

2,8161320

share|improve this answer

answered May 30 at 0:58

HandyDHandyD

2,8161320

answered May 30 at 0:58

HandyDHandyD

2,8161320

answered May 30 at 0:58

HandyDHandyD

2,8161320