I need to copy Between 2 and 3 billion rows from a heap table with no identity to a new partioned table. The primary key is made up of a smallint value that represents a Month and BIGINT a loan_number.
--The partion key is a small int value.
--I have tried the approach of selecting by the partition key, but as you can imagine because of the amount of data each select can take quite some time.
--I also tried using an import\export task it started moving some data, but it is also extremly slow.
--The new table has 24 partitions. each partition represents 24 values of the partition key from 0 - 552
Here is the definition of the partitioned table. The heap table is the same except it does not have the identity column created_by\date attributes.
CREATE TABLE dbo.qc_partition
qc_partition_id BIGINT IDENTITY(1,1)
, pool_id CHAR(3) NOT NULL
, deal_no CHAR(5) NOT NULL
, group_no CHAR(3) NOT NULL
, servicer VARCHAR(4) NULL
, internal_loan_id CHAR(6) NOT NULL
, external_loan_id VARCHAR(18) NOT NULL
, last_int_p SMALLDATETIME NULL
, balance MONEY NULL
, int_rate NUMERIC(6, 3) NULL
, total_payment_due MONEY NULL
, sched_principal MONEY NULL
, sched_mnth_p MONEY NULL
, mba_stat VARCHAR(1) NULL
, ots_stat VARCHAR(1) NULL
, payment_hist VARCHAR(12) NULL
, exception VARCHAR(1) NULL
, start_date SMALLDATETIME NULL
, end_date SMALLDATETIME NULL
, fc_end_typ VARCHAR(1) NULL
, payoff_d SMALLDATETIME NULL
, payoff_r VARCHAR(1) NULL
, sell_date SMALLDATETIME NULL
, inv_bal MONEY NULL
, next_percent NUMERIC(6, 3) NULL
, loss_val MONEY NULL
, net_rate NUMERIC(7, 4) NULL
, period SMALLINT NOT NULL
, file_name CHAR(8) NOT NULL
, created_by VARCHAR(70) NOT NULL DEFAULT CURRENT_USER
, created_date DATETIME NOT NULL DEFAULT (GETDATE())
) ON partition_scheme_qc (period)
One more question about the indexing. I created the following index, but was wondering if anyone had better idea on that. Also, What would be the best way to create a clustered-index\primary key here.
CREATE NONCLUSTERED INDEX IX_period_external_loan_id
ON qc_partition (period, external_loan_id)
ON partition_scheme_qc (period)
Does anyone have any ideas?
Thank you for your help