Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #828 > unrolled thread

about timestamp

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  about timestamp "Tony" <johansson.andersson@telia.com> - 2011-11-23 18:51 +0100
    Re: about timestamp Erland Sommarskog <esquel@sommarskog.se> - 2011-11-23 23:53 +0100

#828 — about timestamp

I read a book called "Beginning ASP.NET 3.5 in C# from Novice to 
Professional"
Here is the text and according to this it seems to be easy to create and use 
a timestamp but when I create one for a new table and add some record to 
this new table the timestamp is always empty.
So can ayone give a comment about how to use this timestamp for concurrency.


"This is about concurrency. Matching every field is an acceptable approach 
for small record, but it isn't the most efficient startegy if you have 
tables with huge amounts of data. In this situations, you have two possible 
solutions: you can match some of the fields (leaving out the ones with 
really big values) or you can add a timestamp field to your database table, 
and use that for concurrency checking.

Timestamp are special fields that the database uses to keep track of the 
state of a record.
Whenever any change is made to a record, the database engine updates the 
timestamp field,
giving it a new, automatically generated value. the purpose of a timestamp 
field is to make strict concurrency checking easier.
When you attempt to perform an update to a table that includes a timestamp 
field you use a Where clause
that matches the appropriate unique ID value(like ProductID) and the 
timestamp field.

UpdateCommand= "Update Products set ProductName=@ProductName, 
UnitPrice=@UnitPrice,
                            UnitsInStock=@UnitsInStock, 
UnitsOnOrder=@UnitsOnOrder,
                            ReorderLevel=@ReorderLevel, 
Discontinued=@Discontinued
                            where ProductID=@ProductID and 
RowTimestamp=@RowTimestamp"

The database engine uses the ProductID to look up the matching record.
Then, it attempts to match the timestamp in order to update the record. If 
the timestamp matches you
know the record hasn't been changed. The actual value of the timestamp isn't 
important, because that's controlled by the database.
You just need to know whether it's changed. Creating a timestamp is easy. In 
SQL Server you create a timestamp field using the timestamp data type."

//Tony

[toc] | [next] | [standalone]

#830

Tony (johansson.andersson@telia.com) writes:
> I read a book called "Beginning ASP.NET 3.5 in C# from Novice to 
> Professional" Here is the text and according to this it seems to be easy
> to create and use a timestamp but when I create one for a new table and
> add some record to this new table the timestamp is always empty. 

What you mean? A timestamp column is never empty. Here is a quick example:

CREATE TABLE ts (b int NULL, ts timestamp NULL)
go
INSERT ts (b) VALUES (NULL)
go
SELECT * FROM ts
go
UPDATE ts SET b = 98 
go
SELECT * FROM ts
go
DROP TABLE ts

timestamp columns are indeed very smooth to implement optimisitc 
concurrency.

The only problem with the type is the name. Not only is it confusing, but in 
ANSI SQL "timestamp" is the name for the type we know as "datetime". 

Microsoft has officially deprecated the name "timestamp" and recommend
using "rowversion" instead. Only problem is that they abide to their
own recommendations...


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web