For those looking to insert a tamestamp column of type DateTime into an existing DB table, you can do this like so:

ALTER TABLE TestTable
ADD DateInserted DATETIME NOT NULL DEFAULT (GETDATE());

The existing records will automatically get the value equal to the date/time of the moment when column is added. New records will get up-to-date value upon insertion.

SQL Server will not track historically when a row was inserted or modified so you need to rely on the JSON data to figure that out yourself. You are going to need a new column to make this efficient to query. Once you have your new column you have some options:

1. Loop through all your records populating the new column with the relevant value from the JSON data.
2. If your version of SQL Server is recent enough, you can query the JSON data directly . Populate this column using a query like this:

UPDATE MyTable
SET MyNewColumn = JSON_VALUE(JsonDataColumn, '$.Customer.DateCreated')

The downside of this method is that you need to maintain this

1. Make SQL Server compute the value from the JSON automatically, for example:

ALTER TABLE MyTable
    ADD MyNewColumn AS JSON_VALUE(JsonDataColumn, '$.Customer.DateCreated')

And, create an index to make it efficient:

CREATE INDEX IX_MyTable_MyNewColumn
ON MyTable(MyNewColumn)

Use a new column CreatedDate and store datetime every time you make an Insert.

You could use GetDate() for inserting date in the column.

A UpdatedDate column can be used for updates.

in order to find e.g. the latest 100 entries. Timestamp is indeed what you need.

It's ever-increasing value, it's updated automatically, so you are always able to find all last modified/inserted rows.

Here is an example:

create table dbo.test1 (id int);

insert into dbo.test1 values(1), (2), (3);

alter table dbo.test1 add ts timestamp;

update dbo.test1
set id = 10 
where id = 2

select  top 1 *
from dbo.test1
order by ts desc;

--id    ts
--10    0x000000001FCFABD2

insert into dbo.test1 (id)
values (100);

select  top 1 *
from dbo.test1
order by ts desc;

--id    ts
--100   0x000000001FCFABD3

As you see, you always get the last modified/inserted row.

For your purpose just use

select top 100 *
...
order by ts desc;

Thanks. Apparently I didn't look hard enough before I posted this question. The question has been asked a couple of times before and the answer is: Nope! There is no easy solution to this.

SQL Server does not keep track of when a record was created or modified, which was somehow what I was looking for. So I will go for the next best solution, which is probably to create a datetime column, retrieve the modified date from the Json document and then update the record. Or rather, the 1,4 million records:-(

ADDING TIMESTAMP TO AN EXISTING TABLE IN SQL SYNTAX:

ALTER TABLE table_name ADD COLUMN column_name TIMESTAMP DEFAULT CURRENT_TIMESTAMP;