To explain what an indexed view is, let’s first look at what constitutes a view. A view may sound like a fancy elaborate thing, however all it is, is a saved SELECT statement, nothing else. It is not explicitly compiled, nor does it contain any data. When you select from a view, it goes to [...]
Archive of posts filed under the Design category.
Introduction to Change Data Capture (CDC)
Change Data Capture (CDC) is a powerful feature included in SQL Server 2008. Using change data capture, it is possible to determine which values have been added, changed, or deleted within a table. Setting up CDC is done by specifying tables to monitor. Under the hood, CDC is written using the same repl logreader function [...]
How to Detect Table Changes or Changes in Data
This is a handy method can be used to detect if a change has happened to any set of data. This can include an entire table or even just two rows. SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM Sales.SalesOrderDetail From our execution, we see that a number is returned. The number itself doesn’t have a particular meaning. All it [...]
Using Synonyms to Decouple
Synonyms were first introduced in SQL 2005 and prove to be a great feature so far. What they basically do is decouple the database you are working in from other databases or objects you reference. This helps because it allows you to move objects (tables, procedures) to other locations without having to change the existing [...]
Choosing a Rules Engine Design
For those that don’t know. A rules engine is a way to harness powerful decision-making based on source data. These decisions are defined in rules. Rules will generally consist of a query that outputs a list of successes or failures, depending on what needs to be reported on. In my experience, I have only found [...]
Extracting Data from a Source System to History Tables
This is a topic I haven’t found much information written about, however nearly every system I’ve worked with needs this exact functionality. It is important that the method for extracting data be done in a way that does not hinder performance of the source system. In this example, the goal is to extract data from [...]
Triggers, Service Broker, CDC or Change Tracking?
In my most recent adventure, I was tasked with creating a real-time push subscription to our companies distributed publication server. This effort kicked off a lot of ideas, foremost being the “Asynchronous trigger” promoted by the service broker. While this asynchronous trigger is asynchronous in respect to “writing to an internal SQL Server object” (i.e. [...]
Creating a Rules Engine
A rules engine is a schedule based data validity application that typically runs as a meta-layer on top of an OLTP application. It fires a set of queries (defined as rules) which determine whether the underlying data elements comply with a specific rule’s definition. The compliance to the rule is then recorded indicating a pass [...]
