when creating tables on which type of tables should i check the 'by default' option ?
what is it good for ?
when i insert 5 records by default, they get the id 's 1,2,3,4,5. now i insert a record with id= 7, next, when i insert 5 more rows by default, it gives an error on the second insertion, because 7 is already used (by me).
what is it good for when i can't rely on it ?
The best use for creating Identity columns that are
"BY DEFAULT" as opposed to "GENERATE ALWAYS" is
in Replication. For example, one site enters rows where the identity
column starts at 0 while another starts at 1 million. This way when
the rows are replicated to each other site, ther are no conflicts. Granted, that when the first site has entered 1 million rows, there will be a conflict then. The starting points at each site should be far enough apart so that there should be very minimal chance for conflict.
The whole idea of Identity column is to let the DBMS generate the column values automatically. If the application program is doing it also, you are defeating the idea of having the Identity column. In Replication, both sites' DBMS are generating values for the Identity column so they cannot use GENERATE ALWAYS since that would mean the the Identity column value would change when replication occurs.