Constraint to prevent overlapping Time periods
With the 24 hour restriction you can change your function to the following:

CREATE FUNCTION [dbo].[fnIsItemGroupAvailabilityValid]
(
    @availabilityStart time(0),
    @availabilityEnd time(0)
)
RETURNS int
AS
BEGIN
    DECLARE @OverLappingRows int

    IF(@availabilityEnd &gt; @availabilityStart)
    BEGIN
        SELECT @OverLappingRows = count(*) 
        FROM [dbo].[ItemGroupAvailability] iga
        WHERE @availabilityStart &lt; iga.AvailabilityEnd 
              AND @availabilityEnd &gt; iga.AvailabilityStart                
    END
    ELSE
        SET @OverLappingRows = 1

    RETURN @OverLappingRows
END


This will insure that the times are sequential, if not it returns an @OverLappingRows = 1 as a sentinel value to tell you something went wrong. You can set it to something else like -1 if you want to specifically catch that failure case.

I removed the equals from the comparisons to drop the inclusion aspect when comparing, it should now allow time spans to butt up against each other but not overlap. Dropped the OR as it's redundant now that we're verifying the the two time stamps are sequential before running the query.

Finally, all day should be 0:00 to 23:59, not 0:00 to 0:00 otherwise you need to do a bunch of convoluted logic.