4

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

3

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?

1

There are specific circumstances where it's advisable to have a clustered index which is different than the primary key, and I think your table is an example.

Specifically you want the clustered index to be something which is ever increasing, so that new rows are inserted at the end. An incrementing number (each new row is one greater than the previous one) or a timestamp (each new row has a value greater than the previous) are good examples. This avoids inserts hitting the middle of the index and causing data to be rearranged.

With your example I'm guessing it's unlikely that RouteId will increase, but rather will be random in terms of sequence?

You're also correct about data size, as the clustered index will be referenced in all non-clustered indexes applied to the table. If you have a wide/large clustered index key then the other indexes will be larger than necessary, degrading performance and using more disk space.

1

Although most people equate PRIMARY KEY with the CLUSTERED INDEX, these are separate concepts. A PRIMARY KEY has the characteristics Non-NULL and Unique, but of course other indexes can have those properties as well.

If you have no actual CLUSTERED INDEX, then the database will be a heap. This will likely affect performance. So, normally I would recommend having a CLUSTERED INDEX as well.

If this is a one-server implementation (as opposed to multiple servers linked together in some fashion) thenI would suggest something like this:

  1. Create a CLUSTERED INDEX column using IDENTITY to just generate an ascending integer (or a bigint) to have a small unique key per row. This will help improve performance in some areas.
  2. Create a PRIMARY KEY which would automatically enforce the Non-NULL and unique requirements. (Thanks to srutzky for correcting my description.)

The integer CLUSTERED INDEX will provide a small key for use in joins, referential integrity, and so forth. This makes all the joined tables smaller than if you used the longer NVARCHAR(1000) as the technical CLUSTERED INDEX.

By the way, the longest index in SQL Server is 900 bytes. You mentioned 60 characters in a column defined a NVARCHAR(1000). I assumed that was just over definition and it would be smaller. I suggest trimming that down to a more reasonable and usable length that SQL Server can absorb.

So, as long as you can see that PRIMARY KEY and CLUSTERED INDEX are separate concepts, you can make the appropriate decision for each use case.

  • A PRIMARY KEY is by definition "unique" so it wouldn't need a UNIQUE INDEX on it. Also, wouldn't it make more sense to focus the PK on what will be used in JOINs as that value will be the one copied to other tables to be the FK field? It doesn't need to be the referenced field, but a referenced field does need to be UNIQUE so in the non-PK Clustered Index would need to be declared as UNIQUE in order to be referenced by an FK. And I agree that PK and Clustered Index are separate concepts, but here it's irrelevant as NVARCHAR(1000) is too large to be indexed ;-). – Solomon Rutzky May 27 '15 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.