Re: Update Identity Column

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I moved every table I was able to move to the SQL ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... communication between ODBC (OLEDB and Native Client, ...
    (microsoft.public.sqlserver.connect)
  • It can be Done
    ... I just installed a 3 SQL Server 2005 instances on a 2 node Active/Passive cluster. ... IWiz will then offer you a choice of Group on where you can install teh Fail Over Clustered Instance of SQL. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Unable to Apply SP4 to SQL 2000 Cluster (new Node)
    ... Rebuild the node in the failover cluster. ... Scenario 1" in SQL Server 2000 Books Online. ... This setup process updates to SP4 only the binaries on the new ...
    (microsoft.public.sqlserver.clustering)
  • Re: eliminate unnecessary joint in the view
    ... select val1 from view1x2x3 ... There are situations where SQL Server will realise that a table is not ... CREATE TABLE Table1(Key1 int NOT NULL PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)
  • Re: WSS 3.0 question
    ... I followed the advise given in removing WSS 3.0 etc, ... the server is complaining that the SQL service(?) was tempered with or corrupt. ... I may just instal the SQL server as I was going eventuall use it anyway. ... If WSUS 3.0 is installed, I would suggest you uninstall it and then you install WSS 3.0. ...
    (microsoft.public.windows.server.sbs)