Record count begin with big number

[Deamond]

Hi I have a Table with Automatic ID numbers...
In access I delete All records by hand

When I add new record with delphi (SQL)
the number ID of record count begin with last record+1
and not with 0

Someone know the statement to reset that?

thanx for any help

[Joe Celko]

>> Hi I have a Table with Automatic ID numbers...
In access I delete All records [sic] by hand. When I add new record
[sic] with delphi (SQL) the number ID of record count begin with last
record+1 [sic] and not with 0 <<

Rows are not records; columns are not fields and tables are not files.
What about leaving the max number in the table?

DELETE FROM Tickets
WHERE ticket_nbr < (SELECT MAX(ticket_nbr) FROM Tickets);

I assume that you are using "(SELECT MAX(ticket_nbr) FROM Tickets) + 1"
to get the next number.

This woudl avoid the proprietary, non-relational, non-deterministic
IDENTITY "feature" in T-SQL.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"Deamond" <bariko@des_oon.net> wrote in message
news:Qs*******************@news.siol.net...

Hi I have a Table with Automatic ID numbers...
In access I delete All records by hand

When I add new record with delphi (SQL)
the number ID of record count begin with last record+1
and not with 0

Someone know the statement to reset that?

thanx for any help

I guess you're talking about a table with an IDENTITY column? if so, then
the easiest way to do this is TRUNCATE TABLE - that will delete all the
rows, and also reset the identity seed.

An alternative is to use DBCC CHECKIDENT (see Books Online), which will
allow you to set the seed to any value you want.

Simon