Filtered Indexes?

Filtered index:

Filtered indexes for SQL Server were introduced in SQL Server 2008. Put simply, filtered indexes are nonclustered indexes that have the addition of a WHERE clause. Although the WHERE clause in a filtered index allows only simple predicates, it provides notable improvements over a traditional nonclustered index. This allows the index to target specific data values – only records with certain values will be added to the index – resulting in a much smaller, more accurate, and more efficient index.
The basic syntax for creating a filtered index is:

CREATE NONCLUSTERED INDEX <index name> ON <table> (<columns>) WHERE <criteria>; GO

Advantages of using filtered indexes

Reduced index maintenance costs. Insert, update, delete, and merge operations are not as expensive, since a filtered index is smaller and does not require as much time for reorganization or rebuilding.
Reduced storage cost. The smaller size of a filtered index results in a lower overall index storage requirement.
More accurate statistics. Filtered index statistics cover only the rows the meet the WHERE criteria, so in general, they are more accurate than full-table statistics.
Optimized query performance. Because filtered indexes are smaller and have more accurate statistics, queries and execution plans are more efficient.

Limitations

• Statistics may not get updated often enough, depending on how often filtered column data is changed. Because of how SQL Server decides when to update statistics (when about 20% of a column’s data has been modified), statistics could become quite out of date. The solution to this issue is to set up a job to run UPDATE STATISTICS more frequently.
• Filtered indexes cannot be created on views. However, a filtered index on the base table of a view will still optimize a view’s query.
• XML indexes and full-text indexes cannot be filtered. Only nonclustered indexes are able to take advantage of the WHERE clause.
• The WHERE clause of a filtered index will accept simple predicates only.

Can we create non clustered index on a primary key column?

Yes we can create non clustered index on a primary key column!

According to the default behaviour of sql server, when we create a primary key column in a table then a clustered index will be automatically created on that particular column.

This default behaviour can be changed. And we can tell sql server not to create any index on primary key column. i.e, we can ask sql server to create primary key column without any index and some other column with clustered index.
So, this is one method in which we can create non clustered index on a primary key column. This is shown in the below way.

CREATE TABLE dbo.Table_1 (
Id int NOT NULL IDENTITY (1, 1) PRIMARY KEY NONCLUSTERED,SomeOtherUniqueColumn int NOT NULL UNIQUE CLUSTERED ) ON [PRIMARY]