Correct way to create indices? SQLServer Express 2005

Hi,

Apologies if this has been asked before, i've done a search but can't find a definitive answer.

I've created a table in an SQLExpress 2005 db using Server Managment Studio Express.

My intention is to use GUID fields as surrogate PK's. I therefore wanted to add a additional index to prevent duplicate records being added to the table. Not having used SQLServer before could someone confirm or deny that this is the correct way to do this. The PK field [EPISODEID{unique identifier}] is set as a non-clustered index. And i've created a second clustered index using the two fields that create a unique record. I've added a screen shot if that is any help.

Thanks

Chris

You can do that, yes. But I would normally leave the PK as the clustered index, since that should be how the majority of your records are located. If it isn't then you need to sit down and think what purpose your surrogate key is supposed to provide.

|||

Motley wrote:

You can do that, yes. But I would normally leave the PK as the clustered index, since that should be how the majority of your records are located. If it isn't then you need to sit down and think what purpose your surrogate key is supposed to provide.

Thanks very much for your reply

I've read up quite a lot of posts and articles regarding the use of surrogate vs natural keys. My primary reason for choosing surrogate keys is the inability to guarantee access to values which would create a natural key that didn't need changing. For example a head injured patient arrives at hospital and is assessed by the team i am working for. This is common as i work for critical care services which include Neurologically impared patients. They need a unique id for that patient but have no access to a possible natural key, the patients NHS number. In this case it's easier to generate a surrogate PK like a GUID which can be used on all the joins required in the database and use an incremental value from a store to replace the NHS number and keep the record unique until the NHS number can be assertained. Even then if the patient is non-uk resident they will never have an NHS number. However if the NHS number is found at a later date the PK can remain intact it's just a case of updating the NHS number field rather than cascading a changing PK through the database.

Anyways,

If i've got this correct the purpose of clustering an index is to create some form of sorting on sequentially related values which helps with retrieving records i.e. dates

If that's correct then as a surrogate key doesn't [or shouldn't] contain data relating to the contents of the record it indentifies it wouldn't matter if it is clustered or not. I think that this would particularly be the case with GUIDs as there is no logical sequence to their creation that would be worth clustering as compared to a sequential integer that might provide a pseudo order of entry index.

The second index i've created i've set as a clustered index as that is the one that holds the data which is used for retrieval i.e. finding all patient episodes within a given date range, so sorting would be beneficial and the unique index constraint should prevent duplication of records.

I've tried to follow the advice given on msdn regarding creating indicies i just wasn't sure if i'd actually done it correctly in Express05. Of course i may have missed the point entirely

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8185.asp

Thanks again

Chris.