Post reply

How SQL Server Index Work

  • April 14, 2009 at 1:44 am

    #202066

    Can anyone explain me how SQL index works. For eample if i create individual index on two fields like Fname and Lname, can following SQL query use this indexes

    Select * from Table Where Fname='A' and Lname='B'

    Thanks in Advance

  • April 14, 2009 at 2:22 am

    #977073

    It can, providing that the query doesn't return too many rows.

    Have a read through these, they're not beginner-level articles, but they may be of use.

    http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

    http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 14, 2009 at 3:15 am

    #977087

    Thanks Gail for prompt reply

    Your answer prompt me to ask another question.

    If individual indexes work, then why we need composite indexes or which one is better individual or composite index.

    I am sorry if i am asking silly questions.

    ChandaMAMA

  • April 14, 2009 at 3:31 am

    #977093

    Did you read those articles?

    If, in your example, you have an index on FName and a second index on LName, then your query may be able to use one of them to do one of the filters, providing that the number of rows returned is not too high. Then SQL will have to fetch the rest of the columns from the clustered index/heap, then do the second filter, then return the result.

    If you have one index on FName and LName (which I thought is what you were describing in your first post) then SQL will use that index to do both filters, then go to the clustered index/heap to fetch the rest of the columns, then return the result.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 14, 2009 at 4:00 am

    #977100

    This may be a good place for you to start off with SQL Server Indexes:

    http://www.sqlteam.com/article/sql-server-indexes-the-basics

  • April 14, 2009 at 4:33 am

    #977106

    I read the article, its very interesting, what i guess is that there is no thumb rule for creating index you have to analyze data and then create index.

    Its basically an art and science 🙂

    Thanks a ton for you prompt help

    ChandaMAMA

  • April 14, 2009 at 4:34 am

    #977107

    Thanks

    I will see the link provided by you

    ChandaMAMA

  • April 14, 2009 at 4:54 am

    #977116

    ChandaMAMA (4/14/2009)

    I read the article, its very interesting, what i guess is that there is no thumb rule for creating index you have to analyze data and then create index.

    Its basically an art and science 🙂

    Yup. You need to know how SQL uses indexes, what queries in your system do and what the data looks like.

    For another article on indexing, see this:

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply