SQL Server Performance

Temp table and non clustered index query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Dec 3, 2009.

  1. atulgoswami New Member

    Assume a temp table which does not have any non clustered index on it and let's say two columns are updated multiple times in a SP, then if i define a non clustered index on this temp table using 2 columns (which are not updated), then would it make any issue?

    I know, this is a very unusual question but still would like hear from experts on this.
    Thanks
    atulgoswami, Dec 3, 2009
    #2

  2. wayen.zjw New Member

    i have the same questionwaiting for the answers
    wayen.zjw, Dec 5, 2009
    #3

  3. Adriaan New Member

    Non-clustered index, columns being updated not part of index - I wouldn't expect any issues.
    Adriaan, Dec 7, 2009
    #4

  4. arunyadav Member

    Atul,
    I think you are concerned about the performance of updates and the role of indexes for this operation. So, as Adriaan said, this shouldn't be a problem.
    arunyadav, Dec 7, 2009
    #5

  5. satya Moderator

    What is the volume of data you are referring here?
    • Clustered indexes are faster than non clustered indexes if created prior to populating the data.
      • Clustered and Nonclustered indexes take about the same amount of time when created on a pre-populated table.
        • The biggest difference was seen when inserting data in spurts with a Nonclustered index.
        This is a smaller portion of what you can think and when you think about performance following factors are key to consider:
        • Speed of the server
          • Size of the data
            • Number of indexes
              • Cardinality of the data
                • Sorting the data prior to inserting
                  • Index fill factor (to prevent page splits)
                    • Other data types
    satya, Dec 7, 2009
    #6

Share This Page