Re: Identity key?

Vaughan Powell wrote:

> In SQL Server, specifically, this problem occurs only if the identity
> column is created as a clustered index.

Don't know if it's different in SQL Server vs. Sybase, but in Sybase, without a clustered index, the table acts as a heap and all inserts go to the last data page by default, causing a hot spot.

> It can be worthwhile creating
> the natural key (or some other search column) as the clustered index
> and the identity key as a non-clustered index - this prevents the above
> problem on INSERT but can slow down retrieval where joins are made via
> the primary key.

With that strategy, while the data rows are dispersed in the table, a single index page probably contains the entries for the surrogate keys currently being inserted, so the hot spot simply moves to the last leaf-level page of the non-clustered index.

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Wed Jan 10 2001 - 12:13:01 CST