Monday, September 10, 2007

timestamp Data type makes my big mistake

Oh…timestamp Data type makes my big mistake
Few days a go I was added post about timestamp data type but unfortunately it was not the correct way. And the name timestamp data type is a little misleading because timestamp data type has nothing to do with date and time and this is not possible converted to date time data type. We have to notice this, The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL date time data type. (SQL Server Books Online)

SQL Server Books Online Say’s:

  • timestamp

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. Each time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the timestamp values had no relationship to time.

In SQL Server version 7.0 and SQL Server 2000, @@DBTS is only incremented for use in timestamp columns. If a table contains a timestamp column, every time a row is modified by an INSERT, UPDATE, or DELETE statement, the timestamp value in the row is set to the current @@DBTS value, and then @@DBTS is incremented by one.

Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to automatically update the values when any modification takes place.

But I have little doubts about CAST or CONVERT function chart its shows as timestamp allow converting (Implicit conversions) to date time.

No comments: