SQL Select and primary keys

  • Thread starter Thread starter J L
  • Start date Start date
J

J L

I should know this but I dont and cant find an answer via Google...

If my table as a primary key bases on multiple fields and I do a
select with a Where statement using those fields, will the select be
faster because of the primary keys?

Hope that makes sense...

TIA
John
 
Hi,

Yes, it will be faster, but it is not just because of the primary key. When
you create a primary key on a table, then SQL Server create an index. In a
case of primary this index is actual sorting of the data and access will be
fast, because records already presorted. But you could build index on
another set of field(s0 and it will allow to speed up selection on those
fields as well.
 
Hi Val,
Thank you for the response. And does this apply to the Jet engine and
Access tables also?

TIA
John
 
If you have a composite primary key (say, 2 fields called a and b) and you
include them both in the WHERE clause, you likely will get better retrieval
speed than if you went without them. However, the reason is that the primary
key also has an index on it. You can create a clustered index (the data is
physically sorted in order) or a non-clustered index (uses its a separate
index storage using trees). Depending on the needs of your system you pick
one or the other.

But in short, yes, using primary keys in a WHERE clause most likely will
speed up your query.

// John Papa
// http://codebetter.com/blogs/john.papa
 
Yes, this is true for the Access database as well. I would say it should be
true for any kind of the database. Otherwise it does not make any sense.

--
Val Mazur
Microsoft MVP

http://xport.mvps.org
 
Back
Top