Auto Populate Time Stamp in SQL Server 2005 Express

Recently I was experimenting with trying to modify a database by adding an auto-populating Time Stamp Column to each table in the database.
Like any reasonable person I decided to test the different techniques on a simplified test database which I keep for experimentations like these.
The thinking I was following was:
Add another column to the table, define it as a TimeStamp type, define it to auto-populate with the current TimeStamp.
So I went ahead and launched the Microsoft SQL Server Management Studio Express and created the following table.
USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TstAutoPopulatedTimeStamp](
[PID] [int] IDENTITY(1,1) NOT NULL,
[SomeText] [varchar](50) NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
This will create a table with two columns, PID and SomeText.
Then I populate this with some random data.
Insert into TstAutoPopulatedTimeStamp values (‘Hello There 001’)
Insert into TstAutoPopulatedTimeStamp values (‘Hello There 002’)
Insert into TstAutoPopulatedTimeStamp values (‘Hello There 003’)
Then I added another column called “Created” and decided on the Timestamp data type. Now I ran into a problem. I could not define a Default Value or Binding for this column. I then decided to define a Computed Column Specification based on a function. I used (getdate()) for this. I saved the table and ran a simple SELECT on the table.
select * from TstAutoPopulatedTimeStamp
Which gave me the following result.
PID SomeText Created
1 Hello There 001 2009-06-05 13:26:31.270
2 Hello There 002 2009-06-05 13:26:31.270
3 Hello There 003 2009-06-05 13:26:31.270
Which to me was really odd. Then I thought, well because I has defined this new column as not-nullable, on saving the table, SQL Server Express decided to fill this column with the time it was created (table was saved).
Now came the fire test, INSERT another record.
Insert into TstAutoPopulatedTimeStamp values (‘Hello There 004’)
PID SomeText Created
1 Hello There 001 2009-06-05 13:30:49.553
2 Hello There 002 2009-06-05 13:30:49.553
3 Hello There 003 2009-06-05 13:30:49.553
4 Hello There 004 2009-06-05 13:30:49.553
What I was expecting was that the latest record would have a new date-time while keeping the old Timestamp unchanged. So this result was really strange. I went back to the drawing board, Looked at what I might have missed.
Examining the table in the design view, I found something odd, the Data Type for the Created column was undefined. I thought, perhaps because this is a column defined through Computed Column Specification, SQL Server does not allow me to explicitly define it’s value.
I noticed something else, I could also persist the data when I define through Computed Column Specification. I decided to do that. I set Is Persist as Yes and saved. To my surprise, SQL Server instead of saving the table definition, it reported the following error.
‘TstAutoPopulatedTimeStamp’ table
– Unable to modify table.
Computed column ‘Created’ in table ‘Tmp_TstAutoPopulatedTimeStamp’ cannot be persisted because the column is non-deterministic.
Basically, what SQL Server was telling me that, I cannot make a column with Computed Column Specification persist data if the Data Type is not specified, i.e. non-deterministic.
This left me scratching my head. Ok, this didn’t work.
I changed my approach and removed the Computed Column Specification for the Created column. Defined the Data Type as datetime and set the Default Value or Binding to (getdate()). On saving the table, I didn’t get any error.
Going back, I ran another insert query like the one I am using so far. I got the following error.
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
I modified the Insert and ran the following SQL Script.
Insert into TstAutoPopulatedTimeStamp (SomeText) values (‘Hello There 005’)
select * from TstAutoPopulatedTimeStamp
Which gave me the result below:
PID SomeText Created
1 Hello There 001 2009-06-05 13:30:49.553
2 Hello There 002 2009-06-05 13:30:49.553
3 Hello There 003 2009-06-05 13:30:49.553
4 Hello There 004 2009-06-05 13:30:49.553
5 Hello There 005 2009-06-05 13:55:45.823
Now it was working as I wanted it to work. However, This required me to look at every query in the application to see if the queries were in this form or the other form. And, how much work I would need to do on it. Before starting off, I wanted to also check the UPDATE query.
Update TstAutoPopulatedTimeStamp set SomeText = ‘Updated Data’ where PID = 1
select * from TstAutoPopulatedTimeStamp order by created asc
I expected the records to be ordered with the Created column in ascending order. Here I ran into a problem that I was in a way expecting. The Created column against the said record did not change. It remained where it was.
PID SomeText Created
1 Updated Data 2009-06-05 13:30:49.553
2 Hello There 002 2009-06-05 13:30:49.553
3 Hello There 003 2009-06-05 13:30:49.553
4 Hello There 004 2009-06-05 13:30:49.553
5 Hello There 005 2009-06-05 13:55:45.823
This meant that I would have to manually change all the UPDATE queries in the application to include the setting of the Created column.
This meant changes in the data layer of an application running at the client side.
Bummer.
This meant that I would have to got onto using Triggers πŸ™‚ Not that I mind. That perhaps will be a better solution to this problem.
It would have been cool if I could do this by just setting one or two values in the table’s definition.

Recently I was experimenting with trying to modify a database by adding an auto-populating Time Stamp Column to each table in the database.

Like any reasonable person I decided to test the different techniques on a simplified test database which I keep for experimentations like these.

The thinking I was following was:

If I add another column to the table, define it as a TimeStamp type, define it to auto-populate with the current TimeStamp.

So I went ahead and launched the Microsoft SQL Server Management Studio Express and created the following table.

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TstAutoPopulatedTimeStamp](
[PID] [int] IDENTITY(1,1) NOT NULL,
[SomeText] [varchar](50) NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

This will create a table with two columns, PID and SomeText.

Then I populate this with some random data.

Insert into TstAutoPopulatedTimeStamp values ('Hello There 001')
Insert into TstAutoPopulatedTimeStamp values ('Hello There 002')
Insert into TstAutoPopulatedTimeStamp values ('Hello There 003')

Then I added another column called “Created” and decided on the Timestamp data type. Now I ran into a problem. I could not define a Default Value or Binding for this column. I then decided to define a Computed Column Specification based on a function. I used (getdate()) for this. I saved the table and ran a simple SELECT on the table.

select * from TstAutoPopulatedTimeStamp

Which gave me the following result.

PID	SomeText	Created
1	Hello There 001	2009-06-05 13:26:31.270
2	Hello There 002	2009-06-05 13:26:31.270
3	Hello There 003	2009-06-05 13:26:31.270

Which to me was really odd. Then I thought, well because I has defined this new column as not-nullable, on saving the table, SQL Server Express decided to fill this column with the time it was created (table was saved).

Now came the fire test, INSERT another record.

Insert into TstAutoPopulatedTimeStamp values ('Hello There 004')
PID	SomeText	Created
1	Hello There 001	2009-06-05 13:30:49.553
2	Hello There 002	2009-06-05 13:30:49.553
3	Hello There 003	2009-06-05 13:30:49.553
4	Hello There 004	2009-06-05 13:30:49.553

What I was expecting was that the latest record would have a new date-time while keeping the old Timestamp unchanged. So this result was really strange. I went back to the drawing board, Looked at what I might have missed.

Examining the table in the design view, I found something odd, the Data Type for the Created column was undefined. I thought, perhaps because this is a column defined through Computed Column Specification, SQL Server does not allow me to explicitly define it’s value.

I noticed something else, I could also persist the data when I define through Computed Column Specification. I decided to do that. I set Is Persist as Yes and saved. To my surprise, SQL Server instead of saving the table definition, it reported the following error.

'TstAutoPopulatedTimeStamp' table
- Unable to modify table.
Computed column 'Created' in table 'Tmp_TstAutoPopulatedTimeStamp' cannot be persisted because the column isΒ 
non-deterministic.

Basically, what SQL Server was telling me that, I cannot make a column with Computed Column Specification persist data if the Data Type is not specified, i.e. non-deterministic.

This left me scratching my head. Ok, this didn’t work.

I changed my approach and removed the Computed Column Specification for the Created column. Defined the Data Type as datetime and set the Default Value or Binding to (getdate()). On saving the table, I didn’t get any error.

Going back, I ran another insert query like the one I am using so far. I got the following error.

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

I modified the Insert and ran the following SQL Script.

Insert into TstAutoPopulatedTimeStamp (SomeText) values ('Hello There 005')
select * from TstAutoPopulatedTimeStamp

Which gave me the result below:

PID	SomeText	Created
1	Hello There 001	2009-06-05 13:30:49.553
2	Hello There 002	2009-06-05 13:30:49.553
3	Hello There 003	2009-06-05 13:30:49.553
4	Hello There 004	2009-06-05 13:30:49.553
5	Hello There 005	2009-06-05 13:55:45.823

Now it was working as I wanted it to work. However, This required me to look at every query in the application to see if the queries were in this form or the other form. And, how much work I would need to do on it. Before starting off, I wanted to also check the UPDATE query.

Update TstAutoPopulatedTimeStamp set SomeText = 'Updated Data' where PID = 1
select * from TstAutoPopulatedTimeStamp order by created asc

I expected the records to be ordered with the Created column in ascending order. Here I ran into a problem that I was in a way expecting. The Created column against the said record did not change. It remained where it was.

PID	SomeText	Created
1	Updated Data	2009-06-05 13:30:49.553
2	Hello There 002	2009-06-05 13:30:49.553
3	Hello There 003	2009-06-05 13:30:49.553
4	Hello There 004	2009-06-05 13:30:49.553
5	Hello There 005	2009-06-05 13:55:45.823

This meant that I would have to manually change all the UPDATE queries in the application to include the setting of the Created column.

This meant changes in the data layer of an application running at the client side.

Bummer.

This meant that I would have to got onto using Triggers πŸ™‚ Not that I mind. That perhaps will be a better solution to this problem.

It would have been cool if I could do this by just setting one or two values in the table’s definition.