Re: SQL Server 2000: Identity columns guaranteed sequential?

Tech-Archive recommends: Fix windows errors by optimizing your registry

10,000 rows is not that much. I'm wondering if a staging table would work
here. Let's say you have an input file or staging table like so:

Smith, John, 123 Main St
Smith, Mary, 123 Main St
Bloggs, Joe, 456 Pine Ave
....

Mary & John are married (though Mary thinks John has been cheating on her
and wants a divorce, but I digress). Thus, you want to import Mary and John
as separate members, but have only one address and then link them. Joe
lives alone (but has his eyes on Mary, once the divorce comes through). You
can do multiple passes - one for addresses, one for members, and one for
links:

begin tran

insert Addresses
select distinct
Address
from
Staging s
where not exists
(
select
*
from
Address a
where
a.Address = s.Address
)

insert Members
select
Surname
, Given
from
Staging s
where not exists
(
select
*
from
Members m
where
m.Surname = s.Surname
and
m.Given = s.Given
)

insert MemberAddress
select
m.MemberID
, a.AddressID
from
Staging s
join
Members m on m.Surname = s.Surname
and m.Given = s.Given
join
Address a on a.Address = s.Address
where not exists
(
select
*
from
MemberAddress ma
where
ma.MemberID = m.MemberID
and
ma.AddressID = a.AddressID
)

commit tran

One Mary divorces John and shacks up with Joe, then her AddressID in
MemberAddress can be updated accordingly. ;-)


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"JimLad" <jamesdbirch@xxxxxxxxxxx> wrote in message
news:3db26135-6251-41f5-9ab3-ef1a5503d5ce@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 10, 3:05 am, "Jeffrey Williams" <jeff.williams3...@xxxxxxxxxxx>
wrote:
Not sure if you mentioned whether or not you are on 2005 or still on 2000.
But, on 2005 you can use OUTPUT from your insert statement to output what
you need.

Jeff

"Tom Moreau" <t...@xxxxxxxxxxxxxxxxxxxx> wrote in message

news:u0zUsoQ6JHA.5932@xxxxxxxxxxxxxxxxxxxxxxx



I think you need to figure out why you need sequential ID's. Typical
applications don't. The purpose of a primary key is uniqueness, and an
identity is a cheap way of doing it. Sure, you get gaps, but so what? If
you need sequential numbers for display, that's easy enough to do. What
happens to your app if you successfully add rows and then later remove
some?
You'll get gaps then.

Setting IDENTITY_INSERT on and off can be problematic. There are
permission
issues:

"Execute permissions default to the sysadmin fixed server role, and the
db_owner and db_ddladmin fixed database roles, and the object owner."

So, you wouldn't have regular users executing this. Also, only one table
can have this property set at a time.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"JimLad" <jamesdbi...@xxxxxxxxxxx> wrote in message
news:7d1aaf38-d4bd-4a49-b3d9-8f9eea14071c@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 9, 12:50 pm, "Tom Moreau" <t...@xxxxxxxxxxxxxxxxxxxx> wrote:
I'm thinking that a trigger may do it for you. Consider this. You want
to
insert into the identity-containing table and then use the generated
ID's
to
insert into the 2nd table. During the INSERT on the first table, you
can
access the inserted virtual table. You can then use the inserted table
to
pick up the ID's and insert them into the 2nd table. Here's a snippet:

create trigger tri_MyTable on MyTable after insert
as
if @@ROWCOUNT = 0
return

insert MyOtherTable (ID)
select
ID
from
inserted
go

No cursors were used - nor were any cute, furry animals harmed - in the
production of this solution. ;-)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau

"JimLad" <jamesdbi...@xxxxxxxxxxx> wrote in message

news:65cfbc5c-6bc7-421d-8f22-a5d9fa19f2a5@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 9, 10:08 am, JimLad <jamesdbi...@xxxxxxxxxxx> wrote:

On Jun 8, 6:12 pm, "Tom Moreau" <t...@xxxxxxxxxxxxxxxxxxxx> wrote:

That's not what I said. All inserts are atomic. Period.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON
Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau

"JimLad" <jamesdbi...@xxxxxxxxxxx> wrote in message

news:eeccb432-8f2e-4005-b871-6c885d2dcecd@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 8, 4:47 pm, "Rick Byham, MSFT" <rick...@xxxxxxxxxxxxx>
wrote:

To explain the rollbacks, identity columns are not guaranteed to
have
no
gaps.If the last identity value was 300, then connection A starts
an
insert:
BEGIN TRANSACTION <A>
INSERT transaction A is assigned 301

Then connection B starts an insert:
BEGIN TRANSACTION <B>
INSERT transaction B is assigned 302
COMMIT TRANSACTION <B>

Then
ROLLBACK TRANSACTION <A>

Now your table will contain 299, 300, 302, ...
This is by design to improve performance.
--
Rick Byham, MSFT
(Implies no warranty or rights)

"Tom Moreau" <t...@xxxxxxxxxxxxxxxxxxxx> wrote in message

news:OKEctTE6JHA.480@xxxxxxxxxxxxxxxxxxxxxxx

The INSERT is atomic. However, there is no guarantee that
concurrent
INSERTS each have contiguous blocks of identity values. As long
as
they
are
unique, there is no problem. For example, I wouldn't be
completely
surprised to see the following inserted results:

-- Insert 1
1
2
3
10
11
12

-- Insert 2
4
5
6
7
8
9

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"JimLad" <jamesdbi...@xxxxxxxxxxx> wrote in message
news:403511d1-42ab-47f3-bef0-14b6eb58837c@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 8, 1:40 pm, "Tom Moreau" <t...@xxxxxxxxxxxxxxxxxxxx>
wrote:
I don't believe it is guaranteed that they will not interlace.
If
you
absolutely need to guarantee that, then you'll have to go with
setting
the
TRANSACTION ISOLATION LEVEL to SERIALIZABLE. However, you will
get
poor
performance in a high load scenario.

Also, keep in mind that any rollbacks will leave gaps in the
identities.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON
Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau

"JimLad" <jamesdbi...@xxxxxxxxxxx> wrote in message

news:135e14d4-1cb6-46c3-8068-e94990c1b21a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

SQL Server 2000 question.

Can I please have confirmation that INSERT statements to
Identity
Columns are guaranteed to be sequential? i.e. that I can
safely
use
SCOPE_IDENTITY() and @@rowcount to work out what ids were
inserted
and
then update the FK in a linking table (with order by clauses
of
course)?

Also that 2 multirow INSERT statements run at the same time
can't
interlace their identity ids - I'm assuming this would break
ACID.

I'm 99% sure, but someone has raised it and I need
confirmation.

Cheers,

James

Hi Tom,

Oh dear! I don't want to hear that!

Are you sure? Surely single multirow INSERT statements are
atomic
and
must be executed completely to ensure ACID compliance?
Interlacing
of
ids on the identity column shouldn't be possible should it? I
don't
see how isolation level would affect that.

James
MCDBA, MCSD, MCITP- Hide quoted text -

- Show quoted text -

Hi guys,

Fine. I understand rollbacks and gaps. I understand that 2 inserts
statement may not be contiguous.

I understood that a single multirow insert statement would be
atomic?
Tom seems to be saying that each row insert within the statement is
atomic, but not the overall INSERT statement? Is that correct? In
which case, I have been mistaken for some years.

Tom is on the money with his last post. However there are a lot of
posts around which assume that the id values will be sequential
within
a single statement. Indeed I have never seen or heard of them not
being sequential. Tom is mooting this situation, although from what
he
is saying he has never actually seen it.

So, can someone else shed light on this? Is the situation Tom
proposes
possible or not?

Cheers,

James- Hide quoted text -

- Show quoted text -

Hi,

Yep, soz, going off on one there.

But question still stands. Has anyone ever seen this happen? Can
anyone confirm or deny what Tom is saying?

-- Insert 1
1
2
3
10
11
12

-- Insert 2
4
5
6
7
8
9

Cheers,

James- Hide quoted text -

- Show quoted text -

Hi Tom,

You are
correct:http://www.sqlservercentral.com/Forums/Topic456664-149-1.aspx
The only way I found to stop interlacing was a TABLOCKX hint on the
INSERT statement, with the obvious performance implications. Note that
HOLDLOCK and SERIALIZABLE hints do not help in this case.

So, going back to my real world problem: I want to insert data into 2
related tables - one which references the other on an IDENTITY PK. Up
until now I have been using a temp table with an identity column,
SCOPE_IDENTITY() and @@rowcount with ordered statements to work out
the created ids and insert them into the related tables. If I can't do
this anymore, what is the alternative? Hopefully not cursors!

Cheers,

James- Hide quoted text -

- Show quoted text -

Thanks Tom.

I have come up with another idea which I have put in the other post.

BEGIN TRAN
SELECT MAX(DeleteMeID) FROM DeleteMe WITH (HOLDLOCK, UPDLOCK)
--Then the inserts...
COMMIT

This will prevent anyone else INSERTING data into the table until the
end of the transaction, BUT crucially it doesn’t prevent SELECTs on
the table or UPDATEs and DELETEs on anything except the last row. This
has some performance issues, but

...

read more »- Hide quoted text -

- Show quoted text -

Hi,

Nice tip on the OUTPUT clause, but yes I am still using 2000 (but
hopefully going to 2008 in the next 6 months - woohoo stop blunting my
skillset!).

Here's the scenario:

We are importing member addresses during the day on an operational
database (i.e. other users on the system). We are in the security
context of the user and can't change it cos we're in 2000. There is
the main table addresses and the linking table member_addresses. I
need to insert the records into addresses and then insert the created
identity address_id and the member_id into the the linking table. I've
got 10000 records to do and I don't want to use a cursor.

The crucial fact is that there may be duplicate addresses (i.e.
spouses at the same residence) so there can't be any linking back
after the first insert.

Oh, and it's all in a transaction.

So, how would you do it? Comments on the design not welcome. Just
assume I know it's less than ideal.

Am I carrying my anti-cursor campaign too far?

Cheers,

James

.

Relevant Pages

  • Re: Backing out of an Ebay auction when its finished?
    ... In effect this is exactly what has happened in the transaction the OP ... member has paid, the PP member has an address end of story. ... first ever ebay purchase does that make me dodgy? ... unverified Paypal account and are more likely than older members to have ...
    (uk.legal)
  • Re: Transactions in ADO.Net 2 strongly-typed datsets.
    ... you need to first execute ... GroupMembers, Updates on Group members, and wrap these up by calling Deletes ... specify the transaction property to all the IUD commands. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Contact Member function
    ... i have just checked the ebay help files on auction interference: ... Members may not contact a seller and offer to buy a listed item outside ... transaction to warn them away from a seller or item. ...
    (alt.marketing.online.ebay)
  • Re: Contact Member function
    ... >i have just checked the ebay help files on auction interference: ... > Members may not contact a seller and offer to buy a listed item outside ... > the eBay online marketplace. ... > transaction to warn them away from a seller or item. ...
    (alt.marketing.online.ebay)
  • Re: Execution Plan Hinting
    ... another possible workaround might be to: ... bulk insert into a staging table ... COMMIT TRANSACTION ...
    (comp.databases.ms-sqlserver)