I have a bunch of tables in SQL Server 2012.
These tables are fed by other tables. Every day there are routines that truncate (fully delete) them and run queries whose result are inserted into them. They are like static views, storing those processed data so that they are queried quicker.
And there are source tables, which receive millions of new records every day, these aren't deleted.
All these tables have a time when they get a lot of inserts, and in the rest of the day they are only read.
Even with these 'static view' tables, I think I can enhance overall performance. Is there any best practice to handle these behaviors?
For the first situation, I've run a few benchmarks and saw that the best performance comes from using nonclustered indexes on the most filtered field. I drop these indexes before truncating and recreate them after inserting. Dropping and recreating is faster than rebuilding, and inserts are faster too.
Should I keep PKs (without semantic meaning) and clustered index too? I suppose that with no index those inserts will be even faster, but will I have any issue if these tables have no PK? They are never used explicitly.
I ask this because when I create the table and set its PK with
idBenchmark int NOT NULL IDENTITY (1,1) PRIMARY KEY
clustered index is created automatically, with a random name, and therefore I can't drop and recreate it.