1

I am using SQL Server 2014. I wish to create a column that automatically captures the time when a record is created and for this value to be persisted. I tried using the GETDATE() function but I learned this could not work since it is not deterministic.

The goal is to use this value to track the newest record in the table. I tried to do this using the rowversion (timestamp) but I could not use this either since in C# this type does not implement IComparer. Could you please suggest a function or technique that I could use. I appreciate all the help. Thanks.

chasingD
  • 21
  • 3
  • 2
    This is usually handled with Getdate() on insert if the row. It will not change unless you physically change it. The column can have it as the default. How is it changing for you? – S3S May 31 '18 at 22:55
  • 1
    @scsimon its changing as they have it as a non persisted computed column. so it will be reevaluated every time it is selected – Martin Smith May 31 '18 at 22:58
  • Ahhhhhh makes sense @MartinSmith good thinking. – S3S May 31 '18 at 22:58
  • or thats my assumption anyway – Martin Smith May 31 '18 at 23:00
  • https://stackoverflow.com/q/92082 is an example of what you want I think – S3S May 31 '18 at 23:02
  • @scsimon: I looked at the example and it is not exactly what I am looking for. I have added the column with default value given by getdate(). What I want is for the value to be persisted. As Martin Smith says, currently it is being re-evaluated every time the column accessed. – chasingD Jun 01 '18 at 11:38
  • Gordon’s answer is exactly what I was talking about – S3S Jun 01 '18 at 12:07

2 Answers2

2

If you do:

create table t (
    . . .
    createdAt datetime
);

insert into t( . . ., createdAt)
    select . . . , getdate();

Then the date of insertion is in the column.

If you do:

create table t (
    . . .
    createdAt datetime default getdate()
);

insert into t( . . .)
    select . . . ;

Then you have exactly the same behavior.

If you wanted a column that had the current datetime, then you would do:

create table t (
    . . .
    createdAt datetime default getdate(),
    cur_datetime as (getdate())
);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Using datetime to find newest record in table is not good idea. In example below all records will have the same value in ts field

declare @t table (id int identity, name nvarchar(50), ts datetime default getdate())

insert @t (name) values ('item #1'),('item #2'),('item #3'),('item #4');

select * from @t

to find latest inserted/updated record you should use timestamp field. You can convert timestamp to bigint

declare @t table (id int identity, name nvarchar(50), ts datetime default getdate(), ts1 timestamp)

insert @t (name) values ('item #1'),('item #2'),('item #3'),('item #4');

select id, name, ts, cast(ts1 as bigint) as ts1 from @t
Dmitry Kolchev
  • 2,116
  • 14
  • 16
  • Thank you very much for your help. The cast was exactly what I had been missing. Now I am able to use the "OrderBy" clause in C# without any problem. – chasingD Jun 01 '18 at 12:51