Sql-server – Need advice on Primary and Clustering Keys of a table

I hate to state the obvious but I'd say test both scenarios and run your production queries against all 3 scenarios (scenario 1 being the current non partitioned one). The reason I state this is because I don't know what your code is querying. Do they actually have a benefit of being sorted in the base table by the identity column as opposed to the other column? For example, are your queries actually looking for the row ID often? If you're not sure, you might be surprised by getting some performance benefits. The general idea of using ID as the clustered key was for range scans, but in our case, we scan by customerID and Date, so it worked out perfectly for us, and perhaps you. Check out this article by Kim Tripp: http://www.sqlskills.com/blogs/kimberly/post/The-Clustered-Index-Debate-Continues.aspx

"What is often cited as the “reason” for IDENTITY PRIMARY KEY clustered index definitions is its monotonic nature, thus minimizing page splits. However, I argue that this is the only “reason” for defining the clustered index as such, and is the poorest reason in the list. Page Splits are managed by proper FILLFACTOR not increasing INSERTS. Range Scans are the most important “reason” when evaluating clustered index key definitions and IDENTITies do not solve this problem.Moreover, although clustering the IDENTITY surrogate key will minimize page splits and logical fragmentation due to its monotonic nature, it will not reduce EXTENT FRAGMENTATION, which can cause just as problematic query performance as page splitting. In short, the argument runs shallow "

Typically you want your clustered index to be as narrow as possible, unique, and non nullable as it get's carried into all other indexes. I have a table with roughly 10 billion rows and we partition off the datetime column, which has worked out great.

As name is unique and will never change, it is certainly a good candidate key from a relational theory point of view.

You might find using a integer surrogate key preferable for space and performance reasons though as it will take less space than the text in every table that has a foreign key to this table (and every index as FKs are usually indexed columns). Operations to search/join on an integer column will be faster too, though for queries that join in this table and need to output and/or sort by the name you might find the extra work going from id to name removes some of that benefit. Of course the space and performance differences may not be significant enough to make a difference to your project at which point this comes down to preference.

In fact I suspect even using a UUID might be faster than using name, assuming your DBMS has a proper UUID type so they are stored in compact binary form not as a text field, despite the type's fixed 16 byte length probably being longer than the average length of your name values, as comparisons against fixed length binary values are faster than those between variable length strings (it would be interesting to benchmark this and see if the difference is indeed significant) - though unless you have reason to use a UUID (replication issues or such) the smaller integer type will of course be more efficient and take a quarter of the space.

tl;dr: the name column as described is a perfectly good candidate key, perfect in theory in fact, but in practise an integer surrogate key will be more space and processing efficient.