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

StackExchange Database Administrators Q#160530, answer score: 15