SQL Server 2008 timestamp data type

Question

At present I have a column with datatype datetime with default constraint. Now I want to alter as a data type as time stamp.

alter table tblname
alter column date_modified timestamp not null

My original requirement is whenever I update a record the column called date_modified should update with recent time .

This is same functionality which is working in MYSQL with datatype TIMESTAMP and default value CURRENT_TIMESTAMP

How can I perform in this in SQL Server 2008??

Answer 1

Did you check what the TIMESTAMP type actually is in SQL Server? It's different from the TIMESTAMP type in mysql. The documentation says:

Is a data type that exposes automatically generated, unique binary numbers within a database

So there's no way to set it to "the current time"

Answer 2

Timestamp in MySQL and timestamp in SQL Server is not the same thing. Keep your datetimeand add an after update trigger that updates date_modified with getdate().