>Yes, you are right. But i read documentation long ago that filtered indexes are not optimized, so i try not to use a FOR clause with permanent indexes.


In this case it depends on what you need the index to do. A candidate index with a "FOR NOT DELETED" filter will enforce the unique SS#s and allow reusing deleted values, but you're right, it isn't optimizable. If you need an optimizable index on that field, then you would need a second regular index on that field. Two similar indexes serving different purposes.