My two questions are:

  • Can One use clustered indexes to hurry up bulk card inserts in large tables?
  • Can One then still effectively use foreign key associations if my IDENTITY column isn't the clustered index any longer?

To elaborate, I've got a database with a few very large (between 100-1000 mln rows) tables that contains company data. Typically there's data about 20-40 companies in this table, each his or her own "chunk" marked by "CompanyIdentifier" (INT). Also, every company has about 20 departments, each using their own "subchunk" marked by "DepartmentIdentifier" (INT).

It frequently happens that the whole "chunk" or "subchunk" is added or taken off the table. My first thought ended up being to use Table Partitioning on individuals portions, consider I'm using SQL Server 2008 Standard Edition I'm not titled into it. Still, most queries I've are performed on the "chunk" or "subchunk" instead of up for grabs in general.

I've been trying to optimize these tables for an additional functions:

  1. Queries which are operate on subchunks
  2. "Benchmarking" queries which are run up for grabs in general
  3. Placing/getting rid of large portions of information.

For 1) and a pair of) I've not experienced lots of problems. I've produced several indexes on key fields (also that contains CompanyIdentifier and DepartmentIdentifier where helpful) and also the queries are running fine.

However for 3) I've battled to locate a good solution. My first strategy ended up being to always disable indexes, bulk place a large chunk and rebuild indexes. It was extremely fast at first, however that you will find many organisations within the database, it requires a really very long time to rebuild the index every time.

Right now my strategy has transformed to simply departing the index on while placing, because this appears to become faster now. But I wish to optimize the place speed even more.

I appear to possess observed that with the addition of a clustered index defined on CompanyIdentifier + DepartmentIdentifier, the loading of recent "portions" in to the table is faster. Before I'd abandoned this tactic towards adding a clustered index with an IDENTITY column, as several articles stated in my experience the clustered index is contained in most other indexes so the clustered index ought to be no more than possible. But now i'm considering refreshing this old technique to accelerate the card inserts. My question, would this be smart, or am i going to suffer performance hits in the areas? And can this really accelerate my card inserts or perhaps is that simply my imagination?

I'm also unsure whether during my situation a name column is actually needed. I must have the ability to establish foreign key associations along with other tables, but could I additionally use something similar to a CompanyIdentifier+DepartmentIdentifier+[uniquifier] plan for your? Or does it need to be considered a table-wide, fragmented IDENTITY number?

Thanks for just about any suggestions or explanations.

Well, I have place it towards the test, and placing a clustered index around the two "chunk-determining" posts boosts the performance of my table.

Placing a chunk has become relatively fast in comparison towards the situation where I'd a clustered IDENTITY key, contributing to as quickly as when I didn't have clustered index. Removing a chunk is faster than without or with clustered index.

I believe the truth that all of the records I wish to remove or place are certain to be altogether on the certain area of the harddisk helps make the tables faster - it might appear logical in my experience.

Update: Following a year of expertise with this particular design I'm able to state that with this method of work, it's important to schedule regular repairing of all of the indexes (we all do it once per week). Otherwise, the indexes become fragmented soon and gratifaction is lost. Nonetheless, we're inside a procedure for migration to a different database design with partitioned tables, that is essentially better in each and every way - aside from the Enterprise Server license cost, but we have already ignored it right now. A minimum of I've.