performance improvements to database table

I have the following sql server 2008 database table:

CREATE TABLE [dbo].[cache](
[cache_key] [nvarchar](50) NOT NULL,
[cache_data] [nvarchar](max) NOT NULL,
[expiry_date] [datetime] NOT NULL) ON [PRIMARY]

I want to add a primary key to it, ie. make the cache_key column the primary key. This column contains unique strings. My question is, are there any implications to making a nvarchar 50 column a primary key? Is it possible to add primary key to this column tha开发者_如何学运维t contains data, even if the cache_key data is unique?

I also have another script that runs each day that removes data from the table based on the expiry_date column. This could mean up to 5000 records deleted based upon comparison to this field. Would it help performance if I created an index on this field?

You can make a primary key out of anything that's indexable and unique. A varchar(50) is no problem. You can add define a primary key after the fact, as long as every record has a unique value in that column. YOu won't be allowed to "primary-ize" a column(s) that isn't unique.

As for the index, if it only ever gets referenced in a single delete query that runs once a day, then don't bother indexing it. The overhead of maintaining the index through every single insert/update on the table won't be worth the microscopic time savings you'd get on the once-a-day delete. On the other hand, if that field is used frequently in where/join clauses in other queries, then go ahead and put on a index - you'll definitely improve performance.

Basically, technically, you can make any column that is less than 900 bytes in maximum size your primary key, e.g. you cannot make a NVARCHAR(2000) your primary key, but a nvarchar(50) works.

The requirements for the primary key are:

• must be unique
• must not be NULL

If those requirements are met - you're good to go.

One thing to keep in mind is this: your primary key is - by default - also your clustering key, the key by which the table's contents is physically ordered (slightly simplified). As such, that clustering key is like the address or pointer of your data row in the table, and it will be included into each and every non-clustered index you have on your table, too.

If you have a table that doesn't have any or just a single non-clustered index - no worries. But if your table has quite a few nonclustered indices (like a Customer table which might have four, five indices or even more), than having such a wide clustering key (100 bytes) of variable width is not ideal. In this case, you're better off using something like an INT IDENTITY as your surrogate key, and put your primary key / clustered index onto that column. It will save you a lot of disk space and make your table perform much better.

Read more about what makes a good clustering key (on a busy, large table) in Kimberly Tripp's blog post Ever-increasing clustering key - the Clustered Index Debate..........again! - highly educational!