SQL Server:在SQL Server中设置日期,但保持确定性

我猜这是某种虫子。在SQL2005中,我能够创建这样一个索引视图,而没有任何问题(代码如下)。当我尝试在SQL 2000上运行它时,我得到了和您得到的相同的错误。

下面的内容似乎适用于SQL 2000,但我收到一个警告,即索引将被忽略,每次从视图中选择索引时都必须进行转换。

CONVERT(CHAR(8), datetime_column, 112)

在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

您的列[datetime_column]是否将默认值设置为“getDate()”??

如果是这样,由于getDate()函数是非确定性的,这将导致此错误…

用户定义函数是确定性函数还是非确定性函数取决于函数的编码方式。用户定义函数具有确定性,如果:

1. 函数是模式绑定的。
2. 所有内置或用户定义的 用户定义调用的函数 函数是确定性的。
3. 函数体引用 外部没有数据库对象 功能范围。例如, 确定性函数不能 表以外的引用表 局部变量 功能。
4. 函数不调用任何 扩展存储过程。

不符合这些条件的用户定义函数被标记为不确定函数。用户定义函数体中不允许使用内置的非确定性函数。

试试这个:

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

它应该比convert选项快得多。

以下是我回答原始问题的最佳答案:

试试这个:

/* 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

要测试,请尝试以下操作。请注意,在引用函数时,对计算列使用“persisted”和对[dbo]的使用

/*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) 
) 

现在应该可以在计算列上添加索引(但请参阅后面的gotcha)。

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

尽可能多次插入一些随机数据,但如果希望测试索引使用/执行计划,则最好插入1000+以上的数据。

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

得到结果

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

这就是你要的… 未使用在计算列上创建的索引!相反,即使在持久化字段testfoordate上选择数据,sqlserver(或者至少是我的版本)也更喜欢在testdate上使用索引。

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

我很肯定(从内存中)从性能的角度来看,计算列和持久列上的索引是有好处的-我猜您只需要尝试/测试自己的特定用法。

(希望我能帮忙!)

我建议简单一点:

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

但我怀疑你也会遇到同样的问题。

现在,如果问题在于回溯到日期时间,那么您可能需要考虑使用 cast([datetime_column] as int) 作为单独的字段,仅用于索引。

看 that question asked and answered by Cade Roux . 也许解决方案是使用WITHSchemaBinding创建一个函数,然后在计算列中使用它

编辑

我理解您的目标是能够在该列上有一个索引。

如果不能用计算列完成这项工作,那么可能唯一的选择就是创建一个普通列,并在每次更新它所基于的列时修改该列中的数据。(在触发器中说)