is this possible

hey all,
i have a transaction table i need to add a record to. the primary key is a 2
field column. RecordID and then the SequenceID.
How i understand it is:
To add a new record i have to
1. Find the last sequence number used
2. Then add the new record
can i do this in one stored procedure?
thanks,
rodcharWhy not use an IDENTITY column?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||Again provide DDL...
Create Table (RecordId Int, SequenceId Int
, Constraint Primary Key (RecordId, SequenceId))
Insert Table(RecordId, SequenceId)
Select RecordId, Max(SequenceId) + 1
From Table
Group By RecordId
BTW, this does have issues in a multi-user environment. If two people were t
o
execute this function at exactly the same time, they'll get the same answer
and
thus a collision. A better way would be to make a small table that stores th
e
last value used.
Thomas
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same
answer and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
..which would have the same issue -- what would stop two readers from
getting the value simultaneously?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||The problem is that this is an existing table in production.
"Adam Machanic" wrote:

> Why not use an IDENTITY column?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> 2
>
>|||Just increase Isolation Leel to Repeatable Read (or Serializeable) to preven
t
this issue from arising...
"Thomas" wrote:

> Again provide DDL...
> Create Table (RecordId Int, SequenceId Int
> , Constraint Primary Key (RecordId, SequenceId))
> Insert Table(RecordId, SequenceId)
> Select RecordId, Max(SequenceId) + 1
> From Table
> Group By RecordId
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same answe
r and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
>
> Thomas
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
>
>|||If you can withstand the entire table being locked during the insert process
,
this would also be a viable choice.
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to prev
ent
> this issue from arising...
> "Thomas" wrote:
>|||If you use a small table that stores the next value, you can lock the table
and
increment the "next" value. In essence, serializing the retrieval of the nex
t id
value. However, it does mean you may get gaps.
Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OI6wTkcQFHA.1884@.TK2MSFTNGP15.phx.gbl...
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> to
> answer and
> the
> ...which would have the same issue -- what would stop two readers from
> getting the value simultaneously?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to
prevent
> this issue from arising...
How would that prevent issues?
QA Window 1:
--
use tempdb
go
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
QA Window 2:
--
use tempdb
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
Serializable blocks only if writes have taken place.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
> "Thomas" wrote:
>
were to
answer and
stores the
is a 2|||Adam,
Yes with just a read, but if you use the relation from t he select as
Insert values, you are doing more than just a read, and qry window 2 will
block..
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Wait here while you run Qry WIndow 2 --
Commit Tran
-- ******************************
--Query Window 2
--
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Now go back and commit Query Window 1
-- ---
"Adam Machanic" wrote:

> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> prevent
> How would that prevent issues?
> QA Window 1:
> --
> use tempdb
> go
> create table x(id int)
> go
> insert x values (1)
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> QA Window 2:
> --
> use tempdb
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> Serializable blocks only if writes have taken place.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> were to
> answer and
> stores the
> is a 2
>
>