What I mean by deterministic is that the query will always return exactly the same result set. Is there a way to do this?
+1
A:
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.
Donnie
2010-07-08 00:19:42
I know that determinism isn't really related to queries. But there is a business requirement (for audit purposes) that a query result must be reproducible.
Josh
2010-07-08 00:24:28
The only way you can guarantee this is to never allow updates or deletes on rows referenced in the query. If you have a one-to-many relationship even inserts become problematic. Your best bet here is to somehow archive the actual query result (perhaps as a report) so you can produce it later.
Donnie
2010-07-08 00:27:21
We don't allow updates or deletes, but we do allow inserts. As you mentioned that creates a problem when one-to-many relationships exist. What I am really after is some one to check that there are no one-to-many relationships that the query doesn't handle.
Josh
2010-07-08 00:30:49
A:
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.
jrista
2010-07-13 14:58:26