Here's something I came up with. Not sure it's bullet-proof, but maybe it will give you

some ideas. Sorry that it's a bit of a kludge and not very elegant... :crazy:

First some sample data:

IF OBJECT_ID('dbo.eventLog') IS NOT NULL

DROP TABLE dbo.eventLog

CREATE TABLE dbo.eventLog

(

sessionID INT NOT NULL

,logID TIMESTAMP NOT NULL

,logText VARCHAR(1000) NULL

,created DATETIME NULL

,PRIMARY KEY CLUSTERED (sessionID, logID)

,UNIQUE (sessionID)

)

;WITH cteSampleData (sessionID,logText,created)

AS

(

SELECT 1001,'George Washington','2013-03-31' UNION ALL

SELECT 1002,'John Adams','2013-02-28' UNION ALL

SELECT 1003,'Thomas Jefferson','2013-02-15' UNION ALL

SELECT 1004,'James Madison','2013-02-01' UNION ALL

SELECT 1005,'James Monroe','2013-01-31' UNION ALL

SELECT 1006,'John Q Adams','2013-01-22' UNION ALL

SELECT 1007,'Andrew Jackson','2013-01-13'

)

INSERT INTO dbo.eventLog

(sessionID,logText,created)

SELECT

sessionID

,logText

,created

FROM

cteSampleData

SELECT

*

FROM

dbo.eventLog

Now of course the following insert will fail (just to set up a base case)

;WITH cteNewData (sessionID,logText,created)

AS

(

SELECT 1001,'Ronald Reagan','2013-05-31'

)

INSERT INTO dbo.eventLog

(sessionID,logText,created)

SELECT

sessionID

,logText

,created

FROM

cteNewData

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'UQ__eventLog__23DB12CA731B1205'. Cannot insert duplicate key in object 'dbo.eventLog'. The duplicate key value is (1001).

The statement has been terminated.

Now the rest of the code below takes some explaining.

The first block of code will do an insert. But FOR TESTING I've put in a

wait state so that you can flip over to another window and insert a

DIFFERENT row while the first is waiting. This is to simulate what might

happen if the next ID has been assigned but before the insert completes

a second insert attempt trying to use the same value.

So this code will INSERT a new row and "hold" that value even if another

insert takes place in the interim. It does this by inserting only the sessioID

and then coming back and doing an update even if another row has been

inserted behind it. To see how this works, before running the code, create another

window with the second insert command. Then run the first script and while it's waiting

flip over and insert a record (or two or more) from the second page. After the wait state

expires (I made it 15 secs for testing) you can go back to the first page and

see that the first row has been updated properly. The only minor anomaly I

see is that the timestamp column value may not be in order.

WINDOW 1:

DECLARE

@NextID INT

BEGIN

-- get the next id

;WITH NextID(sessionID)

AS

(

SELECT

MAX(sessionID)+1

FROM dbo.eventLog

)

INSERT INTO dbo.EventLog(sessionID)

SELECT

sessionID

FROM

NextID AS n

SELECT TOP(1)

@NextID = SessionID

FROM

dbo.EventLog

WHERE

logText IS NULL

ORDER BY

logText

--for testing

SELECT @NextID AS NextID

/* Put in a wait state for testing only */

WAITFOR DELAY '000:00:15' -- 15 sec delay for TESTING ONLY!

--WHILE WAITING, GO TO SEPARATE WINDOW AND RUN AN INSERT--

UPDATE dbo.eventLog

SET

logText = 'Ronald Reagan'

,created = GETDATE()

WHERE

sessionID = @NextID

END

SELECT

*

FROM

dbo.eventLog

WINDOW 2:

/* Simulate a second simultaneous insert */

/* while waiting for the first insert */

DECLARE @NewID INT

SELECT

@NewID = MAX(sessionID)+1

FROM

dbo.eventLog

;WITH cteNewData (sessionID,logText,created)

AS

(

SELECT @NewID,'Bill Clinton','2013-01-31'

)

INSERT INTO dbo.eventLog

(sessionID,logText,created)

SELECT

nd.sessionID

,nd.logText

,nd.created

FROM

cteNewData AS nd

SELECT

*

FROM

dbo.eventLog