Sunday, October 18, 2009

Persisted Columns Problem : Persisting a time difference

Below you'll see I attempt to define 2 calculated columns as PERSISTED...
CREATE TABLE [dbo].[WebActivity](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Url] [varchar] (255) NULL,
[TrackingDateTime] [datetime2] (0) NOT NULL,
[TrackingSeconds] AS DATEDIFF(SECOND,'20000101',TrackingDateTime) PERSISTED,
[TrackingDay] AS DATEDIFF(DAY,'20000101',TrackingDateTime) PERSISTED)
ON [PRIMARY]
GO

This fails however, with the error...

Msg 4936, Level 16, State 1, Line 1
Computed column 'TrackingSeconds' in table 'WebActivity' cannot be persisted because the column is non-deterministic.

What does this mean, then?

According to MSDN...

" Deterministic functions always return the same result any time they are called with a specific set of input values. "

and further down, the answer...

" Deterministic unless used with datetime, smalldatetime, or sql_variant. The datetime and smalldatetime data types are deterministic if the style parameter is also specified. "

So to obtain my 'Deterministic datetime2 difference I have used 2 CONVERT statements.
The first declares my defined epoch of 2000-01-01 as a DATETIME2.
The second casts the entire result back to a numeric data type.

CREATE TABLE [dbo].[WebActivity](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Url] [varchar] (255) NULL,
[TrackingDateTime] [datetime2] (0) NOT NULL,
[TrackingSeconds] AS CONVERT(INTEGER,DATEDIFF(SECOND,CONVERT(DATETIME2,'20000101',112),TrackingDateTime)) PERSISTED,
[TrackingDay] AS CONVERT(SMALLINT,DATEDIFF(DAY,CONVERT(DATETIME2,'20000101',112),TrackingDateTime)) PERSISTED,

ON PRIMARY
GO


No comments: