I use a separate table for the autoinc, because optionally users can use their own numbering.
Besides, I also have a unique key in the table that is handled differently by the framework, and when using autoinc I was forced to use the numeric key as unique field.
In the business class it was very easy to use another table to handle the numbering, and gives me more flexibility. When doing straight updates in the SQL database that turns out to be a disadvantage.
>Why not just use an autoinc int field and let SQL Server handle it internally?
>
>>I wonder if it is possible to insert a new row and select the result in the same command?
>>I have a table that keeps track of incrementing ID numbers (auto inc) and like to get a new ID at the same time.
>>I don't want to create a SP because it would be easier to maintain if I can keep it in the same statement, but like to run one statement only.
>>
>>This does not work:
>>Select max(emID) From EmpId Where (Insert into empID (emKey) values (NewID()))
>>
>>It gives "Incorrect syntax near the keyword 'Insert'"
Christian Isberner
Software Consultant