March 24, 2008 at 3:37 am
Is Timestamp value is unique in sql server?
March 24, 2008 at 4:51 am
- within a table timestamp is unique and is modified every time a row is modified.
- if you want global unique, use uniqueidentifier (generated)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 24, 2008 at 8:48 am
My recollection is that a timestamp is unique within a database to a transaction. Thus multiple rows within a table (and the database) can have the same timestamp, but only if they were last written by the same transaction.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2008 at 2:00 pm
Timestamp values are unique within a database
Books Online
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2008 at 5:46 am
Proof is always good... gotta love BOL. 😉
--Jeff Moden
March 25, 2008 at 5:50 am
Jeff Moden (3/25/2008)
Proof is always good... gotta love BOL. 😉
Habit from academic writing. Cite it or prove it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2008 at 9:10 am
Heh... I'm the same way... Wise man once told me that "All claims require proof... extraordinary claims require extraordinary proof."
In the Navy, we said (cleaned up version) "Why sift through the manure to see what the horse thought... just ask the horse." 😛
--Jeff Moden
March 25, 2008 at 9:38 am
Good old BOL did it again, thanks for the rectification.
Here's a little test script I've found back from my test (sql7)
Just to demonstrate simple working of it.
/****** :) best regards ******/
set nocount on
go
drop table dbo.t_test
go
print 'tabel dropped'
go
CREATE TABLE dbo.T_Test (
Sleutel int IDENTITY (1, 1) NOT NULL ,
Ms_Ts timestamp NOT NULL ,
Ms_Datetime datetime NOT NULL ,
Ms_Datetime_Last_Used datetimeNOT NULL ,
Ms_Char char (10) NOT NULL
)
GO
print 'Table reated'
go
ALTER TABLE dbo.T_Test WITH NOCHECK ADD
CONSTRAINT DF_T_Test_Ms_Datetime DEFAULT (getdate()) FOR Ms_Datetime,
CONSTRAINT DF_T_Test_Ms_Datetime_Last_Used DEFAULT (getdate()) FOR Ms_Datetime_Last_Used,
CONSTRAINT PK_T_Test PRIMARY KEY NONCLUSTERED
(
Sleutel
)
GO
print 'Constraints added'
go
--drop trigger TrU_Ms_Datetime_Last_Used
--go
CREATE TRIGGER TrU_Ms_Datetime_Last_Used ON T_Test
FOR UPDATE
AS
if not UPDATE(Ms_Datetime_Last_Used)
begin
update T_Test set Ms_Datetime_Last_Used = CURRENT_TIMESTAMP where sleutel = ( select sleutel from deleted )
end
go
print 'Trigger added'
insert into t_test (Ms_Char) values('a')
go
insert into t_test (Ms_Char) values('b')
go
Print 'Rows inserted'
go
select * from t_test
go
Print 'Update starts here...'
go
update t_test set ms_char = 'c' where sleutel = 1
go
Print 'Sleutel 1 updated ...'
go
select * from t_test
go
select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime
, CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime
from t_test
order by sleutel
go
update t_test set ms_char = 'D' where sleutel = 2
go
Print 'Sleutel 2 updated ...'
go
select * from t_test
go
select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime
, CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime
from t_test
order by sleutel
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 25, 2008 at 9:20 pm
ALZDBA (3/25/2008)
Good old BOL did it again, thanks for the rectification.Here's a little test script I've found back from my test (sql7)
Just to demonstrate simple working of it.
Aye... nicely done, Johan...
Take a look at @@DBTS, as well.
--Jeff Moden
March 26, 2008 at 6:20 am
[font="Arial"]Hi,
I had read yr script and also implement it.
but can u explain me what is the benefit of timestamp column??
Thanks in advance..[/font]
March 26, 2008 at 6:37 am
krishrana17 (3/26/2008)
but can u explain me what is the benefit of timestamp column??
About the only thing it's good for is to let you know that something has changed. Each Insert/Update to a row with a TimeStamp datatype in it will cause the TimeStamp to be changed. That means a GUI could read the row, including the TimeStamp, and check to see if the TimeStamp has changed to see if the row is "dirty" from some other Update. Of course, you can use CheckSum or Binary_CheckSum to do almost the same thing without maintaining a column. "It Depends" on what you value the most... disk/backup space or CPU time.
The original purpose was to help some forms of recovery... here's from BOL again...
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.
--Jeff Moden
May 5, 2008 at 2:13 pm
Copying a table using "Select Into" copies the timestamp values from the original table. In this case, the values are not unique in the database.
Mike Wright
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply