August 20, 2012 at 8:40 am
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.
August 20, 2012 at 9:02 am
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.
August 20, 2012 at 9:12 am
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.
August 20, 2012 at 9:29 am
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
August 20, 2012 at 9:32 am
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
August 20, 2012 at 9:43 am
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
August 20, 2012 at 10:25 am
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
August 20, 2012 at 10:36 am
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 bitas
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.
August 20, 2012 at 10:43 am
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 ...)
August 20, 2012 at 10:44 am
(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?)
August 20, 2012 at 10:49 am
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
August 20, 2012 at 12:55 pm
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
August 20, 2012 at 1:15 pm
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".
August 20, 2012 at 1:44 pm
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;
August 21, 2012 at 3:34 am
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 38 total)
You must be logged in to reply to this topic. Login to reply