Return to Answer

I have a whole oltp database designed using identity columns for clustering + pk. It work pretty fast on insert/seeks but i've seen a few problems:

1. the index fill option is useless because the inserts happen only to the end of the index
2. more storage space. I have tables with tens of millions of records and 1 int takes up space by itself. Each table with an identity column for it's pk has to have another index for business seeks, so even more storage required.
3. scalability. This is the worst problem. Because every insert goes to the end of the index, each insert will stress only the end of the index (allocation, io for writes, etc). By using a business key as a clustering key you can distribute the inserts evenly on the index. That means that you just eliminated a big hotspot. You can easily use more files for an index, each file on a separate drive, each drive working separately.

I started changing my tables from an identity columns to natural keys (maybe separate for clustering & pk). It just feels better now.

I would suggest the following (at least for an oltp db):

1. use as a clustering key the right columns in the right order as to optimize the most frequent queries
   
2. use a pk the right columns that make sense for you table

If the clustered key is not simple and contains chars (char[], varchar, nvarchar), i think the answer is 'it depends'.

I keep the following principle: optimize for the most common query while minimizing the worst case scenario.

I almost forgot one example. I have some tables that reference themselves. If that table has an identity column for it's primary key, then inserting one row might require an update, and inserting more than one row at a time might be difficult if not impossible (it depends on the table design).

I have a whole oltp database designed using identity columns for clustering + pk. It work pretty fast on insert/seeks but i've seen a few problems:

1. the index fill option is useless because the inserts happen only to the end of the index
2. more storage space. I have tables with tens of millions of records and 1 int takes up space by itself. Each table with an identity column for it's pk has to have another index for business seeks, so even more storage required.
3. scalability. This is the worst problem. Because every insert goes to the end of the index, each insert will stress only the end of the index (allocation, io for writes, etc). By using a business key as a clustering key you can distribute the inserts evenly on the index. That means that you just eliminated a big hotspot. You can easily use more files for an index, each file on a separate drive, each drive working separately.

I started changing my tables from an identity columns to natural keys (maybe separate for clustering & pk). It just feels better now.

I would suggest the following (at least for an oltp db):

1. use as a clustering key the right columns in the right order as to optimize the most frequent queries
   
2. use a pk the right columns that make sense for you table

If the clustered key is not simple and contains chars (char[], varchar, nvarchar), i think the answer is 'it depends', you should analyse individually each case.

I keep the following principle: optimize for the most common query while minimizing the worst case scenario.

I almost forgot one example. I have some tables that reference themselves. If that table has an identity column for it's primary key, then inserting one row might require an update, and inserting more than one row at a time might be difficult if not impossible (it depends on the table design).

I have a whole oltp database designed using identity columns for clustering + pk. It work pretty fast on insert/seeks but i've seen a few problems:

1. the index fill option is useless because the inserts happen only to the end of the index
2. more storage space. I have tables with tens of millions of records and 1 int takes up space by itself. Each table with an identity column for it's pk has to have another index for business seeks, so even more storage required.
3. scalability. This is the worst problem. Because every insert goes to the end of the index, each insert will stress only the end of the index (allocation, io for writes, etc). By using a business key as a clustering key you can distribute the inserts evenly on the index. That means that you just eliminated a big hotspot. You can easily use more files for an index, each file on a separate drive, each drive working separately.

I started changing my tables from an identity columns to natural keys (maybe separate for clustering & pk). It just feels better now.

I would suggest the following (at least for an oltp db):

1. use as a clustering key the right columns in the right order as to optimize the most frequent queries
   
2. use a pk the right columns that make sense for you table

If the clustered key is not simple and contains chars (char[], varchar, nvarchar), i think the answer is 'it depends'.

I keep the following principle: optimize for the most common query while minimizing the worst case scenario.

I have a whole oltp database designed using identity columns for clustering + pk. It work pretty fast on insert/seeks but i've seen a few problems:

1. the index fill option is useless because the inserts happen only to the end of the index
2. more storage space. I have tables with tens of millions of records and 1 int takes up space by itself. Each table with an identity column for it's pk has to have another index for business seeks, so even more storage required.
3. scalability. This is the worst problem. Because every insert goes to the end of the index, each insert will stress only the end of the index (allocation, io for writes, etc). By using a business key as a clustering key you can distribute the inserts evenly on the index. That means that you just eliminated a big hotspot. You can easily use more files for an index, each file on a separate drive, each drive working separately.

I started changing my tables from an identity columns to natural keys (maybe separate for clustering & pk). It just feels better now.

I would suggest the following (at least for an oltp db):

1. use as a clustering key the right columns in the right order as to optimize the most frequent queries
   
2. use a pk the right columns that make sense for you table

If the clustered key is not simple and contains chars (char[], varchar, nvarchar), i think the answer is 'it depends'.

I keep the following principle: optimize for the most common query while minimizing the worst case scenario.

I almost forgot one example. I have some tables that reference themselves. If that table has an identity column for it's primary key, then inserting one row might require an update, and inserting more than one row at a time might be difficult if not impossible (it depends on the table design).

I have a whole database designed using identity columns for clustering + pk. It work pretty fast on insert/seeks but i've seen a few problems:

1. the index fill option is useless because the inserts happen only to the end of the index
2. more storage space. I have tables with tens of millions of records and 1 int takes up space by itself. Each table with an identity column for it's pk has to have another index for business seeks, so even more storage required.
3. scalability. This is the worst problem. Because every insert goes to the end of the index, each insert will stress only the end of the index (allocation, io for writes, etc). By using a business key as a clustering key you can distribute the inserts evenly on the index. That means that you just eliminated a big hotspot. You can easily use more files for an index, each file on a separate drive, each drive working separately.

I started changing my tables from an identity columns to natural keys (maybe separate for clustering & pk). It just feels better now.

I would suggest the following:

1. use as a clustering key the right columns in the right order as to optimize the most frequent queries
   
2. use a pk the right columns that make sense for you table

I have a whole oltp database designed using identity columns for clustering + pk. It work pretty fast on insert/seeks but i've seen a few problems:

1. the index fill option is useless because the inserts happen only to the end of the index
2. more storage space. I have tables with tens of millions of records and 1 int takes up space by itself. Each table with an identity column for it's pk has to have another index for business seeks, so even more storage required.
3. scalability. This is the worst problem. Because every insert goes to the end of the index, each insert will stress only the end of the index (allocation, io for writes, etc). By using a business key as a clustering key you can distribute the inserts evenly on the index. That means that you just eliminated a big hotspot. You can easily use more files for an index, each file on a separate drive, each drive working separately.

I started changing my tables from an identity columns to natural keys (maybe separate for clustering & pk). It just feels better now.

I would suggest the following (at least for an oltp db):

1. use as a clustering key the right columns in the right order as to optimize the most frequent queries
   
2. use a pk the right columns that make sense for you table

If the clustered key is not simple and contains chars (char[], varchar, nvarchar), i think the answer is 'it depends'.

I keep the following principle: optimize for the most common query while minimizing the worst case scenario.