CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
innomatics
innomatics

Reputation: 369

Use Indexed View to limit number of duplicates in table

I have a table that may contain duplicates. I would like to constrain the table to allow only a certain number of duplicates (e.g. 100). I know that this can be achieved using a UDF and a table constraint - but I would like to avoid that as there are disadvantages including performance.

Instead, I am trying the approach of using a unique constraint on an indexed view. Here is what I have tried:

CREATE VIEW DuplicateCheckView
WITH SCHEMABINDING
AS
    SELECT        1 AS Marker
    UNION ALL
    SELECT        1 AS Marker
    FROM          DuplicateContainingTable
    GROUP BY      KeyColumn1, KeyColumn2, KeyColumn3 
    HAVING        COUNT(1) > 100 
GO

CREATE UNIQUE CLUSTERED INDEX IXU_DuplicatesCheck
   ON DuplicateCheckView (Marker);
GO

This results in the error

Msg 10116, Level 16, State 1, Line 1
Cannot create index on view 'DuplicatesCheckView' because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.

OK I understand the error (but not really why it has to be that way), is there any other way to achieve this?

Upvotes: 1

Views: 137

Answers (1)

innomatics
innomatics

Reputation: 369

This is not possible. Another barrier is that HAVING clause is not allowed in indexed view either (as well as sub-queries). I have instead implemented a trigger-based approach.

Upvotes: 1

Related Questions