SQL Server filtered index - can anyone explain this error I am seeing? -

okay, have table filtered index. here script create table, add data , add index:

create table supplier (     supplierid int not null,     supplierlinkid int null,     suppliername varchar(50)); insert supplier values (1, 2, 'test supplier 1'); insert supplier values (2, null, 'test supplier 2'); insert supplier values (3, null, 'test supplier 3'); create unique nonclustered index uq$supplier$supplierlinkid on supplier (supplierlinkid)     supplierlinkid not null; 

then decide want add new column, supplier code, table , maintainability reasons don't want @ end of table. can't use:

alter table supplier add suppliercode varchar(50); 

instead go table designer, highligh supplierlinkid column , right-click, insert column , enter details:

• column name = suppliercode;
• data type = varchar(50);
• allow nulls =

when click save error:

'supplier' table - unable create index 'uq$supplier$supplierlinkid'.
create unique index statement terminated because duplicate key found object name 'dbo.supplier' , index name 'uq$supplier$supplierlinkid'. duplicate key value (). statement has been terminated.

but index supposed filtered, reason ignoring filter.

if try drop index:

drop index supplier.uq$supplier$supplierlinkid; 

...and go still open table designer error instead:

table 'supplier' has changed: - index 'uq$supplier$supplierlinkid' deleted , recreated.

so have come way out of designer , go in make change. can recreate index (that shouldn't have been problem in first place).

but decide want new supplier code column non-nullable go , change in designer... , same error index having duplicate values. instead have script job with:

alter table supplier alter column suppliercode varchar(50) not null; 

now can understand table designer might not if changes made table while open , quite understandable. don't understand why can't use table designer make changes table without getting spurious error filtered index having duplicate values when doesn't.

i reorder new database columns readability poor future devs & dbas come after me. after all, in mature applications 40% - 90% of time spent rework , changing requirements.

however, microsoft doesn't agree, there no column reordering sql can vote on microsoft connect if wasting time. prefer use temp tables , recreate table, below...

--rename table free name exec sp_rename 'supplier', 'supplier_backup' go  --create table again new column in order create table supplier (     supplierid int not null,     suppliercode varchar(50),     supplierlinkid int null,     suppliername varchar(50)) go  --copy data in  insert supplier (supplierid,suppliercode,supplierlinkid,suppliername) select supplierid,null,supplierlinkid,suppliername supplier_backup go  --add index create unique nonclustered index uq$supplier$supplierlinkid on supplier (supplierlinkid)      supplierlinkid not null go  --cleanup old table drop table supplier_backup go