Auotincrement without using autoincrement

Hi Lee,
have you thought about using a counter table. Normally something like
Create table Counter (TableName sysname, IDValue int)
What you do is for each table in your database that requires a increasing
number set the tablename and IDValue to 0 then in a transaction you update
the value then retrieve it do your inserts and then commit
i.e
begin transaction
declare @.ID as int
update Counter IDValue=IDVale +1 where TableName='mastertable'
select @.ID=IDValue from Counter where TableName='mastertable'
-- no do your inseertts
insert into other tbales using the @.ID
..
...
commit transaction
Because the counter table is locked during the update no one can get the
next value. If the transaction aborts the the update is rolled back. Also
it's quick as you don't have to go through the larger table to get the max
(even with an index). Obviously you would have a clustered index on the
TableName column or a covering index on both columns,...
Lee don't be too bothered about Joe's (CELKO) comments . He has been a bit
bashed lately (in the forum) and turned a bit rabid. His comments are way
off the mark. It would of been nice to see such expert in the field of SQL
give help instead of put down someone asking for help.
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Lee Gillie" <Lee@.nospam.odp.com> wrote in message
news:uxoaocRnFHA.2904@.TK2MSFTNGP14.phx.gbl...
>I have a master table which I have been updating in multiple queries to
>create a new row. It has a primary key, call it WorkID. I can't make it
>autoincrement. And it is more complicated because...
> WorkID can also appear in two other tables, as references to the master
> record, but I need to provide for the master record being gone, and as
> such the records in the other two tables are orphaned. That is fine.
> To create a new master record I select MAX(WorkID) unioned on all 3
> tables, and increment the result, and this is my new WorkID. However, even
> if in a transaction, this query work can result in duplicate WorkID
> numbers when new masters are created in extremely rapid succession. I see
> the flaw here. Don't think this approach can be salvaged when the MAX and
> INSERT queries are done separately.
> I have been racking my brain to find a way to deal with this WITHOUT
> resorting to auto-increment on the master table. I can think of no
> expression for an INSERT value which will take the MAX+1 of WorkID on the
> three tables, but it seems there must be a way to do this in a single
> statement. Something like:
> INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
> Maybe I need a new single row, single column table that arbitrates new
> WorkID values? Or maybe someone has a favorite trick?
> Thx - LeeGreg -
I like it!
I appreciate ALL the replies. I learned some stuff, so I got better at
my job, and my project is nearer the BRICK HOUSE I am striving for.
Thanks everyone for taking a moment to consider this and sharing your
thoughts.
Also... I never let anyone else's bad day bring me down. Life is way too
short. But even so, if you are open to it, you can learn something from
anyone.
Best regards - Lee
Pay it forward!|||The problem here (other than slightly incorrect syntax :) is that you are
single threading all inserts in your system. They all have to wait on the
transaction to complete to get the next value. Serious hog on concurrency.
An idea I have used just have a table for getting surrogate values if you
can afford to do this one row at a time and keep the table pared down:
create table counter
(
counterId int identity(1,1) primary key
)
go
insert into counter
default values
select scope_identity()
---
1
Here at least there will be no single threading. A delete job could keep
this table really small well over 1500 should fit on a single page so just
delete all but the last few hundred. Make sure you get a good plan on that
query because if you table scan you will make everyone wait on the clean up.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"GregO" <grego@.community.nospam> wrote in message
news:uUxOwCXnFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hi Lee,
> have you thought about using a counter table. Normally something like
> Create table Counter (TableName sysname, IDValue int)
> What you do is for each table in your database that requires a increasing
> number set the tablename and IDValue to 0 then in a transaction you update
> the value then retrieve it do your inserts and then commit
> i.e
> begin transaction
> declare @.ID as int
> update Counter IDValue=IDVale +1 where TableName='mastertable'
> select @.ID=IDValue from Counter where TableName='mastertable'
> -- no do your inseertts
> insert into other tbales using the @.ID
> ..
> ...
> commit transaction
> Because the counter table is locked during the update no one can get the
> next value. If the transaction aborts the the update is rolled back.
> Also it's quick as you don't have to go through the larger table to get
> the max (even with an index). Obviously you would have a clustered index
> on the TableName column or a covering index on both columns,...
> Lee don't be too bothered about Joe's (CELKO) comments . He has been a
> bit bashed lately (in the forum) and turned a bit rabid. His comments are
> way off the mark. It would of been nice to see such expert in the field
> of SQL give help instead of put down someone asking for help.
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Lee Gillie" <Lee@.nospam.odp.com> wrote in message
> news:uxoaocRnFHA.2904@.TK2MSFTNGP14.phx.gbl...
>|||Hi Louis,
I agree about the delay in my solution but sometimes you need to be sure and
wait for a transaction. In the solution I put forward you have the other
bonus of never missing a number. Unlike identity and doing without a
transaction (not that the user said this was an issue)
Either way if the transactions are small then the delay shouldn't be an
issue
kind regards
Greg O
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eZ04akinFHA.3316@.TK2MSFTNGP14.phx.gbl...
> The problem here (other than slightly incorrect syntax :) is that you are
> single threading all inserts in your system. They all have to wait on the
> transaction to complete to get the next value. Serious hog on
> concurrency.
> An idea I have used just have a table for getting surrogate values if you
> can afford to do this one row at a time and keep the table pared down:
> create table counter
> (
> counterId int identity(1,1) primary key
> )
> go
> insert into counter
> default values
> select scope_identity()
> ---
> 1
> Here at least there will be no single threading. A delete job could keep
> this table really small well over 1500 should fit on a single page so just
> delete all but the last few hundred. Make sure you get a good plan on
> that query because if you table scan you will make everyone wait on the
> clean up.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "GregO" <grego@.community.nospam> wrote in message
> news:uUxOwCXnFHA.1148@.TK2MSFTNGP12.phx.gbl...
>|||That is true, just wanted to make sure that you realized that no more than
one user at a time could insert data into any table using your system. If
you can live with that, it is .
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"GregO" <grego@.community.nospam> wrote in message
news:OFavurinFHA.2444@.tk2msftngp13.phx.gbl...
> Hi Louis,
> I agree about the delay in my solution but sometimes you need to be sure
> and wait for a transaction. In the solution I put forward you have the
> other bonus of never missing a number. Unlike identity and doing without
> a transaction (not that the user said this was an issue)
> Either way if the transactions are small then the delay shouldn't be an
> issue
> kind regards
> Greg O
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eZ04akinFHA.3316@.TK2MSFTNGP14.phx.gbl...
>