Re: mssql: insert into syntax

Hugo Kornelis (hugo@xxxxxxxxxxxxxxxxxxxxxx) writes:
> Your mention of preserving order when rows are deleted makes me think
> that you want to use IDENTITY to get a ranking. In that case: don't. The
> only thing MS guarantees about IDENITY is that it will be a unique value

Eh, André says he does not want to use IDENTITY, so you tell him not to
use it?

Anyway, if you say:

INSERT tbl (...)
SELECT ...
ORDER BY ...

and tbl has an IDENTITY column, the message I have, is indeed that there
is a guarantee that the IDENTITY values will reflect the ORDER BY clause.

However, this does not apply to SELECT INTO.

In any case, it is obvious from Andre's description of his business problem
that he should stay away from IDENTITY.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

.

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: Update Identity Column
    ... is a real problem. ... 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: 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 Humor
    ... Hugo Kornelis wrote: ... SQL Server will often produce the same execution for both versions. ... the big questions for the RM, *for me* seem to include view updatability which has to do with the operators of the algebra as well as whether a relational engine can implement customary features such as concurrency control and presentation coherence without being written in a language that eschews the relational operators. ...
    (comp.databases.theory)
  • 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)