0

I am restructuring a bunch of tables in my database (in this case using a smaller data type for some columns - in most cases cutting those by ~50%, and they tend to be large columns).

I've gone through the process of creating a new table with the wanted structure, copying all the data over, dropping/renaming old table and then applying all constraints, indexes, reseeding etc...

My question is - should I create the new table with the primary key (clustered index) or should I be createing it without?

My understanding is that without means faster copying at the cost of restructuring later on, but with means no restructuring later on.

Considering that the data source should already be sorted by the same PK (the PK doesn't usually change type on these tables), what's the best option?

Data sizes vary between the different tables - some have a few million rows, most have far less.

  • What is your db recovery model? How do you copy data, with insert..select? – sepupic Oct 18 at 8:38
  • Not entirely sure - these are hosted on AWS on db.t2.medium / db.m4.large instances. I see backup snapshots there. The data is copied with insert... select. – Oded Oct 18 at 8:45
  • Looking at AWS documents suggests these are full backups. – Oded Oct 18 at 9:14
  • Full backups has nothing to do with recovery models, they can be taken in any recovery model.Recovery model can be checked in sys.databases:select recovery_model_desc from sys.databases where name = 'your_db' – sepupic Oct 18 at 9:26
  • Recovery model of the DB is FULL. – Oded Oct 18 at 9:32
1

The fastest mode in your case is to use

INSERT INTO..WITH(TABLOCK)
SELECT..
ORDER BY PK_KEYS

With the new table having the same PK defined on it.

This way you don't sort data even if insert in clustered table, and it will be minimally logged in simple and bulk logged recovery models.

Note this will work only if your PK is not identity where you try to preserve the old values by using set identity_insert on, when the rows will be sorted anyway.

In any case you can create your new table and ask server for estimated query plan when trying to insert in it. If you see there is no sort this will be the fastest way.

When your PK is on identity you'll get the sort in the plan and you may use select..cast(), cast(),...into without creating your new table and you still will have minimal logging, but then you create your clustered PK and have sort. Note in this case or you want to use sort_in_tempdb option otherwise your db may increase it size and if autogrouth is not set properly you'll be wait for it autogrow.

  • That caveat is important... as most of these tables do have PKs that are identity fields that require preserving (and reseeding) – Oded Oct 18 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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