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?