In SQL Server, identity fields, a number is never reused... deletes do not reset the counter to the next available identity value.
When you think about what goes on under the covers (of SQL), it makes sense. SQL would need to maintain a linked list (or somesuch) of deleted identity values to be able to reassign them. Over time (and lots of random deletes) the cost of maintaining such a list (just to reassign identities and preserve maxID) would be prohibitive. There are other issues such as referential integrity, backout, recovery, efficiency, performance, etc. that are made much easier (efficient) when the db design *never* reuses an identity value within a table. A serial, non-reusable value for identity is the way to go! HTH Dick At 11:27 AM -0200 1/10/02, Waldemiro Junior wrote: >Hi Folks. > >How can I know the next value to be inserted in an Identity field data >type (MS SQL Server)? >When I put this query: "SELECT MAX(COD_REQ) FROM TAUX_001" it returns >the value 10, which corresponds correctly to what its recorded on the >table, but when I insert something in it, after making a select, it >returns the value 101, cause I have already deleted 90 records from this >table. >If I didnĄt make myself clear, please let me know. > >Thanks and best regards. > >Waldemiro Junior >ADD Technologies Web Developer > ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
