Update Identity Column

[Robert Morley]

Hi everyone,

I realize that this is a "you should never need to do this" situation, and
technically I don't NEED to do it, but can I update an Identity column? If
so, how? I thought SET IDENTITY_INSERT might let me do it, but that doesn't
apply to UPDATE statements, it would seem.


Thanks,
Rob

 

[Sylvain Lafontaine]

Easy: delete the old record and insert a new one with the desired identity
value.

 

[Robert Morley]

I had relationships depending on one of the columns, so that wasn't
desirable. In the end, I figured out another way, which was simply to turn
off the identity flag for the column, adjust the number, then turn it back
on. It's a one-time thing, so not a big deal that it was a manual process.

Thanks for the idea anyway!



Rob

 

[Sylvain Lafontaine]

Hum, to my knowledge, there is no such thing as in identity flag that can be
switched ON/OFF. When you do this in EM, what EM is doing is to remove all
relationships, create a new table, copy to old data into it, delete the old
table, rename the new one and re-etablish the relationships. Fast and easy
to do if you have a small table but could be a killer in the case of a big
table or when there is a lot of users on the system.

Personally, having any identity column that is doing anything more than
simply beeing the primary key for a table and/or keeping the relationship
between tables is calling for trouble. Many years ago, I stopped doing this
and since then, more than half of my problems with the development of
databases has vanished away.

 

[Robert Morley]

The primary key actually serves no purpose whatsoever. In this case,
however, I wanted to add an entry to the table that would be at the very
beginning of the table so that when others besides me opened it, they'd see
that entry first (assuming they opened it with no sorting, and that the
default ordering by primary key took effect). I tried to make most of the
other fields use data that would come first/last in almost any sort as well.

Anyway, what happened was that I did all that, but initially put it in as
just a regular new record in the table, then realized I'd been stupid and
that insertions would always come after it, so I wanted to move it so that
its primary key was 1, without having to go through the annoyance of
deleting all the related records and re-inserting them.

The problem was that programmers who should have been coming to me for
additions and such were just guessing at what they needed to do in a
database they were completely unfamiliar with, and causing no end of
problems. Said programmers are also all server admins for that server. So
I added a dummy entry to say "*** For additions to this table, please
contact Robert Morley ***" as part of the field data (resisting the urge to
put something like "*** don't make changes to the data in my database
without contacting me, you !$^%# morons! ***").

It's a sucky way to do things, I'll admit, but when the server admins are
making additions to the data without contacting the DBA, what else are you
going to do?!? (Yes, it would be nice if I could educate them, but the
turn-over in that group is phenomenal, and nobody really knows WHAT they're
doing there any more! The person I educate today will probably be gone
tomorrow.)

Oh and yes, I'm aware of what EM is most likely doing in the background, but
what I did accomplished what I wanted in an easy manner, so I described the
front-end changes that I used to accomplish the task, not the
behind-the-scenes tasks going on.


Rob

 

[David Portas]

The primary key actually serves no purpose whatsoever. In this case,
however, I wanted to add an entry to the table that would be at the very
beginning of the table so that when others besides me opened it, they'd
see
that entry first (assuming they opened it with no sorting, and that the
default ordering by primary key took effect). I tried to make most of the
other fields use data that would come first/last in almost any sort as
well.

There is no such thing as "default ordering by primary key" as far as SQL
Server is concerned. The ordering produced by a query without ORDER BY is
undefined. Possibly you meant that such an ordering is enforced by some
other application you use but I just thought it worth mentioning that SQL
Server will not automatically do it for you. AFAIK Access will not either
unless you explicitly set the sort order property of the view / query.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Robert Morley]

While it's true that the sort order is undefined, having a clustered index
on the primary key (as most of my tables do), the data will normally be
sorted and stored physically in that order, and therefore SQL Server will
almost invariably decide that that's the best order to retrieve it in. I
should have been more clear that I was referring to a clustered index as
well as it being the primary key. It's still obviously possible that it
might not show the records in that order, but I've done the best I can with
what I have available.

In the end, there's nothing I can do to force someone to read that line, but
I can certainly do my best to make it noticeable, and having it show as the
first or last row in most views of the data seemed like a good place to
start.



Rob

 

[David Portas]

While it's true that the sort order is undefined, having a clustered index
on the primary key (as most of my tables do), the data will normally be
sorted and stored physically in that order, and therefore SQL Server will
almost invariably decide that that's the best order to retrieve it in.

Not true. Not even "almost invariably" true:

CREATE TABLE tbl
(x INT NOT NULL PRIMARY KEY CLUSTERED,
z INT NOT NULL UNIQUE);

INSERT INTO tbl (x,z) VALUES (1,30);
INSERT INTO tbl (x,z) VALUES (2,20);
INSERT INTO tbl (x,z) VALUES (3,10);

SELECT x,z FROM tbl;

Result (YMMV):

x z
----------- -----------
3 10
2 20
1 30

(3 row(s) affected)

I agree with Sylvain. Ascribing any significance to the ordering of IDENTITY
column values is just folly.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Robert Morley]

You're right, the table is unsorted in the SQL 2K EM (either your example or
my actual table), though I'm sure it used to come up sorted previously.
It's not like I open that particular table directly all that often, so
perhaps it was conicidence or some such.

But regardless of that, as I said, what else was I to do here? I wanted to
take every possible precaution to ensure that in most possible sorts and/or
views of the data, somebody would notice the line that said to contact me
before adding to the data, so I wanted to include even the identity in that.
It's no guarantee, I'll grant, but it's better than trusting that anyone who
ever accesses my data will ignore that column as much as I normally would.
As you say, nobody SHOULD assign any importance to an Identity column...but
that doesn't mean that nobody WILL.

And as to that, I have never assigned any more importance to Identity
columns than anybody else ever has. I've been a DBA for about 15 years now
(not all of it SQL Server, mind you), so believe me, I know my way around.
It sucks that I would have a desire to ever take this kind of measure, but
it should tell you how often I've had to do it before that I had to ask how
to do it (and even then, the first response was not an ideal solution).

But now you've got me thinking...perhaps my approach to this was wrong.
Since I'm the only one who will ever add data to the table, perhaps I should
add a trigger to the table that effectively forbids anybody not named Robert
Morley from doing so (not even the server admins). An admin could still
remove the trigger in the event that I get hit by a bus (even though I work
from home), but only someone bent on adding data to the table would actually
be able to do so, and would likely contact me unless there was a REALLY good
reason not to. Hmmm...something to look into.



Rob

 

[Sylvain Lafontaine]

The lack of an easy way to add comments to a table or a column in SQL-Server
is a real problem.

Maybe you could create a table named __Help!!__READ_ME!!!__ with your
comments in there?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

You're right, the table is unsorted in the SQL 2K EM (either your example
or my actual table), though I'm sure it used to come up sorted previously.
It's not like I open that particular table directly all that often, so
perhaps it was conicidence or some such.

But regardless of that, as I said, what else was I to do here? I wanted
to take every possible precaution to ensure that in most possible sorts
and/or views of the data, somebody would notice the line that said to
contact me before adding to the data, so I wanted to include even the
identity in that. It's no guarantee, I'll grant, but it's better than
trusting that anyone who ever accesses my data will ignore that column as
much as I normally would. As you say, nobody SHOULD assign any importance
to an Identity column...but that doesn't mean that nobody WILL.

And as to that, I have never assigned any more importance to Identity
columns than anybody else ever has. I've been a DBA for about 15 years
now (not all of it SQL Server, mind you), so believe me, I know my way
around. It sucks that I would have a desire to ever take this kind of
measure, but it should tell you how often I've had to do it before that I
had to ask how to do it (and even then, the first response was not an
ideal solution).

But now you've got me thinking...perhaps my approach to this was wrong.
Since I'm the only one who will ever add data to the table, perhaps I
should add a trigger to the table that effectively forbids anybody not
named Robert Morley from doing so (not even the server admins). An admin
could still remove the trigger in the event that I get hit by a bus (even
though I work from home), but only someone bent on adding data to the
table would actually be able to do so, and would likely contact me unless
there was a REALLY good reason not to. Hmmm...something to look into.



Rob

 

[Robert Morley]

Actually, I implemented the trigger, and it seems to be working fine. Not
an ideal approach, but it should work, and includes my name & contact number
in the error message.


Rob

The lack of an easy way to add comments to a table or a column in
SQL-Server is a real problem.

Maybe you could create a table named __Help!!__READ_ME!!!__ with your
comments in there?