Sql-server – Is Primary Key Non Clustered recommended

clustered-primary-keyprimary-keysql server

I am designing a database table.
Columns are RouteId ,Origin , Destination.

RouteId is a string, it is always unique but length will be more than 60 characters.

Since RouteId is unique, I am thinking to make it Primary key and since the size is large (nvarchar(1000)) I am thinking to make it non clustered index.

My Question: Is it a best practice to make Primary Key (nVarchar(1000)) non clustered index? Will it have effect on performance? What is the best practice in this situation

Best Answer

In a general sense, the Clustered Index should be what is used most often to access and sort the data. A major factor in deciding what to use for a Clustered Index is that the key field(s) will be copied into all Nonclustered Indexes on that table.

The main concern for choosing a PK is finding the field(s) that uniquely identify a row, never being NULL, and preferably never changing. And given that this field will be copied into related table so that the child tables can be Foreign Keyed back to this PK, you generally want something smaller as any large field will have a multiplying affect on disk space. An additional consideration to keep in mind for string fields is the Collation since you are not going to want to JOIN on a field while doing a case-insensitive comparison, as that will be far less efficient than using either a case-sensitive collation or even better would be a binary collation (though the best option is still usually one of the integer types: TINYINT, SMALLINT, INT, or BIGINT).

That all being said, this question is nearly identical to this one:

Primary Key choice on table with unique identifier

And my answer would generally be the same.

BUT, there is one very important difference here: an index can only hold 900 bytes, hence your NVARCHAR(1000) is invalid for being indexed. Unless, of course, the data is never more than 450 characters, but in that case, the field should be NVARCHAR(450) instead of NVARCHAR(1000).

So, create an INT NOT NULL IDENTITY(1, 1) {TableName}ID field and use that as the Clustered PRIMARY KEY.

If you are curious about the total impact of using a large field in either a Clustered Index or Primary Key (given that those values are copied into Nonclustered Indexes and/or child tables), I wrote an article detailing the down-stream effects of this decision:

Disk Is Cheap! ORLY?

Related Question