SQL Server Performance

function index

Discussion in 'Performance Tuning for DBAs' started by dcforum, Nov 30, 2005.

  1. dcforum New Member

    I have implemented function based indexes on tables in Oracle database. I want to implement same in SQL Server. Is there something similar to function index in SQL Server also? If yes, please explain as how I can do this.

  2. FrankKalis Moderator

  3. Adriaan New Member

    You can always add a column for the derived values that you want indexed, using triggers on update and insert to maintain the data on the column. Add an index on that column and you're done.

    Of course the column is redundant normalization-wise, but if it helps performance then why not ... But do keep an eye on response times on insert and update.
  4. FrankKalis Moderator

    Yes, that might be a workaround. You can have a computed column which you can index. However, when you index such a column you materialize it and it will occupy disk space. Carefully test for the performance impact.

    Frank Kalis
    Microsoft SQL Server MVP
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. dcforum New Member

    Yes you workaround will help definately. But I am looking for a common implementation across different databases. I have used function indexing in Oracle and want to check whether there is something similar existing on SQL Server side, as I could not find anything similar to it.

    Thanks guys for your time.
  6. Adriaan New Member

    It would be a nice feature to have, but I wouldn't be surprised if there's a lot of overhead involved in Oracle, slowing down response times if you're not careful.

    Like Frank pointed out, a computed column might be your best shot. If the expression that you want to index applies to all tables to which you want to add the function index, then you might be able to create a UDF for it - however that would make life a lot more difficult if you ever want to change the UDF, then you might as well just have a complex expression for the computed column and change each instance.

    Have fun.

Share This Page