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.

copying a table and all of its objects

Is there any way, using t-sql, to copy a table, all its indices, contraints,
etc? The contraints and indices would have to have a different name, of
course, if they remain in the same database, as would the table.
What I'm trying to do is replicate a table in every way, except foreign
keys, so that I can restore it if needed. If my customer runs a routine in
his windows app that appends rows to an important table, but realizes that
he shouldn't have (for whatever reason), I want to create a backup before he
runs his routine and provide a means of restoring that backup if he realizes
his error, if something goes wrong in the middle (electrical problems, etc).
So I want to make my exact copy first and then be able to restore it
subsequently.
I know how to do this with some tedious and complicated sp's, but I was
hoping there'd be an easier way.
Thanks for any help.
Bernie YaegerBernie Yaeger (berniey@.cherwellinc.com) writes:
> Is there any way, using t-sql, to copy a table, all its indices,
> contraints, etc? The contraints and indices would have to have a
> different name, of course, if they remain in the same database, as would
> the table.
> What I'm trying to do is replicate a table in every way, except foreign
> keys, so that I can restore it if needed. If my customer runs a routine
> in his windows app that appends rows to an important table, but realizes
> that he shouldn't have (for whatever reason), I want to create a backup
> before he runs his routine and provide a means of restoring that backup
> if he realizes his error, if something goes wrong in the middle
> (electrical problems, etc). So I want to make my exact copy first and
> then be able to restore it subsequently.
> I know how to do this with some tedious and complicated sp's, but I was
> hoping there'd be an easier way.
Rather than answering your question, I think we should look at alternate
ways to handle this situation.
First, with proper transaction handling, electric outages should not be
a problem. If the routine is interrupted, nothing should be committed
until all is over. That is, once you bring the machine up again, SQL
Server till automatically rollback the transaction for you.
Next, assuming that once the routine is completed, someone realizes
that he messed up, and wants to restore, SQL Server offers a solution
for this, and therre is a third-party solution. Both of them requires
that you run with the full or bulk-logged recovery model.
SQL Server offer point-in-time restores. That is, you restore a full
backup, and then apply the transaction log to the point in time just
before the mistaken operation. As you may guess this also wipes out
any other activity that took place simultaneously or after the user
mistake.
The 3rd party solution is to rely on Lumigent Log Explorer
(www.lumigent.com). With Log Explorer you can examine the transaction
log for what happened, and you can also have Log Explorer to generate
SQL statements that revokes the operations that should not have occurred.
Of course, if you expect this to be a very common scenario that you
will need to restore data, then you need to develop something application-
specific. But in such case I would have some flag columns that tells
me whether a inserted row is approved. Updated and deleted rows I would
copy to a shadow table, or just set a status bit for "delete_pending" on.
Such a bit would have to be part of the primary key, to handle updates.
Note that your intented solution of restoring the entire table, has the
same problem as point-in-time resotres: you lose all activity in the
table.
Yet one solution is to put the table on a filegroup on its own. Then you
can restore that filegroup only, but I'm lukewarn for that solution.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi Erland,
Tx for your reply.
Lots of what you say is of course quite relevant. I will look into several
of your suggetions.
Bernie
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9466ADEF7E64DYazorman@.127.0.0.1...
> Bernie Yaeger (berniey@.cherwellinc.com) writes:
> > Is there any way, using t-sql, to copy a table, all its indices,
> > contraints, etc? The contraints and indices would have to have a
> > different name, of course, if they remain in the same database, as would
> > the table.
> >
> > What I'm trying to do is replicate a table in every way, except foreign
> > keys, so that I can restore it if needed. If my customer runs a routine
> > in his windows app that appends rows to an important table, but realizes
> > that he shouldn't have (for whatever reason), I want to create a backup
> > before he runs his routine and provide a means of restoring that backup
> > if he realizes his error, if something goes wrong in the middle
> > (electrical problems, etc). So I want to make my exact copy first and
> > then be able to restore it subsequently.
> >
> > I know how to do this with some tedious and complicated sp's, but I was
> > hoping there'd be an easier way.
> Rather than answering your question, I think we should look at alternate
> ways to handle this situation.
> First, with proper transaction handling, electric outages should not be
> a problem. If the routine is interrupted, nothing should be committed
> until all is over. That is, once you bring the machine up again, SQL
> Server till automatically rollback the transaction for you.
> Next, assuming that once the routine is completed, someone realizes
> that he messed up, and wants to restore, SQL Server offers a solution
> for this, and therre is a third-party solution. Both of them requires
> that you run with the full or bulk-logged recovery model.
> SQL Server offer point-in-time restores. That is, you restore a full
> backup, and then apply the transaction log to the point in time just
> before the mistaken operation. As you may guess this also wipes out
> any other activity that took place simultaneously or after the user
> mistake.
> The 3rd party solution is to rely on Lumigent Log Explorer
> (www.lumigent.com). With Log Explorer you can examine the transaction
> log for what happened, and you can also have Log Explorer to generate
> SQL statements that revokes the operations that should not have occurred.
> Of course, if you expect this to be a very common scenario that you
> will need to restore data, then you need to develop something application-
> specific. But in such case I would have some flag columns that tells
> me whether a inserted row is approved. Updated and deleted rows I would
> copy to a shadow table, or just set a status bit for "delete_pending" on.
> Such a bit would have to be part of the primary key, to handle updates.
> Note that your intented solution of restoring the entire table, has the
> same problem as point-in-time resotres: you lose all activity in the
> table.
> Yet one solution is to put the table on a filegroup on its own. Then you
> can restore that filegroup only, but I'm lukewarn for that solution.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp