Why is [date] + [time] non-deterministic in SQL Server 2008?
- by John Gietzen
I'm trying to do the following for my IIS logs table:
ALTER TABLE [W3CLog]
ADD [LogTime] AS [date] + ([time] - '1900-01-01') PERSISTED
However, SQL Server 2008 tells me:
Computed column 'LogTime' in table 'W3CLog' cannot be persisted because the column is non-deterministic.
The table has this definition:
CREATE TABLE [dbo].[W3CLog](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
...
[date] [datetime] NULL,
[time] [datetime] NULL,
...
)
Why is that non-deterministic?
I really need to index that field. The table currently has 1598170 rows, and it is a pain to query if we can't do an index seek on the full time. Since this is being UNION'd with some other log formats, we can't very easily just use the two columns separately.
