Use “LEN” function in “WHERE” clause in “CREATE UNIQUE INDEX”

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.

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

EDIT:

How should I define the view if I have two columns in the index?
CREATE UNIQUE INDEX UIX_01 ON Table01(column01, column02) WHERE LEN(column01)>=5

The indexed view approach can be extended for a composite key by adding other key columns to the view definition and index. The same filter is applied in the view definition but uniqueness of qualifying rows enforced by the composite key rather than the single column value:

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

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

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

INSERT INTO dbo.Table01 VALUES('1','A'); --success
INSERT INTO dbo.Table01 VALUES('1','A'); --success
INSERT INTO dbo.Table01 VALUES('55555','A'); --success
INSERT INTO dbo.Table01 VALUES('55555','B'); --success
INSERT INTO dbo.Table01 VALUES('55555','B'); --duplicate key error
GO

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 an INSERT/UPDATE trigger¹ to supply the correct value for the column, so it appears as computed to client applications.

_{¹As explained in Triggers Compared to Constraints, you would need to use an INSTEAD OF trigger for this. An AFTER trigger would simply never execute, because the absent length would fail the check constraint and that, in turn, would prevent the trigger from running. INSTEAD OF triggers, however, have their own restrictions (see DML Trigger Planning Guidelines for a quick overview).}

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;