Results 1 to 4 of 4

Thread: function index

  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Unanswered: function index

    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.

    Thank,
    dc

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Explain more clearly what you want to do.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You have to create a computed column with the function you want indexed, then index the computed column. Then you have to change all of your applications to query the new column. Simple, eh?

    In short, Function Based Indexes don't really exist in SQL Server, but they are pretty nice things to have.

    create index ind on table (upper(name))

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Function based indicies (where the indexed value is the result of one or more function calls) were the most dearly missed feature of Clipper when I converted to SQL Server. They are lovely things when you don't have to deal with most of the complexities that a real relational database engine is intended to solve!

    Unfortunately, they don't scale, introduce all kinds of "functional dependancies", lead to really interesting collation questions, etc. A Function Based Index is certainly convenient in many ways, but it is a scaling/supportability nightmare in search of its next victim!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •