I have seen the filtered indexing in SQL Server 2008 mentioned a couple of times over the last couple of weeks but I only got it properly when I got to try it myself in ctp6.
I have often needed to constrain a column to a only allow unique values but also allow the column to allow nulls and with filtered indexes you can put in a where clause like this ...
create unique index production.nullidx on production.product(code) where production.product(code) is not null
create unique index production.nullidx
where production.product(code) is not null
So the filter takes out the nulls. Apparently this is also really useful if you are migrating your database from another provider to SQL Server.
SQL Server 2008 has a new feature called filtered indexes which are like normal table indexes only they
I am working in one scenario wherein I need to search on one field CustomerID.UI needs this to be displayed in varchar and I am storing this columnvalue in 2 fields one in int (for refering in FK) and varchar as CustomerID-('c0010') and CustomerSID-(1) as PK. Now if I search on CustomerID or CustomerSID which one will be faster. In record search if I have index on both then, using which column(int/varchar) I will get faster results. Let me know your views asap. How I can use filtered index to benefit the above scenario.
the integer index is the faster one as it is smaller and thereofre requires less io to access.
Filtered indexes would only be applicable to you if you had some customers who had a default SID that you don't want the index to pick up.
Sparse Columns are another new feature of SQL Server 2008 and are included in the February CTP (CTP6).
The command is very useful, that solves the problem when going to move the database from the other to SQL Server database!
It is also powerfull for many other cases and should be inclued in standard ANSII commands!!
Your query syntax is a little off - create unique index production.nullidx
where code is not null
would be the correct syntax - SQL 2008 SP1