What is timestamp data type

What is the difference between datetime and timestamp data types ?

First of all, let me tell you that, there no similarity between timestamp and datetime data types. timestamp has nothing to do with calender date or time.

Then what is timestamp data type ?

timestamp is a custom data type defined as varbinary(8).
The value of the timestamp is maintained on server level, and current value of the timestamp is stored in a global variable, @@DBTS.

To see the current value of timestamp ..

select @DBTS
go

Observe the value returned, and you will agree that it has nothing to do with date/time.

So where should I use the timestamp datatype ?

If defined in a table, it can be used as a flagging column for each row in a table. Whenever, any of the column in a row gets updated the value of the timestamp column gets automatically updated by the current value of @@DBTG.

So in multi-client environment, say you are updating a row, then you can check the old and new timestamp value for the row in subject.
If the old and new value matches that means there is no other updates to the row while you were accessing the row for manipulation, so you are fine to go and do update.
Else, if the old and new value does not match, that means, there has been some kind of update on the row, when you were browsing it, so depending on the requirements, you can develop the further logic for this condition.

In brief, it is used to prevent an update on a row that has been modified during the operation.

You need to use tsequal() function is used to compare these timestamp values.

It's just name ( timestamp ) which is pretty easily confusing most of us ;-).

P.S. - You can have only one column of datatype timestamp in a table.