如何检查 sql 查询是否是确定性的？
我所说的确定性是指查询将始终返回完全相同的结果集。有办法做到这一点吗？
What I mean by deterministic is that the query will always return exactly the same result set. Is there a way to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
查询通常不被认为是确定性的，因为基础表可能会在调用之间发生变化。确定性通常仅在讨论 UDF 时发挥作用。
Queries aren't typically considered to be deterministic as the underlying tables can change between invocations. Determinism typically only comes into play when talking about UDFs.
据我所知，在数据库中生成确定性数据的唯一方法是在适当的时间点创建必要数据的快照。通常，数据会被转换并加载到数据仓库中，并附带正在归档的行的时间戳，以供以后检索。审计和其他类型的报告查询可以针对数据仓库运行，并且结果对于任何给定时间段是“确定性的”，但不是跨时间段的。
如有必要，数据仓库中存储的信息也可以预先计算到多维立方体中，以便更快地处理。为了保持正确的形式，数据仓库表应该只被插入，并且新批次的数据应该始终与唯一的时间戳相关联。通常最好以某种形式存储数据星形或雪花模式，这有助于生成如果需要的话，一个立方体。
The only way I know of to produce deterministic data in a database is to create a snapshot of the necessary data at the appropriate point in time. Usually, data is transformed and loaded into a data warehouse, along with a timestamp for the rows being archived, for later retrieval. Auditing and other kinds of reporting queries may be run against the data warehouse, and the results are "deterministic" for any given time period, but not across time periods.
Information housed in a data warehouse may also be precomputed into a multi-dimensional cube for faster processing, if necessary. To maintain proper form, data warehouse tables should only ever be inserted into, and new batches of data should always be associated with a unique timestamp. It is usually best to store data in some form of star or snowflake schema, which facilitates generation of a cube if necessary.