Managing latest versions of rows with group by queries and how to best arrange the primary key

I have the following table

| Path          | Version | FirstName | LastName |
| People/Frank  | 1       | Frank     | Smith    |
| People/Frank  | 2       | Frank     | Jones    |
| People/Jack   | 1       | Jack      | Johnson  |    

I'd like my query to return the Path and Max Version for all the rows that match a given criteria.

Currently I'm doing this;

select Path, MAX(Version) as Version from Table where FirstName = 'Frank' group by Path;

This is a really performance critical part of the code and I'm wondering if there's something specific I can do to sql server that would make this quicker or if there's something I'm missing.

Additionally I'd like to make sure I have my constraints defined correctly. I'm expecting the queries to contain any or all of the columns that aren't path and version, so you could in the above case query for either FirstName, LastName or both. My create table sql looks like this:

create table Index_PersonByFirstName(
   FirstName NVarChar(100) not null, 
   LastName NVarChar(100) not null, 
   Path NVarChar(100) not null, 
   Version Int not null, 

   constraint pk_Index_PersonByFirstName primary key(
      FirstName, 
      LastName, 
      Path, 
      Version), 

    constraint uc_Index_PersonByFirstName_Path_Version unique (
      Path, 
      Version), 

    constraint fk_People_Path_Version foreign key (
      Path, 
      Version) REFERENCES People(Path, Version))

Would it make sense to remove the Path from the primary key as that's never directly queried?

Another option I've considered is having a column that indicates if the row is the 'latest' version for a given path and updating the old rows when a new one is written, but that feels icky.

Your thoughts would be greatly appre开发者_如何学Cciated. If I haven't been detailed enough please let me know and I'll add any other information that is required.

---

The query is fine. That's the textbook right way to do it.

The primary key should be the minimum set of fields that uniquely identify a record. Is your example above your actual database or just a simplified or hypothetical example? Because it's pretty unlikely that first name plus last name can be guaranteed to be unique. Are you sure you will never have two "Jim Smith"s? I don't know how the "path" is defined. Maybe that's under your control so that you could guarantee that it's unique.

Don't add fields to a primary key just because this is what you want to sort or select on. Use alternate indexes for that.

Update based on wild guesses about your examples

I don't know what your data means or what you're trying to do. But I'd guess that first and last name are really dependent on the path. That is, you won't have:

path        vers first name  last name
----        ---- ----------  ---------
/foo/fredm  1    Fred        Miller
/foo/fredm  2    Fred        Miller
/foo/fredm  1    Sally       Jones

If that's true, then you really should have a table with Path, First Name, Last Name, maybe other data, and key of Path. Then a separate table with Path and Version and whatever data is dependent on the version.

Otherwise, there is the potential for your database to contain contradictory data. If a Path is supposed to map to only one name, what happens if, by some sort of coding or data entry mistake, you get one record with path "fredm" and name "Fred Miller" and another with path "fredm" and name "Frank Mendel"? Queries expecting them to always be the same could end up picking one at random and giving inconsistent results, or you could get two records where you thought you should have one, etc. One of the prime rules of good database design is: Don't store redundant data.