Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following tables:

CREATE TABLE Revision (
    RevisionId INT PRIMARY KEY IDENTITY,
    UserName NVARCHAR(256) NOT NULL,
    DateTime DATETIME NOT NULL DEFAULT GETDATE()
)

CREATE TABLE MyEntity (
    MyEntityId INT NOT NULL,
    RevisionId INT NOT NULL FOREIGN KEY REFERENCES Revision (RevisionId),
    Deleted BIT NOT NULL DEFAULT 0,
    Name NVARCHAR(256) NOT NULL,
    Body NVARCHAR(MAX) NOT NULL,
    PRIMARY KEY (MyEntityId, RevisionId)
)

That is, the MyEntity table has all changes to an entity. When an entity is created, modified, or deleted, a new record is inserted into MyEntity and into Revision, so that the entire history is tracked.

I would like a view to have the latest version of each entity:

CREATE VIEW MyEntityLatest WITH SCHEMABINDING AS
SELECT
    Latest.MyEntityId,
    Latest.Name,
    Latest.Body
FROM dbo.MyEntity
INNER JOIN (
    SELECT
        MyEntityId,
        RevisionId,
        Name,
        Body,
        RANK() OVER (PARTITION BY MyEntityId ORDER BY RevisionId DESC) AS RevisionIdDesc
    FROM MyEntity
    WHERE Deleted = 0
) AS Latest
    ON dbo.MyEntity.MyEntityId = Latest.MyEntityId
    AND dbo.MyEntity.RevisionId = Latest.RevisionId
WHERE Latest.RevisionIdDesc = 1

However, I would like to create an unique index (and hence constraint) such that the Name is unique for the latest revision only. I cannot create an index on the view, because of the subquery in the view.

How can I accomplish this?

share|improve this question
    
Why don't you have a UNIQUE constraint in the Entity table?: UNIQUE (Name). Doesn't that table hold the latest info and therefore the latest Name? –  ypercube Apr 7 '13 at 15:12
    
The MyEntity table holds all of the revisions, not just the latest. –  cubetwo1729 Apr 7 '13 at 15:16
    
I wasn't referring to the MyEntity table. I thought you also had another table with latest info. I don't see any other easy way, besides adding such a table. Some of the SQL-Server gurus that hang at the site will surely come up with other plans. –  ypercube Apr 7 '13 at 15:22
add comment

1 Answer

up vote 1 down vote accepted

Relying on the order of RevisionId to determine the latest revision is incredibly dangerous, and points to a design issue here.

Since you're using SQL Server 2008+, what I would recommend is to add a bit flag IsLatest to the MyEntity table to identify the latest revision. Then, create a unique filtered index to constrain each entity to only have a single latest revision (technically, this isn't required given what you asked for, but it's a Really Good Idea):

CREATE UNIQUE NONCLUSTERED INDEX UC_MyEntity_IsLatest_MyEntityId
    ON [dbo].[MyEntity](IsLatest, MyEntityId)
        WHERE (IsLatest = 1);

Then use the same technique to enforce the constraint on the name:

CREATE UNIQUE NONCLUSTERED INDEX UC_MyEntity_IsLatest_Name
    ON [dbo].[MyEntity](IsLatest, Name)
        WHERE (IsLatest = 1);

You'll need to manage the flag by turning it off from the "previous latest" revision before inserting the "new latest" revision, which is not much extra work. I'll leave it up to you to determine how this should work with the Deleted flag involved.

This solution enforces the constraint by design, and will greatly simplify the view definition, which you may not even need to index separately. (Indexing the Body column which is nvarchar(MAX) is a bit suspect, but that's a separate issue.)

Note: I included the IsLatest column in the key of the indexes on purpose, as the optimizer isn't smart enough to implicitly include the value of that column from the filter predicate (since the predicate is arbitrary, I assume it doesn't even try). This is what will allow the index to be used when you specify WHERE IsLatest = 1 in a query.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.