I have this table:

CREATE TABLE Table01 (column01 nvarchar(100));

And I want to create a unique index on column01 with this condition LEN(column01) >= 5

I tried:

CREATE UNIQUE INDEX UIX_01 ON Table01(column01) WHERE LEN(column01) >= 5;

I got:

Incorrect WHERE clause for filtered index 'UIX_01' on table 'Table01'.

And :

ALTER TABLE Table01 ADD column01_length AS (LEN(column01));
CREATE UNIQUE INDEX UIX_01 ON Table01(column01) WHERE column01_length >= 5;

Produces:

Filtered index 'UIX_01' cannot be created on table 'Table01' because the column 'column01_length' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

share|improve this question
    
@ypercubeᵀᴹ nvarchar(100) – Jalil 2 hours ago
up vote 8 down vote accepted

One method to workaround the filtered index restriction is with an indexed view:

CREATE TABLE dbo.Table01 (
  Column01 NVARCHAR(100)
);
GO

CREATE VIEW dbo.vw_Table01_Column01_LenOver5Unique
WITH SCHEMABINDING AS
SELECT Column01
FROM dbo.Table01
WHERE LEN(Column01) >= 5;
GO

CREATE UNIQUE CLUSTERED INDEX cdx
    ON dbo.vw_Table01_Column01_LenOver5Unique(Column01);
GO

INSERT INTO dbo.Table01 VALUES('1'); --success
INSERT INTO dbo.Table01 VALUES('1'); --success
INSERT INTO dbo.Table01 VALUES('55555'); --success
INSERT INTO dbo.Table01 VALUES('55555'); --duplicate key error
GO
share|improve this answer
    
And I expect this will perform much better than my monstrosity. – James Anderson 2 hours ago
    
@Dan Guzman should I use 'WITH SCHEMABINDING'? – Jalil 2 hours ago
    
@JamesAnderson, yes, SCHEMABINDING is required for an indexed view. The implication is of course that you will need to drop the view before altering the table. Tooling like SSDT will take care of that dependency automatically. – Dan Guzman 1 hour ago
    
@Jalil: ^^^ Apparently meant as a response for you. – Andriy M 1 hour ago
    
@AndriyM, yes. I hate auto-complete :-) – Dan Guzman 59 mins ago

This seems to be another of the many limitations of filtered indexes. Trying to bypass it with LIKE using WHERE column01 LIKE '_____' does not work either, producing the same error message ("Incorrect WHERE clause ...").

Besides the VIEW solution, another way would be to convert the computed column to a regular column and add a CHECK constraint so it has always valid data:

CREATE TABLE Table01 (column01 nvarchar(100),
                      column01_length int,
                      CHECK ( column01_length = len(column01)
                              AND column01 IS NOT NULL 
                              AND column01_length IS NOT NULL
                           OR column01 IS NULL 
                              AND column01_length IS NULL )
                     ) ;


CREATE UNIQUE INDEX UIX_01 ON Table01 (column01) WHERE column01_length >= 5 ;

Tested at rextester.com

Naturally, that means you need to explicitly populate column01_length with the correct length every time you populate column01 (on inserts and updates). That may be tricky, because you need to make sure that the length is calculated the same way as the T-SQL LEN() function does it. In particular, the trailing spaces need to be ignored, which is not necessarily how the length is calculated by default in various programming languages that client applications are written in. The logic may be easy to account for in the caller, but you need to be aware of the difference in the first place.
An option would be INSERT/UPDATE triggers to supply the correct value for the column, so it appears as computed to client applications.

share|improve this answer

I'm not sure how this will perform and there may be a much easier way to achieve this that I've overlooked, but this should do what you need if you're only interested in enforcing uniqueness.

CREATE TABLE dbo.Table01 
(
  Column01 NVARCHAR(100)
);
GO

CREATE FUNCTION dbo.ChkUniqueColumn01OverLen5()
RETURNS BIT
AS
BEGIN
DECLARE @Result BIT, @Count BIGINT, @DistinctCount BIGINT

SELECT  @Count = COUNT(Column01),
        @DistinctCount = COUNT(DISTINCT Column01)
FROM    Table01
WHERE   LEN(Column01) >= 5 

SELECT @Result = CASE WHEN @Count = @DistinctCount THEN 1 ELSE 0 END

RETURN @Result

END;
GO

ALTER TABLE dbo.Table01
ADD CONSTRAINT Chk_UniqueColumn01OverLen5
CHECK (dbo.ChkUniqueColumn01OverLen5() = 1);
GO

INSERT dbo.Table01 (Column01)
VALUES (N'123'), (N'1234');
GO

INSERT dbo.Table01 (Column01)
VALUES (N'12345');
GO

INSERT dbo.Table01 (Column01)
VALUES (N'12345'); -- Will fail
GO

INSERT dbo.Table01 (Column01)
VALUES (N'123'); -- Will pass
GO

UPDATE dbo.Table01
SET Column01 = '12345'
WHERE Column01 = '1234' -- Will fail
GO

SELECT * FROM dbo.Table01;
GO

DROP TABLE Table01;
DROP FUNCTION dbo.ChkUniqueColumn01OverLen5;
share|improve this answer
    
Using a scalar valued function in a check constraint or computed column definition will force all queries that touch the table to run serially, even if they don't reference the column. – sp_BlitzErik 1 hour ago
1  
@sp_BlitzErik Yep and that may not even be the worst thing about this solution :). I just wanted to see if it would work, hence the performance warning. – James Anderson 1 hour ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.