Sample Header Ad - 728x90

Restructuring table - start with PK or not?

0 votes
1 answer
94 views
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.
Asked by Oded (334 rep)
Oct 18, 2019, 08:20 AM
Last activity: Oct 24, 2021, 08:30 AM