Re: Update Identity Column

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)


"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ulFGMpN6HHA.5212@xxxxxxxxxxxxxxxxxxxxxxx
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

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@xxxxxxx> wrote in message
news:OUWKWON6HHA.484@xxxxxxxxxxxxxxxxxxxxxxx
"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:er$3DPE6HHA.5424@xxxxxxxxxxxxxxxxxxxxxxx
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
--






.

Relevant Pages

  • Re: Update Identity Column
    ... Actually, I implemented the trigger, and it seems to be working fine. ... Sylvain Lafontaine, ing. ... SQL Server will almost invariably decide that that's the best order to ... INSERT INTO tbl VALUES; ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Linked Tables - ODBC SQL Server Driver Timeout Expired
    ... THANK YOU Sylvain for your help! ... Go to SQL Server Configuration Manager ... TCP/IP Properties: ... I need to use the SQL Server driver instead of the Native Client driver ...
    (microsoft.public.sqlserver.odbc)
  • Re: SQL Express - Identity specification property - how to change
    ... UPDATE tbl ... You can use ALTER TABLE ALTER COLUMN to add an IDENTITY column, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: SQL question
    ... JOIN tbl b ON a.UserID = b.UserID ... INSERT statements with sample data. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Getting list of recently added IDENTITY items
    ... FROM tbl WITH ... COMMIT TRANSACTION ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
Loading