Restructuring table – start with PK or not?

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…

Should I create the new table with the primary key (clustered index) or should I be creating 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.

These are hosted on AWS on db.t2.medium / db.m4.large instances. The data is copied with insert... select. Recovery model of the DB is FULL.

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 in size and if auto-growth is not set properly you’ll be waiting for it to auto-grow.