I have a PK on two fields on my table (UtilityId, int & ACCT_NO varchar(100)) I really don't know if the order makes a difference, but this is the code (with UtilityId first):
ALTER table AccountAddress
ADD CONSTRAINT [PK_AccountAddresses] PRIMARY KEY CLUSTERED (UtilityId,ACCT_NO)
If I want to query the table on UtilityId, would creating an index on UtilityId be beneficial (maybe I need not do that, because the system has a PK defined--by virtue of which indexing is already there).
Conversely, If I want to query on ACCT_NO, which is the second field in the index definition, then, would creating an index on ACCT_NO be beneficial, or not really?
An "ordinary" (SQL Server "relationnal" AKA B-Tree+) index is a sort of vector. And the vector path is compound of all the columns in the key, in the key list order.
The efficiency of seeks depends of the "ways" you try to retrieve your data As an example :
CREATE INDEX X ON T (A, B, C)
Will be efficient for equal seeks in theses cases :
Buth won't be efficient for equal seeks :
But depending of the optimizer, it can do a scan on the index if the cardinality estimator found that there is a few number of rows to return.
If you want to query on ACCT_NO only, you can create a specific index to improve performances.
The efficiency of an index is relative to the predicates you have in your query (WHERE clause, ON clause of joins, HAVING, CASE…). This is called "sargable" (Search ARGument ABLE). As an example LIKE '%anyword%' is never sargable, whatever index you have.