PK and Timestamp in same table?

[laurenq uantrell]

Is there any reason to have a row that is the PK/Identity and a row
that is datatype Timestamp in the same table?

Does this in any way help speeding up row updates?

Thanks,
lq

[monkeybrains]

I think you mean columns :P

Timestamps should have nothing to do with speeding up updates (or any
query). They should also not be used in a PK/Identity combo. BOL has
a nice comment on that second notion:

"timestamp is used typically as a mechanism for version-stamping table
rows....."

"The value in the timestamp column is updated every time a row
containing a timestamp column is inserted or updated. This property
makes a timestamp column a poor candidate for keys, especially primary
keys. Any update made to the row changes the timestamp value, thereby
changing the key value. If the column is in a primary key, the old key
value is no longer valid, and foreign keys referencing the old value
are no longer valid. If the table is referenced in a dynamic cursor,
all updates change the position of the rows in the cursor. If the
column is in an index key, all updates to the data row also generate
updates of the index.
"

[laurenq uantrell]

Oops. Yes, I meant columns!

Thanks for that.

Smewhere I thought I read that having a TimeStamp column would speed up
UPDATE activities on rows.

[David Cartwright]

"laurenq uantrell" <la************ *@hotmail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Is there any reason to have a row that is the PK/Identity and a row
that is datatype Timestamp in the same table?
Does this in any way help speeding up row updates?

Can't see that adding a timestamp field would speed anything up - in fact
the presence of a field that is auto-populated means that, by definition,
every time you INSERT or UPDATE the row, it'll have to do some work writing
the current date and time to the table.

A timestamp is, of course, useful as a "last modified date/time" indicator
for your applications, though.

D.

[Trevor Best]

laurenq uantrell wrote:

Is there any reason to have a row that is the PK/Identity and a row
that is datatype Timestamp in the same table?

Does this in any way help speeding up row updates?

Thanks,
lq

Timestamp is useful for checking if a row has been updated by someone
else since you read it, so you don't need to check every other column
value and compare.

It's also a good idea if you use Access (and possibly other) front-end
if you have any floating point data types (even dates) as floating point
errors can cause the front end to think the row has been updated by
someone else even if it hasn't. (You've no doubt seen in CDMA,
timestamps recomended as cures for such problems).

[Dan Guzman]

> A timestamp is, of course, useful as a "last modified date/time" indicator

for your applications, though.
Laurenq referred to the timestamp *data type* in his post. The timestamp
data type is a misnomer because it is not related to date or time. The
system-generated timestamp is simply an 8 byte binary value that is
guaranteed to be unique within a database that is updated automatically
whenever any data in the row changes. Consequently, the primary purpose of
timestamp is for optimistic concurrency checks to see if the row was updated
by another user. For example:

UPDATE MyTable
SET
SomeColumn1 = @SomeValue1,
SomeColumn2 = @SomeValue2,
SomeColumn3 = @SomeValue3
WHERE
MyPK = @MyPK AND
MyTimestamp = @OriginalMyTime stamp

IF @@ROWCOUNT = 0
BEGIN
RAISERROR ('Data was updated or deleted by another user', 16, 1)
END

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David Cartwright" <ds**********@h otmail.com> wrote in message
news:di******** **@nwrdmz02.dmz .ncs.ea.ibs-infra.bt.com... "laurenq uantrell" <la************ *@hotmail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Is there any reason to have a row that is the PK/Identity and a row
that is datatype Timestamp in the same table?
Does this in any way help speeding up row updates?

Can't see that adding a timestamp field would speed anything up - in fact
the presence of a field that is auto-populated means that, by definition,
every time you INSERT or UPDATE the row, it'll have to do some work
writing the current date and time to the table.

A timestamp is, of course, useful as a "last modified date/time" indicator
for your applications, though.

D.

[--CELKO--]

The short answer is "No", it makes the table bigger and size will slow
down operations (probably not by much, but some).

The right answer is first, get the logical design right. An IDENTITY
cannot ever be a logical key, so is this thing actually a table at all?
What would the TIMESTAMP mean in your data model?

[Erland Sommarskog]

laurenq uantrell (la************ *@hotmail.com) writes:

Oops. Yes, I meant columns!

Thanks for that.

Smewhere I thought I read that having a TimeStamp column would speed up
UPDATE activities on rows.

As Trevor said - you don't have to check all columns to check for
concurrent updates, so the WHERE clauses of your updates are slightly
faster.

But as Celko pointed out, eight bytes more means bigger table, and degrades
performance.

I would say that timestamp is mainly a booster for development, as it
makes checks for concurrent updates easier to implement.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp