Index performance

I have a table called Transactions with the following schema:

ColumnName     DataType             Constraints
----------    ----------            -----------
   id             int           PK (Cluster-Index)
 details      varchar(50)

Later on, I add the following two columns to this table:

  • How to select specific records of groups based on criteria
  • In SQL Server, how to move/import a multiple .trc files to a trace table
  • Entity Framework auto assign FK to reference Entity during insertion?
  • Dynamic Grouping
  • Dynamic sql inside stored Procedure-sql server 2008
  • Need to archive data on successful cart transaction using Update SQL JOIN
  • ColumnName     DataType             Constraints
    ----------    ----------            -----------
       id             int           PK (Cluster-Index)
     details      varchar(50)
      date          datetime 
     comment      varchar(255)
    

    What will be the index performance on that table with the following query?

    select * from transactions where id=somenumber
    

    Will the performance change because I added the two columns? Will there be an effect on the clustered index?

    2 Solutions collect form web for “Index performance”

    Your performance will roughly be the same. Your clustered index defines the physical ordering of the rows. When you do a query on a clustered primary key, the database essentially does a binary search for your data. The result of adding columns means that not as many rows fit on the same data page. This means the database may have to do a bit more IO to get the same data.

    What will be the index performance on that table with the following query?

    No discernible change (see below for why).

    Will the performance change because I added the two columns?

    Yes, but minuscule. The actual search of the index will not be affected in any way because you’re not changing the key at all.

    The only impact is that you will be returning more data from your one record and that may result in a little more I/O but, since it’s only for the one record, it’s not relevant.

    Even if there are not more I/O operations to retrieve the record itself, you will be transferring more data to the client. So technically, it’s slower no matter what, but the difference between your previous schema and the changed one is not worth worrying about.

    Will there be an effect on the clustered index?

    No, you have not changed any of the parameters in such a way to affect the primary key.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.