SQL Server: timestamp clustering; pros and cons

Question

I have a table on SQL Server where I want inserts to be added to the end of the table (as opposed to a clustering key that would force them to be inserted in the middle). This means that I want the table clustered by some column to grow constantly.

This can be achieved by clustering in a column datetime:

CREATE TABLE Things (
    ...
    CreatedDate datetime DEFAULT getdate(),
    [timestamp] timestamp,        

    CONSTRAINT [IX_Things] UNIQUE CLUSTERED (CreatedDate)
)

But I can not guarantee that the two Thingswill not have the same time. Therefore, my requirements cannot really be achieved using the datetime column.

I could add an identity column and a cluster on it: int

CREATE TABLE Things (
    ...
    RowID int IDENTITY(1,1),
    [timestamp] timestamp,        

    CONSTRAINT [IX_Things] UNIQUE CLUSTERED (RowID)
)

, timestamp; , . , -.

, rowversion (aka timestamp):

CREATE TABLE Things (
    ...
    [timestamp] timestamp,        

    CONSTRAINT [IX_Things] UNIQUE CLUSTERED (timestamp)
)

, identity int (RowID) , , .

, , - , ; .

: , .

Answer 1

, rowversion (aka timestamp): int column (RowID) , , .

, , . ?

(. Kim Tripp ):

• - ,

rowversion , , . - , .

, , , . - datetime, , SQL Server "" - 3.33 - , , rowversion...

Answer 2

: timestamp :

. Microsoft SQL . , .

rowversion SELECT INTO rowversion SELECT. rowversion .

, alwsys ? .

Answer 3

. DateTime, , CLUSTERED, .

CREATE TABLE Things (
    ...
    CreatedDate datetime DEFAULT getdate(),
    [timestamp] timestamp,        
)

CREATE CLUSTERED INDEX [IX_CreatedDate] ON .[Things] 
(
    [CreatedDate] ASC
)

Answer 4

If there are many inserts in this table, you can create a hot spot that interferes with the update, because all inserts will occur on the same physical / index pages. Check the lock setting.