Post reply

Adding Non-Cloustered Index differs no. of rows returned in resultset

  • December 6, 2012 at 5:50 am

    #271216

    Hi,

    I was trying to tune a particular SQL query which use to take more than 10 seconds to return data. Added much needed Non-Clustered Index for one of the column used in where clause. However, the no. of rows returned has a difference before and after adding index. Is it a bug in SQL Server or something wrong the way the Index is created?

    I use Sql Server 2008 R2 version.

    Thanks,

    Suresh

    Regards,
    Suresh Arumugam

  • December 6, 2012 at 6:07 am

    #1566126

    Suresh Arumugam (12/6/2012)

    Hi,

    I was trying to tune a particular SQL query which use to take more than 10 seconds to return data. Added much needed Non-Clustered Index for one of the column used in where clause. However, the no. of rows returned has a difference before and after adding index. Is it a bug in SQL Server or something wrong the way the Index is created?

    I use Sql Server 2008 R2 version.

    Thanks,

    Suresh

    not enough information.

    it's more likely the WHERE statement you are using before and after are different...unless you specifically added a FILTERED Index?

    show us the query you actually used before and after you added the index.

    also, could any data have been added /changed after you added the index?

    a table i query yesterday might be different today, due to data entry by others is what I'm driving at.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 6, 2012 at 6:07 am

    #1566127

    Suresh

    Please will you post the query? What is the difference in the number of rows? Is anyone else using the database? If you drop the index and run the query again, do you get the original number of rows?

    John

  • December 6, 2012 at 6:20 am

    #1566129

    The only bug I'm aware of that did something like this was fixed in SQL Server 2005 SP3 CU3 and was never a problem in SQL Server 2008R2 - I'll take a look to see if I can find the connect item.

    --EDIT--

    Connect item here

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 4 posts - 1 through 3 (of 3 total)

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