Can timestamp columns be updated?

Hi,
Is there any way timestamp columns can be updated in Microsoft SQL Server
2000 (SP3)?
-- Many thanks, OskarNot directly.
Linchi
"Oskar" wrote:

> Hi,
> Is there any way timestamp columns can be updated in Microsoft SQL Server
> 2000 (SP3)?
> -- Many thanks, Oskar
>|||update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...[vbcol=seagreen]
> Not directly.
> Linchi
> "Oskar" wrote:
>|||Thanks. Not sure if this helps though. I really wanted to update the
timestamp column with the value I choose not the one of the internal @.@.DBTS
counter.
Any ideas if there's a way of updating the value the internal @.@.DBTS
counter, besides updating a zillion of records? Linchi?
"Hilary Cotter" wrote:

> update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=p
k
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
>
>|||"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:C9211B6D-90B4-43B2-AD80-F068FB8ACF17@.microsoft.com...
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal
@.@.DBTS
> counter.
There is no way. Not really sure why you'd want to anyway. There's
probably better ways of doing what you want to do.

> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?|||No, you can't directly update it. You can't do something like the following:
update myTable
set my_timestamp_column = ...
Linchi
"Oskar" wrote:
[vbcol=seagreen]
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal @.@.DBT
S
> counter.
> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?
>
> "Hilary Cotter" wrote:
>

Can timestamp columns be updated?

Hi,
Is there any way timestamp columns can be updated in Microsoft SQL Server
2000 (SP3)?
-- Many thanks, OskarNot directly.
Linchi
"Oskar" wrote:
> Hi,
> Is there any way timestamp columns can be updated in Microsoft SQL Server
> 2000 (SP3)?
> -- Many thanks, Oskar
>|||update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
> Not directly.
> Linchi
> "Oskar" wrote:
>> Hi,
>> Is there any way timestamp columns can be updated in Microsoft SQL Server
>> 2000 (SP3)?
>> -- Many thanks, Oskar|||Thanks. Not sure if this helps though. I really wanted to update the
timestamp column with the value I choose not the one of the internal @.@.DBTS
counter.
Any ideas if there's a way of updating the value the internal @.@.DBTS
counter, besides updating a zillion of records? Linchi?
"Hilary Cotter" wrote:
> update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
> > Not directly.
> >
> > Linchi
> >
> > "Oskar" wrote:
> >
> >> Hi,
> >> Is there any way timestamp columns can be updated in Microsoft SQL Server
> >> 2000 (SP3)?
> >>
> >> -- Many thanks, Oskar
> >>
>
>|||"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:C9211B6D-90B4-43B2-AD80-F068FB8ACF17@.microsoft.com...
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal
@.@.DBTS
> counter.
There is no way. Not really sure why you'd want to anyway. There's
probably better ways of doing what you want to do.
> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?|||No, you can't directly update it. You can't do something like the following:
update myTable
set my_timestamp_column = ...
Linchi
"Oskar" wrote:
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal @.@.DBTS
> counter.
> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?
>
> "Hilary Cotter" wrote:
> > update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> > news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
> > > Not directly.
> > >
> > > Linchi
> > >
> > > "Oskar" wrote:
> > >
> > >> Hi,
> > >> Is there any way timestamp columns can be updated in Microsoft SQL Server
> > >> 2000 (SP3)?
> > >>
> > >> -- Many thanks, Oskar
> > >>
> >
> >
> >

Can Timestamp column value overflow?

Hi,
I am having a table with a timestamp column. The data in this table is
assumed to accumulate over a period of time. On an avarage every second a
row is inserted into this table.
I using time stamp column for two reasons:
- Ensure that the value of timestamp is ever incrementing.
- Time stamp value should be unique.
Issue: Is there any probability of timestamp getting overflow over a period
of time? If there is a overflow what is the behavior of SQL Server in such
cases?
If there can not be overflow, then how SQL Server manages to generate ever
incrementing unique value for 8 bytes size column.
A bigint Identity column gives arithmetic overflow error if value exceeds
the maximum range.
Thanks in advance.
Pushkar> A bigint Identity column gives arithmetic overflow error if value exceeds
> the maximum range.
I don't remember the exact calculation, but I think you have to do 10,000
inserts a second for 122 years or something like that, to exceed the upper
bound of a BIGINT. So, you're probably safe. If you have anything other
than this table in the database, you're going to run out of available disk
space on the planet before you use up all of the unique, ever-incrementing
BIGINT values available to you.
Just my opinion.
A|||Consider using an identity column of datatype 4 byte int.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am having a table with a timestamp column. The data in this table is
> assumed to accumulate over a period of time. On an avarage every second a
> row is inserted into this table.
> I using time stamp column for two reasons:
> - Ensure that the value of timestamp is ever incrementing.
> - Time stamp value should be unique.
> Issue: Is there any probability of timestamp getting overflow over a
> period of time? If there is a overflow what is the behavior of SQL Server
> in such cases?
> If there can not be overflow, then how SQL Server manages to generate ever
> incrementing unique value for 8 bytes size column.
> A bigint Identity column gives arithmetic overflow error if value exceeds
> the maximum range.
>
> Thanks in advance.
> Pushkar
>|||> Issue: Is there any probability of timestamp getting overflow over a period of time? If t
here is a
> overflow what is the behavior of SQL Server in such cases?
The way I remember it:
Even when this was internally a 6 byte size (some earlier version), you coul
d do something like 100
transactions per second for over 100 years before overflow. And it is now 8
byte.
And well before such a theoretic overflow would happen, SQL Server would log
an error and not permit
any more updates in the database, would such a (theoretic) overflow occur. T
his I recall reading in
Books Online, some earlier version.
OK, did some calculations. See code below. If you do 1 transaction per secon
d, it will "overflow"
after 146,235,604,338 years. Or, put another way, if you do 1 million transa
ctions per second, it
will overflow after 146,235 years. Say you plan for a life span of 100 years
for the database, you
would have to do 1 billion transactions per second. TSQL calculations:
DECLARE @.noPossibleValues numeric(38,0)
DECLARE @.secondsPerYear int
SET @.noPossibleValues = POWER(CAST(2 AS numeric(38,0)), 62)
SET @.secondsPerYear = 60*60*24*365
SELECT @.noPossibleValues/@.secondsPerYear AS YearsIfOneTransactionPerSecond
SELECT @.noPossibleValues/(CAST(@.secondsPerYear AS bigint) * 1000000) AS
YearsIfMillionTransactionsPerSecond
And here is some old text that I found on the subject:
"Maximum Value
--
Timestamps increase until the maximum value that can be stored in 6
bytes (2**48) is reached (8 bytes, nowadays). When this maximum is reached,
the database
will not permit any more updates.
A 935 warning message is generated when there are only 1,000,000
timestamp values left in the database.
The only way to start over is to copy out all of the data with BCP
and to re-create the database; dumping and restoring will not help.
This is not a major concern because at 100 transactions per second,
2**48 will not wrap for more than 100 years."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.p
hx.gbl...
> Hi,
> I am having a table with a timestamp column. The data in this table is ass
umed to accumulate over
> a period of time. On an avarage every second a row is inserted into this
table.
> I using time stamp column for two reasons:
> - Ensure that the value of timestamp is ever incrementing.
> - Time stamp value should be unique.
> Issue: Is there any probability of timestamp getting overflow over a perio
d of time? If there is a
> overflow what is the behavior of SQL Server in such cases?
> If there can not be overflow, then how SQL Server manages to generate ever
incrementing unique
> value for 8 bytes size column.
> A bigint Identity column gives arithmetic overflow error if value exceeds
the maximum range.
>
> Thanks in advance.
> Pushkar
>|||Oops, I did 2**62 instead of 2**64. Means my numbers were on the conservativ
e side... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:e4gVkw0UGHA.1160@.TK2MSFTNGP09.phx.gbl...
> The way I remember it:
> Even when this was internally a 6 byte size (some earlier version), you co
uld do something like
> 100 transactions per second for over 100 years before overflow. And it is
now 8 byte.
> And well before such a theoretic overflow would happen, SQL Server would l
og an error and not
> permit any more updates in the database, would such a (theoretic) overflow
occur. This I recall
> reading in Books Online, some earlier version.
> OK, did some calculations. See code below. If you do 1 transaction per sec
ond, it will "overflow"
> after 146,235,604,338 years. Or, put another way, if you do 1 million tran
sactions per second, it
> will overflow after 146,235 years. Say you plan for a life span of 100 yea
rs for the database, you
> would have to do 1 billion transactions per second. TSQL calculations:
> DECLARE @.noPossibleValues numeric(38,0)
> DECLARE @.secondsPerYear int
> SET @.noPossibleValues = POWER(CAST(2 AS numeric(38,0)), 62)
> SET @.secondsPerYear = 60*60*24*365
> SELECT @.noPossibleValues/@.secondsPerYear AS YearsIfOneTransactionPerSecond
>
> SELECT @.noPossibleValues/(CAST(@.secondsPerYear AS bigint) * 1000000) AS
> YearsIfMillionTransactionsPerSecond
>
> And here is some old text that I found on the subject:
> "Maximum Value
> --
> Timestamps increase until the maximum value that can be stored in 6
> bytes (2**48) is reached (8 bytes, nowadays). When this maximum is reached
, the database
> will not permit any more updates.
> A 935 warning message is generated when there are only 1,000,000
> timestamp values left in the database.
> The only way to start over is to copy out all of the data with BCP
> and to re-create the database; dumping and restoring will not help.
> This is not a major concern because at 100 transactions per second,
> 2**48 will not wrap for more than 100 years."
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.phx.gbl...
>|||Thanks a lot. Now I can safely use it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uurqw78UGHA.4660@.tk2msftngp13.phx.gbl...
> Oops, I did 2**62 instead of 2**64. Means my numbers were on the
> conservative side... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:e4gVkw0UGHA.1160@.TK2MSFTNGP09.phx.gbl...
>sql