GetDate() 是确定性的 [英] Is GetDate() deterministic

问题描述

这是一个哲学问题.在 GetDate 的一次采访中，我将其作为非确定性函数的示例.我明白为什么这个论点站得住脚了；但这对我来说似乎是一个似是而非的论点.

This is a sort of philosophical question. In an interview I was present at GetDate was given as an example of a non deterministic function. I can see why that argument holds water; but it seems a specious argument to me.

详细说明:对于给定的时间实例(在 100 微秒范围内)，getdate 将返回特定值.

To elaborate: For a given instance in time (within a 100 microsecond band) getdate will return a specific value.

对于运行在相同时钟时间(由足够精确的时钟同步)的两台计算机，它们都将返回相同的 getdate 值.

For two computers running to the same clock time (synchronised by a sufficiently accurate clock) they will both return the same value for getdate.

所以这是确定性的.

可以认为 getdate 在不同的时间返回不同的值，因此它不能被描述为确定性的.

It can be argued that getdate returns different values at different times and so it can not be described as deterministic.

但是一个 sql 查询get x from y where primary key equals z"将为 x 返回相同的值，其中 z 是相同的值.因此，如果时钟固定为某个值，那么我们将始终收到相同的 getdate 值.

But a sql query "get x from y where primary key equals z" will return the same value for x where z is the same value. So if the clock is fixed to a certain value then we shall always receive the same value for getdate.

换句话说，getdate 的值由外部参数决定，这与使用 where 子句的 SQL 查询由该 where 子句参数控制的方式完全相同.

In other words the value of getdate is determined by an external parameter, in exactly the same way as a SQL query that uses a where clause is controlled by that where clause parameter.

那么为什么我们要暗示 getdate 是非确定性的，而选择查询中提供结果的任何其他变量参数都被描述为确定性的.

So why should we imply that getdate is non deterministic whereas any other variable parameter in a select query which provides a result is described as deterministic.

只是为了扩展问题；如果数据发生变化，那么我们会收到与 select 查询不同的值，然后我们解释说这些值不会影响确定性(创造一个词)，因为这些值会随着时间发生变化，就像 getdate 一样.

And just to extend the question; if the data changes then we receive different values to the select query, which we then explain do not affect the deterministicity (to coin a word) as the values have changed in time, just as getdate has.

为了扩展(作为编辑)，我可以使用 XP_CmdShell 设置特定日期，然后立即运行 GetDate()；忽略系统速度等的变幻莫测.然后我总是会得到相同的答案.这有效地否定了系统日期时间不是输入的论点，因为我已经通过 SQL 修改了它，从而将整个过程保持在 SQL 控制的循环中.

To expand (as an edit) I could use XP_CmdShell to set a particular date and then immediately run GetDate(); ignoring the vagaries of the speed of the system etc. I would then always get the same answer. This effectively of negates the argument that the system date time is not an input as I have modified it via SQL and thus kept the whole process within a SQL controlled loop.

推荐答案

对于运行在相同时钟时间(由足够精确的时钟同步)的两台计算机，它们都将返回相同的 getdate 值.

For two computers running to the same clock time (synchronised by a sufficiently accurate clock) they will both return the same value for getdate.

所以这是确定性的.

不，不是 - 确定性意味着函数返回相同的值给定相同的输入.在这种情况下，您没有输入，但您始终会获得不同的值！系统时钟不是输入，它是函数依赖的外部状态.

No, it's not - deterministic means that the function returns the same value given the same inputs. In this case you have no inputs, but you get different values all the time! The system clock is not an input, it is external state that the function relies upon.

任何依赖于表数据的查询都是不确定的，因为它依赖于外部状态.确定性函数的示例是那些不依赖于外部状态而仅依赖于函数输入的函数:FLOOR、DATEADD 等.

Any query that relies on table data is non-deterministic because it relies on external state. Examples of deterministic functions are those that do NOT rely on external state, but rely solely on inputs to the function: FLOOR, DATEADD, etc.

如果数据发生变化，那么我们会收到与 select 查询不同的值，然后我们解释说这些值不会影响确定性(创造一个词)，因为这些值会随着时间发生变化，就像 getdate 一样.

if the data changes then we receive different values to the select query, which we then explain do not affect the deterministicity (to coin a word) as the values have changed in time, just as getdate has.

实际上，这证明查询不是确定性的——如果外部状态的变化改变了查询的输出.

Actually, that proves that the query is NOT deterministic - if a change in external state changes the output of the query.

这篇关于GetDate() 是确定性的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！