How To Insert Data If Not In Between In Sql Server 2008?

If I have a table of events like this: event_name begin_date(pk) end_date(pk) ------------------------------------------ holiday 2014-11-01 2014-11-05

Solution 1:

The best thing would be to avoid triggers and perform a check with if exists before inserting

IFNOTEXISTS (SELECT TOP 11 FROM MyTable WHERE @InsertedEndDate > begin_date AND @InsertedBeginDate < end_date)
BEGIN--doactualinsert/workEND

Its a simple check to find the first overlap. The Select TOP 1 1 is a trick to avoid actually fetching the data, it will return as soon as it matches a row that overlaps the date range you're actually trying to save

Solution 2:

Triggers should be your last resort. If your application uses a stored procedure, it's better if you put the validation there. Or you could use a check constraint. This is the condition you need to use, from what I understand of your problem:

SELECT*FROMTableWHERE@begin_date BETWEEN begin_date AND end_date
OR@end_date BETWEEN begin_date AND end_date
OR@begin_date < begin_date AND@end_date > end_date

If that query returns any rows, those @begin_date and @end_date values should't be inserted.

Solution 3:

I always think that if something can be constrained in the database, it should be. You never know which developer is going to disable a trigger, or bypass application code and run the insert directly, so while triggers and business logic is good, it is not fool proof.

The first thing I would do is constrain begin_date to be before end_date:

CREATETABLE dbo.T
(
    ID INTIDENTITY(1, 1) NOTNULL,
    Event_name VARCHAR(50) NOTNULL,
    begin_date DATENOTNULL,
    end_date DATENOTNULL
);
ALTERTABLE dbo.T ADDCONSTRAINT CHK_T_ValidDates CHECK (Begin_date <= end_date);

Then (if you don't already have one) you can create a calendar table (which are incredibly useful anyway):

CREATETABLE dbo.Calendar 
(
    DateDATENOTNULL
);
CREATEUNIQUE CLUSTERED INDEX UQ_Calendar_Date ON dbo.Calendar (Date);
GO
INSERT dbo.Calendar (Date)
SELECT  TOP (7305) DATEADD(DAY, ROW_NUMBER() OVER(ORDERBY a.object_id) -1, '20000101')
FROM    sys.all_objects a, sys.all_objects;
GO

Finally you can create an indexed view, to ensure that no dates are duplicated in your table:

CREATE VIEW dbo.TCheck
WITH SCHEMABINDING
ASSELECT  c.DateFROM    dbo.T
            INNER JOIN dbo.Calendar AS c
                ON c.Date >= t.begin_date 
                AND c.Date <= t.end_date;
GO
CREATE UNIQUE CLUSTERED INDEX UQ_TCheck_ID ON dbo.TCheck (Date);

In the tests I ran (comparing to a trigger) the indexed view performed about 50% better than the trigger, but neither performed well. Unfortunately, sometimes data integrity has a cost.