为什么是[日期] + ([时间] - [偏移]) SQL Server 2008 中的不确定性？
我正在尝试对 IIS 日志表执行以下操作：
ALTER TABLE [W3CLog]
ADD [LogTime] AS [date] + ([time] - '1900-01-01') PERSISTED
但是，SQL Server 2008 告诉我：
Computed column 'LogTime' in table 'W3CLog' cannot be persisted because the column is non-deterministic.
该表具有以下定义：
CREATE TABLE [dbo].[W3CLog](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
...
[date] [datetime] NULL,
[time] [datetime] NULL,
...
)
为什么是非确定性的？
我确实需要对该字段建立索引。该表目前有 1598170 行，如果我们不能全程进行索引查找，那么查询会很痛苦。由于这是与其他一些日志格式进行 UNION 的，因此我们不能很容易地单独使用这两列。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的
'1900-01-01'是不确定的，因为它取决于语言设置。当然，这对于 DMY 或 MDY 设置来说是明确的，但一般来说，它是不明确的。尝试
'19000101'：SQL Server 处理日期和时间的方式有些奇怪：“yyyy-mm-dd”可以被视为“yyyy-” dd-mm" 如果你有英国设置，尽管理论上是 ISO编辑：使用它来删除日期方面：
DATEADD(day, 0, DATEDIFF(day, 0, [time]))Edit2: 1900 年 1 月 1 日在日期时间格式中为零，因此无需减去它。您可以发布示例数据和输出吗？
Your
'1900-01-01'is non-deterministic because it depends on language settings. of course, this is unambiguous for DMY or MDY settings bit generally it's ambiguousTry
'19000101': SQL Server treats dates and times somewhat oddly: "yyyy-mm-dd" can be treated as "yyyy-dd-mm" if you have British settings despite being ISO in theoryEdit: use this to remove the date aspect:
DATEADD(day, 0, DATEDIFF(day, 0, [time]))Edit2: 1st Jan 1900 is zero in the datetime formats, so no need to subtract it. Can you post sample data and output please?
好吧，感谢 @gbn 的回答，我明白了。
以下三者是等价的：
底部是确定性的。
Alright, I figured it out, thanks to @gbn's answer.
The following three are equivalent:
The bottom is deterministic.