I just need a simple way to re-create indexes without major disruption to the site. I supose I should wait for the next major rollout, but there are some that are plain wrong that need fixing sooner rather than later.

The dbForums link is a good'un, thanks ehorn.

Thinking on a bit - can I just change the FILLFACTOR? I want to change it from 90% to default (i.e. 100% but there is a subtle difference between "default" and "100% - maybe I'd be better off with 100% than 90% and can then change to Default later)

If your biggest downside is that its difficult to change FILLFACTOR, how often are you changing this value?

HTH

=================================================================Hear the sledges with the bells - Silver bells! What a world of merriment their melody foretells! How they tinkle, tinkle, tinkle, In the icy air of night!While the stars that oversprinkle All the heavens, seem to twinkle With a crystalline delight; Keeping time, time, time, In a sort of Runic rhyme, To the tintinnabulation that so musically wellsFrom the bells, bells, bells, bells, Bells, bells, bellsFrom the jingling and the tinkling of the bells.

"If your biggest downside is that its difficult to change FILLFACTOR, how often are you changing this value?"

Absolutely agree. However, right now I've found that the FILLFACTOR is broken, and its seriously mucking up the DEFRAGs each day. And we need to scheduled downtime to fix it - but that just is not possible until after the Christmas rush - but because of the Christmas rush we absolutely need to fix the problem :-(

I was kind of absorbed by that circular argument!

For the one table where we found that we could manually recreate the index (before all the user session timed out!) we haven't had to defrag it since - i.e. the Scan Density has been above 90% since. So I'm pretty sure it will make a big difference - I just wish to hell that I'd pursuaded the client to invest in some decent scripts for maintenance rather than letting the Maintenance Plan Wizard change the indexes to 90% fill factor ...

quote:Originally posted by nrIt has a special meaning to sql server and values in it shouldn't be updated.

Can you expand on that please Nigel? I know its an old battle ground for you and I, but this intrigues me.

The model I am working on has a core entity which has a natural key (2 columns). The problem is it can change and is fully audited. At the moment I have added a non changing artifical key for the auditing framework, but am having a hard time in convincing myself to use that key as the FK in the subsequent RI tables. The thing is, only one column in the key will change. Cascading is propogated to certain entities only to ensure temporal correctness (not being able to change an event after it has taken place).

>> The model I am working on has a core entity which has a natural key (2 columns). The problem is it can change and is fully audited.

Is it?It means that the pk doesn't identify the row. If it is audited the row must be identified by the old PK and also reference the new PK - in the audit trail the new pk is an attribute of the old PK.This can't be done by triggers as a trigger will have no way of knowing that a pk has changed.What happens if another row is changed in the same transaction to have a new pk the same as the old pk that has just changed.You can say that the business rules prohibit this but it's not prevented by the database structure if you allow updateable PKs - only by the application.

==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.

"What about doing a full DBCC DBREINDEX and specify the new fillfactor?"

I want to set it to default, but if you specify ZERO to DBREINDEX it leaves the current fill-factor as-is, rather that setting it to "default" as CREATE INDEX ... WITH FILLFACTOR 0 would do :-(

"I don't know of anyone who's happy with the Maintenance Plan Wizard. Sorry to hear that you've been wracked by it, also"

Cheers! We've got our own set of maintenance routines, but we charge for them - it took us a lot of work to develop. We now require this for clients who have their own servers (if they use our shared hosting we chuck it in the mix); but until a little while ago we were lenient on this :-(

1) There is nothing in the relational model that says that a primary key cannot change. The whole point of a primary key is to have a means of uniquely identifying a row in a table. Changing one unique value for another unique value is perfectly legal and acceptable. There are details to remember to deal with. But changing keys is why foreign keys have a CASCADE ON UPDATE option.

2) Triggers absolutely can detect when a primary key has changed:

SELECT t.MyKey FROM MyTable t join Inserted i on i.MyKey = t.MyKey WHERE not exists (select MyKey from Deleted)

3) While it would be possible to have a second update change rowB's PK to have rowA's original PK value, the trigger would have already fired.

4) IN RE >>> We've got our own set of maintenance routines, but we charge for them <<<Crack the whip on these clients. They are paying good money for your advice and expertise. They NEED a different maintenance plan. Maybethey don't need yours, but you'd be doing them a disservice to allow them to keep the one they have.

5) You're going to hear many comments and opinions on forums like this. The forums are a valuable tool. But not everything you hear is gospel.

Can you expand on that please Nigel? I know its an old battle ground for you and I, but this intrigues me.

The model I am working on has a core entity which has a natural key (2 columns). The problem is it can change and is fully audited. At the moment I have added a non changing artifical key for the auditing framework, but am having a hard time in convincing myself to use that key as the FK in the subsequent RI tables. The thing is, only one column in the key will change. Cascading is propogated to certain entities only to ensure temporal correctness (not being able to change an event after it has taken place).

Any ideas welcome.

DavidM

I have run into the same issues, and my conclusion was that the "natural" key that I first used as pk, was not stable enough. Stability is one criteria of pk's.In those cases I use artificial keys as pk ( and use that for RI ), the "natuaral" key is degraded to "unique constraint" with not nulll columns of course.PK not stable -> not good pk.

Yes but they can't find the before and after values of the row.It is a logical delete and insert soI feel should be coded that way.

>> 1) There is nothing in the relational model that says that a primary key cannot change.No, but if a PK uniquely identifies a row then changing it means that row no longer exists and you have a new row i.e. a delete and insert rather than an update. Just because something is allowed doesn't mean it's a good idea (cursors?).

==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.

Yes but they can't find the before and after values of the row.It is a logical delete and insert soI feel should be coded that way.

>> 1) There is nothing in the relational model that says that a primary key cannot change.No, but if a PK uniquely identifies a row then changing it means that row no longer exists and you have a new row i.e. a delete and insert rather than an update. Just because something is allowed doesn't mean it's a good idea (cursors?).

2) It is true that you can't guarantee matching the old and new PK in a multi-row update. You could with single row update and under certain conditions in a multi-row update.

1) I hear what you're saying; just don't agree. If I change the number on my house so that I have a new address, does this mean that I really have a new house? Heck, no. It's the same house. It just has a new means of differentiating it from the other houses.

At this point we're just splitting hairs.

Your opinion is that primary keys should not be changed. I've heard your defense of this premise. I remain unconvinced. I am convinced, however, you will find a way to live without my approval.

>> If I change the number on my house so that I have a new address, does this mean that I really have a new house?

If the number identifies the house then yes. You would only have the same house if there is some other means of identifying differences in houses other than the number which is more important. Of course you don't have to make the connection between a house identifier and a PK in a database but if you do then a number not identifying the house means that it shouldbn't be the pk.

>> I am convinced, however, you will find a way to live without my approval.Already found.Your point of view is valid, I just feel that it can cause problems that could easily be avoided. If you feel that the problems will never arise or are prepared to deal with them then that's fine.I feel I'm probably in the minority here - buts that's never caused me to lose sleep.

==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.

Lets say on average 15% of the rows will undergo a key update. Only one column in the composite key is ever updated. 75% of the updating rows will be updated only once. Cascading is restricted to 3 tables, while the other RI related tables do not cascade ie: If any data makes it into these no cascading tables, any attempt at a key update will fail.. that's part of the business process it self..

So.. by using an artificial key, I can eliminate any cascading to those three table, plus multi-row update auditing is easier. But I do not want to lose the natural key to the non cascading tables.

That makes 4 candidate keys for this table... I have a unique constraint on all of them at the moment, but am yet to decide on the "Primary Key"