Id (indentity) is increments on faults.

Hi,

When i eg. manually ad entries to a table and, cancels the insert Ms SQL
increment the counter on the ID anyway. Is there a way to avoid this
behavior?

Regards
AndersFlare (dct_flare@.hotmail.com) writes:
> When i eg. manually ad entries to a table and, cancels the insert Ms SQL
> increment the counter on the ID anyway. Is there a way to avoid this
> behavior?

Yes, don't use the IDENTITY property, but roll your own. IDENTITY works
that way by design. By grabbing one number which never has to be
rolled back, insertions into tables with IDENTITY columns can scale
better.

One way to get a key on your on is:

BEGIN TRANSACTION

SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl (UPDLOCK)

INSERT tbl (id, col1, col2, ...)
VALUES (@.id, @.par1, @.par2, ...)

COMMIT TRANSACTION

The UPDLOCK is required to avoid that two processes grab the
same id.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

I want to jump to an entry specified by parameter, doable as bookmark?

I know I can do to just show 1 entry

select * from table where no = @.parameter_no

but is it possible to actually select ALL entries (I have 250 for now) in the list report, but jump to a particular # based on parameter?

Say I have ID 1 ~ 250 (select * from table) in DataSet, but when I enter 220 in parameter, the report still selects all 250 entries but jump to the 220 entry?

I have bookmark set up on each entry

Thanks for any help. I noticed in my search that jump to a report + bookmark isn't supported, does my situation fall under that?

Hello

Did you ever figure out how to do this? If so, would you care to share?

Thanks

sql

I want to jump to an entry specified by parameter, doable as bookmark?

I know I can do to just show 1 entry

select * from table where no = @.parameter_no

but is it possible to actually select ALL entries (I have 250 for now) in the list report, but jump to a particular # based on parameter?

Say I have ID 1 ~ 250 (select * from table) in DataSet, but when I enter 220 in parameter, the report still selects all 250 entries but jump to the 220 entry?

I have bookmark set up on each entry

Thanks for any help. I noticed in my search that jump to a report + bookmark isn't supported, does my situation fall under that?

Hello

Did you ever figure out how to do this? If so, would you care to share?

Thanks