Enforce business rules with indexed views and a two-row table

  • Thanks for the clarification. You think someone would have solved that already (unlike the active/inactive parents problem, I haven't had to solve overlapping dates). If no one posts a solution to that problem, however, I will accept the challenge to either use a trigger or admit in this case the view solution might be better.

  • I've just knocked up a quick solution to the overlapping dates problem.

    I have a table Locations for which the column LocationCodes must not have overlapping dates. A datetime of NULL means any date so an EffectiveFromDate of NULL means the earliest possible date and EffectiveUntilDate of NULL means it has no expiry date. The table also has a [Deleted] column which we don't care if there are rows that are deleted that have overlapping dates.

    Table script

    CREATE TABLE [dbo].[Locations]

    (

    [LocationId] int IDENTITY(1,1) NOT NULL,

    [LocationCode] nchar(4) NOT NULL,

    [EffectiveFromDate] datetime NULL,

    [EffectiveToDate] datetime NULL,

    [Description] nvarchar(30) NOT NULL,

    [Deleted] bit NOT NULL,

    CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED ([LocationId] ASC)

    )

    Some test data inserts:

    INSERT INTO [locations] ([LocationCode],[EffectiveFromDate],[EffectiveToDate],[Description],[Deleted])

    VALUES('AAAA','Jan 1 2012 12:00:00:000AM','Dec 31 2012 12:00:00:000AM','AAAA Description',0)

    INSERT INTO [locations] ([LocationCode],[EffectiveFromDate],[EffectiveToDate],[Description],[Deleted])

    VALUES('BBBB','Jan 1 2012 12:00:00:000AM','Dec 31 2012 12:00:00:000AM','BBBB Description',0)

    INSERT INTO [locations] ([LocationCode],[EffectiveFromDate],[EffectiveToDate],[Description],[Deleted])

    VALUES('AAAA','Feb 1 2012 12:00:00:000AM','Apr 1 2012 12:00:00:000AM','AAAA Description',1)

    INSERT INTO [locations] ([LocationCode],[EffectiveFromDate],[EffectiveToDate],[Description],[Deleted])

    VALUES('BBBB','Jan 4 2012 12:00:00:000AM','Jan 5 2012 12:00:00:000AM','BBBB Description',0)

    A query to detect any overlapping dates:

    SELECT LocationCode

    FROM Locations L1

    WHERE EXISTS(SELECT *

    FROM Locations L2

    WHERE L2.LocationCode = L1.LocationCode

    AND L2.LocationId > L1.LocationId

    AND (L1.EffectiveFromDate <= L2.EffectiveToDate

    OR L1.EffectiveFromDate IS NULL

    OR L2.EffectiveToDate IS NULL )

    AND (L1.EffectiveToDate >= L2.EffectiveFromDate

    OR L1.EffectiveToDate IS NULL

    OR L2.EffectiveFromDate IS NULL)

    AND L2.Deleted = 0)

    AND L1.Deleted = 0

    So I would just need to add CROSS JOIN to this this query to the table with two rows in and make it a schema bound view with a unique index on the location code. Job done!

    NB: In fact with this particular example I can get away without the table with two rows in by just changing the inequality on the LocationId join from ">" to "<>" i.e.: AND L2.LocationId <> L1.LocationId as this will always return two rows with the same location code.

  • Alternatively, you could simply define a composite foreign key constraint with a "hidden" persisted computed column, defining the subset of regions which are valid references for the office table.

    [font="Courier New"]

    ALTER TABLE dbo.Office

       DROP CONSTRAINT [FK_Office_Region];

    CREATE UNIQUE NONCLUSTERED INDEX ixRegion_RegionIDIsActive_UN ON dbo.Region (RegionId, IsActive);

    ALTER TABLE dbo.Office

       ADD h_RegionMustBeActive AS CAST(1 AS bit) PERSISTED;

    ALTER TABLE dbo.Office

       ADD CONSTRAINT fkOffice_ActiveRegion FOREIGN KEY (RegionId, h_RegionMustBeActive) REFERENCES dbo.Region (RegionId, IsActive);

    GO

    [/font]

    All your logic is contained within the two tables with standard constructs.

  • dcdanoland

    thanks

    Is it the ((2*isActive)-1) that is the scary looking part of it . That's just to change the 1 and 0 we have in the isActive columns to a 1 and -1 .

    Athos

  • Jason Hannas,

    Your code does indeed solve the problem by only adding a computed column and a foreign key. I'm not sure how well it would work with other constraint problems, such as Jonathan AC Roberts's post regarding the prevention of overlapping dates within a table. I think for such a problem it can be done using your technique, but I think it would tax my brain too much to figure it out, let alone understand it after I got it to work.

    -Dan

  • Athos,

    Yes, I do find ((2*isActive)-1) a bit scary, even though I do understand what it does. In general, complicated arithmetic in a computed column gives me the willies.

    -Dan

  • While I applaud the innovation behind this solution, I would rather keep things simple and more easily understandable when I have to return to it 2 months later...

    The addition of special fields, special tables, and a complex indexed view is unnecessary when a simple check constraint and user-defined function for each of the business rules we want to enforce would suffice. If the business rules change, or grow more complex, the functions can perhaps more easily be improved to meet the requirement.

    Create function dbo.udf_OfficeMayBeAddedToRegion (@RegionId int) returns bit

    as

    begin

    Return (select [isActive] from [dbo].[Region] where [RegionId] = @RegionId);

    end;

    go

    Create function dbo.udf_RegionMayBeSetInactive (@RegionId int) returns bit

    as

    begin

    Declare @result bit = 1;

    If exists (select top 1 1 from [dbo].[office] where [RegionId] = @RegionId and [IsActive] = 1)

    Set @Result = 0;

    Return @result;

    end;

    go

    Alter Table dbo.Region Add Constraint [CK_Region_Office_Activeness] Check (

    not ([IsActive] = 0 and dbo.udf_RegionMayBeSetInactive([RegionId]) = 0)

    )

    go

    Alter Table dbo.Office Add Constraint [CK_Office_Region_Activeness] Check (

    not ([IsActive] = 1 and dbo.udf_OfficeMayBeAddedToRegion([RegionId]) = 0)

    )

    go

    Just my twopenniesworth...

    - Steve

  • Steve in Fairfax (8/20/2012)


    While I applaud the innovation behind this solution, I would rather keep things simple and more easily understandable when I have to return to it 2 months later...

    The addition of special fields, special tables, and a complex indexed view is unnecessary when a simple check constraint and user-defined function for each of the business rules we want to enforce would suffice. If the business rules change, or grow more complex, the functions can perhaps more easily be improved to meet the requirement.

    Create function dbo.udf_OfficeMayBeAddedToRegion (@RegionId int) returns bit

    as

    begin

    Return (select [isActive] from [dbo].[Region] where [RegionId] = @RegionId);

    end;

    go

    Create function dbo.udf_RegionMayBeSetInactive (@RegionId int) returns bit

    as

    begin

    Declare @result bit = 1;

    If exists (select top 1 1 from [dbo].[office] where [RegionId] = @RegionId and [IsActive] = 1)

    Set @Result = 0;

    Return @result;

    end;

    go

    Alter Table dbo.Region Add Constraint [CK_Region_Office_Activeness] Check (

    not ([IsActive] = 0 and dbo.udf_RegionMayBeSetInactive([RegionId]) = 0)

    )

    go

    Alter Table dbo.Office Add Constraint [CK_Office_Region_Activeness] Check (

    not ([IsActive] = 1 and dbo.udf_OfficeMayBeAddedToRegion([RegionId]) = 0)

    )

    go

    Just my twopenniesworth...

    - Steve

    Thank you. I think this is the most direct method and clearly indicates to other DBAs, etc. that this business rule is a constraint on the table.

  • Jonathan AC Roberts,

    Bad news - I've been unable to create an indexed view for your query. First I had a few problems creating a view with schema binding - SQL Server didn't like some of the aliasing. Those problems were easily fixed. But when I tried to add an index, SQL Server wouldn't allow it because it had a subquery. SQL Server is awfully persnickety about what types of views can have an indexed added to it. The list of restrictions below is from http://msdn.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx. I couldn't think of a way to create the view without using some sort of self-join.

    To create an index on a view in SQL Server, the view definition must not contain any of the following:

    ANY, NOT ANY

    OPENROWSET, OPENQUERY, OPENDATASOURCE

    Arithmetic on imprecise (float, real) values

    OPENXML

    COMPUTE, COMPUTE BY

    ORDER BY

    CONVERT producing an imprecise result

    OUTER join

    COUNT(*)

    References to a base table with a disabled clustered index

    GROUP BY ALL

    References to a table or function in a different database

    Derived tables (subquery in FROM list)

    References to another view

    DISTINCT

    ROWSET functions

    EXISTS, NOT EXISTS

    Self-joins

    Expressions on aggregate results (for example, SUM(x)+SUM(x))

    STDEV, STDEVP, VAR, VARP, AVG

    Full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)

    Subqueries

    Imprecise constants (for example, 2.34e5)

    SUM on nullable expressions

    Inline or table-valued functions

    Table hints (for example, NOLOCK)

    MIN, MAX

    text, ntext, image, filestream, or xml columns

    Nondeterministic expressions

    TOP

    Non-Unicode collations

    UNION

    Contradictions SQL Server can detect that mean the view would be empty (for example, where 0=1 and ...)

  • (Posted this before reading "page 2" of the discussion... looks like this is the same as Jason Hannas' solution):

    Along the lines of athosfolk's note regarding use of a foreign key to enforce this rule... You could also do this by putting a nullable "RegionIsActive" column in Office. Create a unique constraint on the two columns (RegionId, IsActive) in Region, and have a second foreign key point from Office (RegionId, RegionIsActive) to the new unique constraint (RegionId, IsActive). Add a check constraint on Office that when IsActive=1, RegionIsActive=1; when IsActive=0, RegionIsActive is null. If you want add a trigger to set RegionIsActive when you set IsActive, but that's not necessary if you don't mind setting RegionIsActive yourself.

    (As an aside, I'm not sure athosfolk's solution would work... how would you set a Region active or inactive without violating the foreign key?)

  • Steve in Fairfax,

    I think your code snippit is the most accesible and understandable of the solutions offered in the posts. Of course, some would argue against relying on functions which can be problematic with regard to performance. It seems to me that in the many posts so far there is a range of simplicity vs. elegance and set-based vs. programmatic-based solutions. When confronted with so many good solutions, the answer in nearly every case is to go with the person who yells the loudest (or perhaps writes in all caps.)

    -Dan

  • dcdanoland (8/20/2012)


    Jonathan AC Roberts,

    Bad news - I've been unable to create an indexed view for your query. First I had a few problems creating a view with schema binding - SQL Server didn't like some of the aliasing. Those problems were easily fixed. But when I tried to add an index, SQL Server wouldn't allow it because it had a subquery. SQL Server is awfully persnickety about what types of views can have an indexed added to it. The list of restrictions below is from http://msdn.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx. I couldn't think of a way to create the view without using some sort of self-join.

    I don't think that's a big deal as it's easy to rewrite a self-join:

    SELECT L1.LocationCode

    FROM Locations L1

    INNER JOIN Locations L2

    ON L2.LocationCode = L1.LocationCode

    AND L2.LocationId <> L1.LocationId

    AND (L1.EffectiveFromDate <= L2.EffectiveToDate

    OR L1.EffectiveFromDate IS NULL

    OR L2.EffectiveToDate IS NULL)

    AND (L1.EffectiveToDate >= L2.EffectiveFromDate

    OR L1.EffectiveToDate IS NULL

    OR L2.EffectiveFromDate IS NULL)

    AND L2.Deleted = 0

    WHERE L1.Deleted = 0

  • Jonathan AC Roberts,

    You can't create an indexed view from a self-join query.

    When I try to create a view with the query you provided I get the following message:

    Msg 4512, Level 16, State 3, Procedure OverlappingEffectiveDatesView, Line 3

    Cannot schema bind view 'dbo.OverlappingEffectiveDatesView' because name 'Locations' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

    I changed the references to Location to dbo.Location and the Create View command works. I then tried to create the following index:

    CREATE UNIQUE CLUSTERED INDEX [IX_OverlappingEffectiveDatesView_LocationCode] ON [dbo].[OverlappingEffectiveDatesView]

    (

    [LocationCode] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    I received the following error:

    Msg 1947, Level 16, State 1, Line 2

    Cannot create index on view "DanTestArea.dbo.OverlappingEffectiveDatesView". The view contains a self join on "DanTestArea.dbo.Locations".

  • Regarding Steve from Fairfax's function-based solution... I agree that it is clear and pretty simple. However I think it may have concurrency and possibly integrity issues. You can see this in a database with "read committed snapshot" turned on:

    create database atest;

    use atest;

    alter database atest set read_committed_snapshot on;

    create table Region (RegionId int not null primary key, IsActive bit not null default 0);

    create table Office (OfficeId int not null primary key, RegionId int not null references Region (RegionId),

    IsActive bit not null default 0);

    go

    -- Create Steve's functions here

    go

    insert into Region values (1,1);

    insert into Office values (1,1,0);

    go

    begin tran

    update Region set IsActive = 0 where RegionId = 1;

    -- In another window: update Office set IsActive=1 where RegionId=1;

    -- Then ...

    commit;

    go

    -- Show Active Office in an InActive Region

    select * from Region;

    select * from Office;

  • I put the query for detecting overlapping dates into a function and then added this as a table constraint and this does the same job:

    Function

    CREATE FUNCTION CheckLocationsOverlappingDates()

    RETURNS int

    AS

    BEGIN

    DECLARE @Result int

    SET @Result=0 -- Initialise

    SELECT TOP(1)

    @Result=1

    FROM Locations L1

    INNER JOIN Locations L2

    ON L2.LocationCode = L1.LocationCode

    AND L2.LocationId <> L1.LocationId

    AND (L1.EffectiveFromDate <= L2.EffectiveToDate

    OR L1.EffectiveFromDate IS NULL

    OR L2.EffectiveToDate IS NULL)

    AND (L1.EffectiveToDate >= L2.EffectiveFromDate

    OR L1.EffectiveToDate IS NULL

    OR L2.EffectiveFromDate IS NULL)

    AND L2.Deleted = 0

    WHERE L1.Deleted = 0

    RETURN @Result

    END

    GO

    Add the constraint:

    ALTER TABLE Locations

    ADD CONSTRAINT chkLocationsOverlappingDates CHECK (dbo.CheckLocationsOverlappingDates() = 0 );

Viewing 15 posts - 16 through 30 (of 39 total)

You must be logged in to reply to this topic. Login to reply