Index performance

Question

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:

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?

Answer 1

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.

Answer 2

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.