CODEWITHSUNDEEP
sqlsql-server
rmdussa
rmdussa

Reputation: 1557

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:

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?

Upvotes: 1

Views: 186

Answers (3)

MJH
MJH

Reputation: 1750

No change, the sample query you supplied will use an index seek to get to the data page containing the row you want. If your query were to use a scan then you would probably see some difference in time, depending how many leaf level pages were scanned and how fast your storage system is.

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881463

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.

Upvotes: 0

brianegge
brianegge

Reputation: 29872

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.

Upvotes: 3

Related Questions