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:
Post a Comment