We are taking legacy flat txt files and inserting them into stage tables with SSIS.The question arose whether table should have primary clustered key index or not. This is direct flat file import with no transformation.
create table dbo.CustomerTransaction ( CustomerName varchar(255), PurchaseLocation varchar(255), Productid int, AmountSold float, CustomerAddress varchar(50) ) create table dbo.CustomerTransaction ( -- discussion for adding this column CustomerTransactionId int primary key clustered identity(1,1) CustomerName varchar(255), PurchaseLocation varchar(255), Productid int, AmountSold float, CustomerAddress varchar(50) ) -- both tables have nonclustered indexes create nonclustered index idx_ProductId on dbo.CustomerTransaction(ProductId) create nonclustered index idx_CustomerAddress on dbo.CustomerTransaction(CustomerAddress) -- Actually have more indexes, tables above are just for sample
1) Before ETL, the staging tables are truncated. There are No Deletes and No Updates. Only Inserts.
truncate table dbo.[CustomerTransaction]
2) Then disable all indexes before ETL.
alter index all on dbo.[CustomerTransaction] DISABLE
3) We conduct SSIS data flow with default fast load, which I read is equivalent to bulk insert. No transformations occur here.
4) Then reenable all indexes after import is done.
alter index all on dbo.[CustomerTransaction] REBUILD
5) The staging tables are then selected on join and where clauses, and placed into datawarehouse. This we why we have nonclustered indexes. After data warehouse is loaded, we truncate the staging tables.
We are hearing information that ETL Stage tables are good as heaps. However, also learning of fragmentation and performance issues with heaps. Reading all the articles below
I am reading conflicting opinions. One says Binary tree clustered are maintenance headaches for import ETL. Other says Heaps have performance issues with fragmentation. Our performance testing does not show much difference, but our data may change later. So we need to make a good design decision.
We know a good reason to have identity is for row labelling, however question is mostly about internals and performance.