SQL Server: Floor a date in SQL server, but stay deterministic
(This is related to Floor a date in SQL server.)
Does a deterministic expression exist to floor a DATETIME? When I use this as a computed column formula:
DATEADD(dd, DATEDIFF(dd, 0, [datetime_column]), 0)
the I get an error when I place an index on that column:
Cannot create index because the key column 'EffectiveDate' is non-deterministic or imprecise.
But both DATEDIFF and DATEADD are deterministic functions by definition. Where is the catch? Is it possible?
My guess is that this is a bug of some sorts. In SQL 2005 I was able to create such an indexed view without a problem (code is below). When I tried to run it on SQL 2000 though I got the same error as you are getting.
The following seems to work on SQL 2000, but I get a warning that the index will be ignored AND you would have to convert every time that you selected from the view.
CONVERT(CHAR(8), datetime_column, 112)
Works in SQL 2005:
CREATE TABLE dbo.Test_Determinism ( datetime_column DATETIME NOT NULL DEFAULT GETDATE()) GO CREATE VIEW dbo.Test_Determinism_View WITH SCHEMABINDING AS SELECT DATEADD(dd, DATEDIFF(dd, 0, [datetime_column]), 0) AS EffectiveDate FROM dbo.Test_Determinism GO CREATE UNIQUE CLUSTERED INDEX IDX_Test_Determinism_View ON dbo.Test_Determinism_View (EffectiveDate) GO
Does your column [datetime_column] have a default value set to "getDate()" ??
If so, since getdate() function is non-deterministic, this will cause this error...
Whether a user-defined function is deterministic or nondeterministic depends on how the function is coded. User-defined functions are deterministic if:
- The function is schema-bound.
- All built-in or user-defined functions called by the user-defined function are deterministic.
- The body of the function references no database objects outside the scope of the function. For example, a deterministic function cannot reference tables other than table variables that are local to the function.
- The function does not call any extended stored procedures.
User-defined functions that do not meet these criteria are marked as nondeterministic. Built-in nondeterministic functions are not allowed in the body of user-defined functions.
CAST(FLOOR(CAST([datetime_column] as FLOAT)) AS DateTime)
It should go much faster than the CONVERT option.
Here's my best answer to answering the original question:
/* create a deterministic schema bound function */ CREATE FUNCTION FloorDate(@dt datetime) RETURNS datetime WITH SCHEMABINDING AS BEGIN RETURN CONVERT(datetime, FLOOR(CONVERT(float, @dt))) END GO
To test, try the following. Please note the use of "PERSISTED" for the computed column and the use of [dbo.] when referring to the function
/*create a test table */ CREATE TABLE [dbo].[TableTestFloorDate]( [Id] [int] IDENTITY(1,1) NOT NULL, [TestDate] [datetime] NOT NULL, [TestFloorDate] AS ([dbo].[FloorDate]([TestDate])) PERSISTED, CONSTRAINT [PK_TableTestFloorDate] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )
You should now be able to add an index on the computed column (but see gotcha later)
CREATE INDEX IX_TestFloorDate ON [dbo].[TableTestFloorDate](TestFloorDate)
Insert some random data as many times as you wish but more (1000+) is better if you wish to test index usage/execution plans
INSERT INTO TableTestFloorDate (TestDate) VALUES( convert(datetime, RAND()*50000))
Get the results
SELECT * FROM TableTestFloorDate WHERE TestFloorDate='2013-2-2'
Now here's the GOTCHA... The index that has been created on the computed column is not used! Instead, even when selecting data on the persisted field TestFloorDate, SQLServer (or at least my version) prefers an index on TestDate.
CREATE INDEX IX_TestFloorDate ON [dbo].[TableTestFloorDate](TestDate)
I'm pretty sure (from memory) that indexes on computed, persisted columns are of benefit from a performance perspective - I guess you'll just have to try/test for your own specific usages
(Hope I've helped!)
I'd suggest the somewhat simplier:
cast(cast([datetime_column] as int) as datetime)
but I suspect you'll run into the same problem.
Now if the problem is in casting back to a datetime, you might want to consider using just cast([datetime_column] as int) as a separate field, just for the index.
Look at that question asked and answered by Cade Roux. Perhaps the solution would be to create a function using WITH SCHEMABINDING and then use it in the computed column
I understand that you goal is to be able to have an index on that column.
If that cannot be done with a computed column then perhaps the only option would be to create an ordinary column and modify the data in that column each time you update the one it is based on. (say in trigger)