Category Archives: Data Integrity
Basic Guidelines for Data Integrity in Enterprise Architecture
The original Relational Model of Data for Large Shared Data Banks [acm.org] was a major milestone in the history of information technology that has undergone changes in understanding over the years. Despite the fact There is No Database Magic [kimballgroup.com], A Review of Relational Concepts [wikipedia.org] is useful to avoid widespread Logical-Physical Confusion [dbdebunk.com]. Conceptual, […]
Time After Time: Sql Server Audit Triggers and Query Syntax
In the last post Time After Time: Creating a Valid Time Sql Audit Table, we created the basic data structures required for the audit tables. To complete the solution, we are now ready to create an audit trigger that will populate the audit_Orders history table every time the main Orders table is modified and discuss […]
Time After Time: Creating a Valid Time Sql Server Audit Table
In an earlier post Time After Time: Temporal Modeling for Sql Audits and Historic Views I discussed the concept of Valid-Time tables. This is a powerful technique that can be used for many different design goals. To further clarify the idea, here is a very basic example demonstrating the use of an Valid-Time audit history […]
Time After Time: Temporal Modeling for Sql Audits and Historic Views
Most database designs can also provide a quick answer to questions of the form: “What information do I currently have right now about last week ?” However, historic questions like “What information did I have at the time last week about last week ?” are not often not as easy to answer. Using time as […]
On Relations and Relationships: Sql and the Rise Of Relational Database Technology
E.F. (“Ted”) Codd conceived of his relational model for databases while working at IBM in 1969. Codd’s approach took a cue from first-order predicate logic, the basis of a large number of other mathematical systems, and was presented in terms of set theory leaving physical representation and access implementer-defined. In June of 1970, Codd laid […]
Dynamic IN without Dynamic T-Sql for Sql Server
Here is stripped down T-Sql code that illustrates a “no dynamic sql” approach to the common requirement for SQL Server stored procedures to accept comma delimited ID values while maintaining strong types. SQL Server @TableVariables are more efficient than regular temp tables and enhance the performance and scalability of this technique. However @TableVariables are _NOT_ […]