The system is working as designed. The identity column keeps incrementing the number to use. so if you do 100 inserts then delete 90 records the next ID used will be 101.
The question is, why do you need this number? The usual places where I've seen it needed is when you are doing an insert into 1 table then using the ID fields value as a foreign key in another table. When I need to do this I do the insert in the main table then return the value of the @@IDENTITY system variable. This variable should contain the identity value of the last insert (provided you do it IMMEDIATELY after the insert). Then you can use the returned value for the inserts into other tables. ______________________________________________________ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX: 301.424.3696 649 North Horners Lane E-Mail: <mailto:[EMAIL PROTECTED]> Rockville, MD 20850-1299 WWW: <http://www.euservices.com> ______________________________________________________ > -----Original Message----- > From: Waldemiro Junior [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 10, 2002 8:27 AM > To: CF-Talk > Subject: SQL Help > > > 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 > > ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
