Post reply

How indexs are used to locate data from table

  • October 30, 2003 at 2:07 am

    #81448

    I have a table structure

    Titles (table)

    -> TitleID (Primary Key clustered index)

    -> AuthorID (Foreign Key non-clustered index)

    -> AccountID (Foreign Key non-clustered index)

    -> some other columns (title name etc)

    Now i fire the following query

    Select * from Titles

    Where AuthorID = 10 and AccountID = 20

    Suppose in the table there are 100 rows corresponding to AuthorID = 100 and 150 rows for AccountID = 20 and say

    28 rows where AuthorID = 100 and AccountID = 20.

    It means the above query returns 28 rows.

    Now my question is how the two non clustered index will be used, i mean to say which of the following scenario will

    be used in SQL server 2000

    1. Uses AuthorID non-clustered to find rows containg 100 and then out of these 100 rows find AccountID where value is 20

    If this happens in SQL server 2000 then does it mean the non-clustered index of AccountID is not used.

    2. Uses AuthorID non-clustered to find rows containg 100 and Uses AccountID non-clustered to find rows containg 20 and then take the intersection of these results.

    3. any other scenario (if that so please suggest).

  • October 30, 2003 at 2:47 am

    #480256

    Have you followed the query's execution plan in QA?

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • October 30, 2003 at 4:18 am

    #480262

    No i haven't seen its query plan.

    Actually i want to know what is the approach of SQL server.?

    No problem i will check the execution plan

  • October 30, 2003 at 4:21 am

    #480263

    quote:

    Actually i want to know what is the approach of SQL server.?

    ex ante you can't tell which strategy will be chosen by the optimizer.

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • October 30, 2003 at 9:41 pm

    #480390

    Hi Frank Kalis

    The query plan shows that it uses second approach that is select the data using indexes and then take a intersection of the results based on the primary key column.

  • October 30, 2003 at 11:29 pm

    #480395

    Get a copy of

    Inside sql server 2000 by Kalen Delaney.

    It tells you a lot about the internal structures in sql server.

    Cursors never.

    DTS - only when needed and never to control.


    Cursors never.
    DTS - only when needed and never to control.

  • October 31, 2003 at 12:58 am

    #480406

    quote:

    Cursors never.

    DTS - only when needed and never to control.

    ...

    Hey Nigel, where is the third line in your autosignature ?

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • October 31, 2003 at 11:07 pm

    #480553

    Beer is not cold and it's not fizzy

  • November 2, 2003 at 4:55 pm

    #480578

    The SQLServer is using only 1 index on one table at time.

    The optimizer will choose one of the index dependig on the statistics.

    After either there will be a bookmark lookup or it will access the table and fetch all the corresponding rows and selects the reqired ones (those corresponding to the second parameter)

    I hope in the next versions of there will be an improved index join/merge usage before accessing the data table itself

    Bye

    Gabor



    Bye
    Gabor

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

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