opc.three (5/13/2013)
The issuance of the IDENTITY column is part of the transaction so we are protected from loss of data.In SSMS, Query Window 1, run this:
USE YourDatabaseName;
DxROP TABLE dbo.StagingTable
CxREATE TABLE dbo.StagingTable (StagingTableID INT NOT NULL IDENTITY(1,1), Col1 sysname);
In Query Window 2, populate the table with some data:
BEGIN TRAN
INSERT INTO dbo.StagingTable
(
Col1
)
SELECT TOP 1000
c1.name
FROM sys.columns c1
CROSS JOIN sys.columns c2;
-- note, I am leaving the transaction open on purpose
In Query Window 3, insert a single row:
INSERT INTO dbo.StagingTable
(
Col1
)
SELECT TOP 1
c1.name
FROM sys.columns c1
CROSS JOIN sys.columns c2;
-- note that the insert succeeds despite the fact
-- that there are 1000 rows trying to, but not yet
-- committed on another SPID
Query Window 4, along comes our SSIS Package wanting to get the MAX ID and copy the data:
SELECT MAX(StagingTableID)
FROM dbo.StagingTable;
Nope, must wait...due to the open transaction in Window 2. Go back to Window 2 and call COMMIT or ROLLBACK, your choice, and you'll see as soon as that happens Window 4 completes immediately and returns 1001. This is the protection we need, i.e. no lost data.
Except this is not what happens when I execute this code in this manner on our 2008R2 instance. After I have executed Queries 1-3, I can see the SPID for the first insert sleeping. However, when I run Query 4, I get a result = 1001. SELECT * FROM dbo.StagingTable gives me the one row I inserted in Query 3. We're running in autocommit mode - are there other database- or instance-level settings that can affect this?
Jason Wolfkill