Two Problems
-
Hi there,
FIRST PROBLEM:
Can some one tell me the syntax to re-calculate the values of the IDENTITY Column.
E.g
ID Value
1 100
3 300
4 400
6 600
In the above example ID is an IDENTITY column and suppose i have deleted the records of ID 2 & 5. Now i want to recalculate the ID as follows.
ID Value
1 100
2 300
3 400
4 600
SECOND PROBLEM:
I have some 100+ views in my DB. Some views are used in other views. Now the problem is when i generate the script of the views through Enterprise Manger, the script of the dependant views is generated after the views in which they are used. Than i have to manually write the script of the dependant views before the views in which they are used.
Now i want to ask is there any way to generate the script of dependant objects before the objects in which they are used.
-
First problem: No you don't want to do that..
Assuming that ID is the primary key? It should *not* have any meaning, especially since it's supposedly just an arbitrary number. You do not want to reassign your primary keys just because there are holes or gaps in the sequence.
If, though.. this is not your pk, and that you in fact do have some good reason for this 'work around shuffling', I'd suggest that you as a first step remove the identity property and roll your own counter functionality instead. By implementing your own 'sequence-numbering' scheme you'll get full control over how to use it and change it. I'd like to know more about the reason to your request, though. It strikes me as an odd thing to do.
Second problem: Unfortunately, there is no working method presently to find out objects dependecies in a reliable way. sp_depends et al has never worked and can't be relied upon. I'm afraid you're stuck with manual 'eyeballing' the scripts and rearranging when necessary.
/Kenneth
-
First of all thanks for humiliating
Ne ways...i asked the first problem just for my knowledge
-
Well, humiliation wasn't intended
Anyways, short answer to prob 1 is - you can't. Can't update an identity - period. What you could do, is that for each and every row delete it and re-insert it with the new id-value, after setting IDENTITY_INSERT to ON. (only works for inserts, not updates) It's nothing I'd recommend in a production environment, though..
Small tip (no humiliation intended) it's pretty easy and quick to try out things one wonders about in QA. Sometimes the messages coming back is clear and understandable.
Also, Books On Line is a very good source of information.
-- quote BOL --
If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to ensure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON.
-- end quote --
/Kenneth
-
2nd problem. Steve Jones posted a link in one of his editorials for a tool from Red Gate software that tracks the dependencies.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply