I have a table with four columns that are all non-nullable, and the data is such that all four are needed to distinguish a unique record. This means that if I were to make a primary key, it would need to comprise all columns. Queries against the table will almost always be to pull back a single record, i.e. all columns will be filtered in the query.
Since every column will need to be searched, does having a primary key benefit me at all (besides enforcing uniqueness of records)?
In your case these fields are natural key.
Surrogate Key:
Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table. Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).
Natural Key:
Keys are natural if the attribute it represents is used for identification independently of the database schema. What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.
Surrogate Keys vs Natural Keys for Primary Key
I prefer to add surrogate key to separate business and database model management. Other question is using clustered and nonclustered index on primary key.. If you table changes (non static table, it has high intensive inserts or update), you will get problem with performance in case of using clustered index on non-monotonic increased key.
It is usually recommended that you have a surrogate key in such situations, so foreign keys in other tables (and any record references that may be stored externally, such as if they are carried on query strings where a http(s) request refers to one of the records) have something to refer to that will not change if the data in the row changes. If you do this then that would be your primary key.
If you don't add such a surrogate key, then given how you describe the data being access having all four columns as the primary key would not be a disadvantage. If you make the key the clustered index for the table it will help such requests as there will be one level in the b-tree on disk to go down to find the data for a given row.
Composite keys as primary keys also run into index size issues that can affect disk usage, io speeds, and backups. You might want to review Kimberly Tripp's posts about primary keys and clustered indexes here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-again!.aspx
I too would suggest a surrogate key in this case instead of a natural one.
If you have a table representing a many-to-many relationship which has just 2 columns, it seems reasonable.
Cf. this SO question
But I admit, that I add Surrogate Keys even in those cases.
