Concurrent Access - New Record, Primary Key problem....

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For example,
user 1 creates a new record and manipulates it within a transaction making
use (perhaps) of the @.@.IDENTITY value when creating other, related records.
Before this transaction is complete, user 2 creates a new record and does
the same thing. Presumably they will both have the same @.@.IDENTITY? If
this is the case, how is it possible to manage such a situation?

Thanks."Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
> Can someone explain what happens when two users concurrently attempt to
> create a new record in a table with an autonumber primary key? For
> example, user 1 creates a new record and manipulates it within a
> transaction making use (perhaps) of the @.@.IDENTITY value when creating
> other, related records. Before this transaction is complete, user 2
> creates a new record and does the same thing. Presumably they will both
> have the same @.@.IDENTITY? If this is the case, how is it possible to
> manage such a situation?
> Thanks.

No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY in
Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make sure
it is declared as a PK when you create the table.

Simon|||Ok that simplifies things somewhat. Yes, the columns in question are both
Identity and Primary Key.

Thanks very much for your reply.

Robin

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ebea66$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
>>
>> Can someone explain what happens when two users concurrently attempt to
>> create a new record in a table with an autonumber primary key? For
>> example, user 1 creates a new record and manipulates it within a
>> transaction making use (perhaps) of the @.@.IDENTITY value when creating
>> other, related records. Before this transaction is complete, user 2
>> creates a new record and does the same thing. Presumably they will both
>> have the same @.@.IDENTITY? If this is the case, how is it possible to
>> manage such a situation?
>>
>> Thanks.
>>
> No, each session will have a different value, so there's no problem with
> concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY
> in Books Online.
> Note that just defining a column as an identity column is not enough to
> guarantee uniqueness - you can still create duplicates manually (see SET
> IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make
> sure it is declared as a PK when you create the table.
> Simon

Concept for View

Hi,
Could someone please explain to me when the view is updatable and when it
is not? I am under the impression that if the two tables are joined togethe
r
in the view, then the view is not updatable... is it correct?
ThanksSee the CREATE VIEW in the Books Online <tsqlref.chm::/ts_create2_30hj.htm>
for a detailed description of when a view is updatable.
For an updatable view containing joins, INSERT/UPDATE/DELETE statements are
allowed as long as only one underlying base table is affected. For example:
CREATE TABLE Table1
(
Col1a int NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
Col1b int NOT NULL
)
GO
CREATE TABLE Table2
(
Col2a int NOT NULL
CONSTRAINT PK_Table2 PRIMARY KEY,
Col2b int NOT NULL
)
GO
CREATE VIEW MyView
AS
SELECT Col1a, Col1b, Col2a, Col2b
FROM Table1 AS t1
JOIN Table2 AS t2 ON
t2.Col2a = t1.Col1a
GO
--succeeds
UPDATE MyView
SET Col1b = 1
GO
--fails
UPDATE MyView
SET Col1b = 1, Col2b = 1
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:864C5D55-3A5D-4F97-ABF1-9DAE7B057B33@.microsoft.com...
> Hi,
> Could someone please explain to me when the view is updatable and when it
> is not? I am under the impression that if the two tables are joined
> together
> in the view, then the view is not updatable... is it correct?
> Thanks