sql server misc: I'm selecting from TableA into TableB (nothing fancy, just using "insert
into") and TableB has an IDENTITY column. It is important that the rows are
inserted contiguously. If two processes are both calling the stored proc at
the same time and both inserting, say , 1000 rows will sql lock the table
while one is processing before allowing the second process to insert into
the table? Or under these circumstances would it be possible that the two
processes inserted rows in TableB are "interwoven"?

At the moment I'm putting a serialisable transaction around the INSERT just
in case, but I'd obviously much prefer to remove the transaction but I am
unaware how SQL will handle the above scenario.

[quoted text, click to view]

It isn't sensible to use IDENTITY for this IMO. IDENTITY is fine for
surrogate keys but virtually useless for populating any "meaningful"
data in a column. Do you need to see some predetermined serial order
of rows within each transaction? The only fully documented and
reliable method to achieve that is to insert the rows one at a time. I
wouldn't recommend that though.

What you could do would be to allocate the "next" number to each
transaction instead of each row (you could do that with IDENTITY in
another table for example). Then just populate a column with that
value so that it corresponds to a contiguous block of rows at a time.
That way you wouldn't need to serialize your transactions at all.

Alternatively, consider using the ROW_NUMBER() function.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[quoted text, click to view]

Could you elaborate? Do you mean that when the proc is first executed it
will insert the number 1 in a certain field for all records in the insert,
then the second time it will insert the number 2 etc? Thus identidying each
block of inserts? If so I'm doing that anyway.

What I'm ultimately doing is retrieving data in pages so I want to select
where the ID is between two numbers. So if the first block of data starts
its ID at 1000 I want to select the rows where the ID >= 1000 and < 1010.
This obviously requires the IDs to be contiguous.

[quoted text, click to view]

That would be perfect but I'm not using SQL 2005 :(