Null Timestamp
-
For some cursor work I created a timestamp column on a temporary table.
,t_stamp timestamp null
I then inserted a null value into that table.
Insert Into #OriginalSort Select ..., null
It resulted in a null value being inserted. However, were I to add an order by clause into the query (something there usually is no point in doing with an insert query in a relational database) each row would be assigned a unique timestamp value.
I know how to make use of this effect but I don't see it documented anywhere. What are your thoughts on this?
-
That's how they work. Timestamps are taken care of by the server. You don't need to do anything to set their values. The server will set in on insert and on all updates.
-
No.
I meant the dual behavior.
In my stored procedure, when I try:
Insert Into ... Select ..., null From ...
I get a different result than when I do:
Insert Into ... Select ..., null From ... Order By ...
I'm trying to understand why this is.
-
I can't duplicate your behavior... the timestamps always get populated in this case :
create table #a (id int not null primary key clustered, ts timestamp null)
Insert into #a (id) values (-23451)
Insert into #a (id, ts) values (-23452, null)
Select * from #a
Insert into #a (id, ts) Select id, null from dbo.SysObjects where id < 1000000
Select * from #a
Insert into #a (id, ts) Select id, null from dbo.SysObjects where id >= 1000000 order by id
Select * from #a
drop table #a
Also why do you need
1 - TimeStamp on a temp table
2 - A cursor in the first place
-
Okay. I can't seem to reproduce this behavior in query analyzer.
-
Can you post your code??
Also you didn't answer my questions about the whys!!!!
-
Okay. I was thinking about an old problem I had before some changes were made a while back. I thought the issue still carried over into the example I posted (Insert Into... Select... ). I can't discuss the details as to why I did what I did with the cursor(it involves some proprietary stuff on behalf of the company it was done for).
This is the behavior I was wondering about:
select t_stamp=convert(timestamp, null) into #a order by 1
select t_stamp=convert(timestamp, null) into #b
select * from #a
select * from #b
drop table #a
drop table #b
-
I am not sure if it is related but the NULL TimeStamp is Varbinary while the NOTNULL TimeStamp is Binary. Try the link below for details. Hope this helps.
(A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.)
http://msdn2.microsoft.com/en-us/library/ms182776.aspx
Kind regards,
Gift Peddie -
It might turn out to be somehow related but I'm still not sure why the Order By should combine with the Select Into to produce this effect.
If someone has an explanation, I would appreciate seeing it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply