Freigeben über

SQL Server 2008 Filtered Indexes

  • Artikel

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 

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.

Technorati Tags: indexing,SQL Server 2008

Comments

  • Anonymous
    May 22, 2008
    SQL Server 2008 has a new feature called filtered indexes which are like normal table indexes only they

  • Anonymous
    October 24, 2008
    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.

  • Anonymous
    October 27, 2008
    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.

  • Anonymous
    May 18, 2009
    Sparse Columns are another new feature of SQL Server 2008 and are included in the February CTP (CTP6).

  • Anonymous
    November 25, 2009
    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!!

  • Anonymous
    December 22, 2009
    Your query syntax is a little off - create unique index production.nullidx on production.product(code)   where code is not null would be the correct syntax - SQL 2008 SP1