SQL Server: set date on SQL server, but stay deterministic

Question

(This is due to set the date on the SQL server .)

Is there a deterministic expression to populate DATETIME? When I use this as a calculated column formula:

DATEADD(dd, DATEDIFF(dd, 0, [datetime_column]), 0) 

I get an error when I put the index in this column:

It is not possible to create an index because the Effective Time key column is not deterministic or inaccurate.

But both DATEDIFF and DATEADD are deterministic functions by definition. Where to catch? Is it possible?

Answer 1

I assume this is some kind of mistake. In SQL 2005, I was able to create such an indexed view without any problems (code below). When I tried to run it on SQL 2000, I got the same error as you.

The following seems to work on SQL 2000, but I get a warning that the index will be ignored and you will have to convert every time you select 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 

Answer 2

Does your [datetime_column] column have a default value of "getDate ()"?

If so, since the getdate () function is not deterministic, this will cause this error ...

Regardless of whether a user-defined function is deterministic or non-deterministic, it depends on how the function is encoded. User-defined functions are determinate if:

• The function is tied to a circuit.
• All built-in or user-defined functions called by a user-defined function are deterministic.
• Function reference body There are no database objects outside the scope of the function. For example, a deterministic function cannot look up tables, other than table variables that are local to the function.
• The function does not call any extended stored procedures.

Custom functions that do not meet these criteria are marked as non-deterministic. Built-in non-deterministic functions are not allowed in the body of user-defined functions.

Answer 3

Try the following:

 CAST(FLOOR(CAST([datetime_column] as FLOAT)) AS DateTime) 

It should go much faster than the CONVERT option.

Answer 4

Here is my best answer to the original question:

Try the following:

 /* 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. Note the use of "PERSISTED" for the computed column and the use of [dbo.] When calling a 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) ) 

Now you can add an index to the calculated column (but see later)

 CREATE INDEX IX_TestFloorDate ON [dbo].[TableTestFloorDate](TestFloorDate) 

Insert some random data as many times as you want, but more (1000+) is better if you want to check index usage / execution plans

 INSERT INTO TableTestFloorDate (TestDate) VALUES( convert(datetime, RAND()*50000)) 

Get Results

 SELECT * FROM TableTestFloorDate WHERE TestFloorDate='2013-2-2' 

Now here is GOTCHA ... The index created in the computed column is not used! Instead, even when selecting data in the TestFloorDate field to be saved, SQLServer (or at least my version) prefers the TestDate index.

 CREATE INDEX IX_TestFloorDate ON [dbo].[TableTestFloorDate](TestDate) 

I'm sure (from memory) that indexes on computed, persistent columns have an advantage in terms of performance - I think you just need to try / test your own specific actions

(Hope I helped!)

Answer 5

Take a look at this question asked and answered by Cade Roux . Perhaps a solution would be to create a function using WITH SCHEMABINDING and then use it in a computed column

EDIT

I understand that you must have an index in this column.

If this cannot be done with a computed column, perhaps the only option would be to create a regular column and change the data in that column with each update of the one on which it is based. (say in a trigger)

Answer 6

I would suggest somewhat simpler:

  cast(cast([datetime_column] as int) as datetime) 

but I suspect that you will face the same problem.

Now, if the problem is returning to the time date, you may need to use only cast([datetime_column] as int) as a separate field for the index only.