Updating redundant/denormalized data automatically in SQL Server

Question

Use a high level of redundant, denormalized data in my DB designs to improve performance. I'll often store data that would normally need to be joined or calculated. For example, if I have a User table and a Task table, I would store the Username and UserDisplayName redundantly in every Task record. Another example of this is storing aggregates, such as storing the TaskCount in the User table.

• User
  • UserID
  • Username
  • UserDisplayName
  • TaskCount
• Task
  • TaskID
  • TaskName
  • UserID
  • UserName
  • UserDisplayName

This is great for performance since the app has many more reads than insert, update or delete operations, and since some values like Username change rarely. However, the big draw back is that the integrity has to be enforced via application code or triggers. This can be very cumbersome with updates.

My question is can this be done automatically in SQL Server 2005/2010... maybe via a persisted/permanent View. Would anyone recommend another possibly solution or technology. I've heard document-based DBs such as CouchDB and MongoDB can handle denormalized data more effectively.

Answer 1

You might want to first try an Indexed View before moving to a NoSQL solution:

http://msdn.microsoft.com/en-us/library/ms187864.aspx

and:

http://msdn.microsoft.com/en-us/library/ms191432.aspx

Using an Indexed View would allow you to keep your base data in properly normalized tables and maintain data-integrity while giving you the denormalized "view" of that data. I would not recommend this for highly transactional tables, but you said it was heavier on reads than writes so you might want to see if this works for you.

Based on your two example tables, one option is:

1) Add a column to the User table defined as:

TaskCount INT NOT NULL DEFAULT (0)

2) Add a Trigger on the Task table defined as:

CREATE TRIGGER UpdateUserTaskCount
ON dbo.Task
AFTER INSERT, DELETE
AS

;WITH added AS
(
    SELECT  ins.UserID, COUNT(*) AS [NumTasks]
    FROM    INSERTED ins
    GROUP BY    ins.UserID
)
UPDATE  usr
SET     usr.TaskCount = (usr.TaskCount + added.NumTasks)
FROM    dbo.[User] usr
INNER JOIN  added
        ON  added.UserID = usr.UserID


;WITH removed AS
(
    SELECT  del.UserID, COUNT(*) AS [NumTasks]
    FROM    DELETED del
    GROUP BY    del.UserID
)
UPDATE  usr
SET     usr.TaskCount = (usr.TaskCount - removed.NumTasks)
FROM    dbo.[User] usr
INNER JOIN  removed
        ON  removed.UserID = usr.UserID
GO

3) Then do a View that has:

SELECT   u.UserID,
         u.Username,
         u.UserDisplayName,
         u.TaskCount,
         t.TaskID,
         t.TaskName
FROM     User u
INNER JOIN   Task t
        ON   t.UserID = u.UserID

And then follow the recommendations from the links above (WITH SCHEMABINDING, Unique Clustered Index, etc.) to make it "persisted". While it is inefficient to do an aggregation in a subquery in the SELECT as shown above, this specific case is intended to be denormalized in a situation that has higher reads than writes. So doing the Indexed View will keep the entire structure, including the aggregation, physically stored so each read will not recalculate it.

Now, if a LEFT JOIN is needed if some Users do not have any Tasks, then the Indexed View will not work due to the 5000 restrictions on creating them. In that case, you can create a real table (UserTask) that is your denormalized structure and have it populated via either a Trigger on just the User Table (assuming you do the Trigger I show above which updates the User Table based on changes in the Task table) or you can skip the TaskCount field in the User Table and just have Triggers on both tables to populate the UserTask table. In the end, this is basically what an Indexed View does just without you having to write the synchronization Trigger(s).