1

I am inserting 50 million records to an existing table with a primary key , and a non clustered key. My question is whether indexing of those will existing indexes will happen along with the insert operation or not.? Or do i have to do an index rebuild/reorganize after insert to keep index updated.

My assumptions is when new records comes in data pages will get updated and so will index pages also.

May be this is a silly question,but i have to know this for some other activities for like bulk insert/update i perform in my tables.

2
  • The non-clustered index will be built/populated in real-time as inserts into your table are happening. It will most likely be faster to drop everything including the PK and perform the insert with the TABLOCK hint and then rebuilding after the fact.
    – Queue Mann
    Jul 16, 2019 at 14:27
  • 1
    You should check data loading performance guide. This addresses a lot of performance optimization you can do e.g Enabling TF610, Using BCP OUT/IN, SSIS etc. You just have to follow the recommendations and test it out in your environment. Also, make sure that the autogrowth is enabled and the db is pregrown to expected size to minimize the autogrowth events.
    – Kin Shah
    Jul 16, 2019 at 14:28

1 Answer 1

1

Everything will be populated on insert, it is also one of the reasons why having too many indexes is considered bad practice on tables that have a lot of CRUD operations.

In your case, you only have a CLUSTERED and a NON CLUSTERED index (SQL Server creates a CLUSTERED index for a PK by default even if you don't specify it). Non clustered indexes have something called a "clustering key" which is basically a pointer to the clustered index.

A clustering key consists of the values of the clustered index keys from the row being pointed at and the Uniquifier (if present). With every insert, the clustered key has to sort the values in memory, and in turn each one of those pointers have to move. Also keep in mind that clustered indexes in SQL Server immediately make the table a B-Tree structure (unless it's a column store index), and this is one of the biggest causes for fragmentation. As the rules of the B-Tree data structure could force pages to split and therefor change the entire structure of the B-Tree (it's like a cascading effect).

In order to avoid some fragmentation on insert, you should sort the data with a query before doing so. This post explains how it works and why.

Fun fact, a nonclustered index will point at something called an RID (Row Identifier) when no clustered index is present. This is then called a heap table.

1
  • 1
    "clustered indexes in SQL Server immediately make the table a B-Tree structure" unless its a clustered column store index. Jul 20, 2019 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.