sql - Adding identity column - order of ID -

please see ddl below:

create table #test (name varchar(10),dateofbirth datetime)  insert #test values ('ian', '1976-09-08') insert #test values ('maria', '1976-09-08') 

i realise poor design choice not have primary key. want add new identity column this:

alter table #test add id int identity 

this works expect i.e. ian has id of 1 , maria has id of 2.

there 10 million rows in table. can assume id order respect order entered table? e.g. ian entered first has id of 1. anne millionth row entered id 1,000,000.

the order in identity values assigned existing rows undefined. heap example, sql server assign values in physical order of files/pages/slots used store rows. if matches order of insertion, happenstance , should not relied on. space allocation , reuse can cause physical order of data differ original order of insertion may database multiple files.

if table has clustered index, values assigned in order of clustered index key, again, don't count on it. relational database table logically unordered set of rows , should treated such.

if have incremental column derive insertion order, can use identity function in select...into...order by query create new table identity values assigned in desired sequence. identity values new rows inserted going forward incremental in order of insertion.