StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You might want to first try an Indexed View before moving to a NoSQL solution:</p> <p><a href="http://msdn.microsoft.com/en-us/library/ms187864.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms187864.aspx</a></p> <p>and:</p> <p><a href="http://msdn.microsoft.com/en-us/library/ms191432.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms191432.aspx</a></p> <p>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.</p> <p>Based on your two example tables, one option is:</p> <p>1) Add a column to the User table defined as:</p> <pre><code>TaskCount INT NOT NULL DEFAULT (0) </code></pre> <p>2) Add a Trigger on the Task table defined as:</p> <pre><code>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 </code></pre> <p>3) Then do a View that has:</p> <pre><code>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 </code></pre> <p>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.</p> <p>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).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload