StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>EDIT</strong> this is basically the method purposed by @Quassnoi, I just implement it in a loop so you can run it against multiple windows at the same time to see it works great.</p> <p>set up:</p> <p>create user's existing table:</p> <pre><code>create table Subscriber ( recid int not null primary key ) </code></pre> <p>create new table to keep track of the missing identity, you could add an extra column to keep track of a table if this is needed for multiple tables, but I hve not done that in this example:</p> <pre><code>CREATE TABLE SubscriberIDs ( SubscriberID int ) insert into SubscriberIDs values (0) --row must exist first </code></pre> <p>create test script, put this into multiple windows and run them at the same time:</p> <pre><code>declare @idtable table --will hold next ID to use ( id int ) declare @x int declare @y int set @x=0 while @x<5000 --set up loop begin set @x=@x+1 begin transaction --get the next ID to use, lock out other users UPDATE SubscriberIDs SET SubscriberID= SubscriberID+ 1 OUTPUT INSERTED.SubscriberID INTO @idtable --capture the next id from temp table variable select @y=id from @idtable --print @y --use the next id in the actual table insert into subscriber values (@y) commit --print @x waitfor delay '00:00:00.005' end --while </code></pre> <p><strong>---------------------------------------------------------------</strong><br> <strong>EDIT</strong> here is my original attempt, which will eventually get some deadlocks when run in a loop and in multiple windows at the same time. The above method always works. I tried all combinations of transactions, with(holdlock), and set transaction isolation level serializable, etc. but could not get it to run as well as the above method.</p> <p>set up:</p> <pre><code>create table subscriber ( recid int not null primary key ) </code></pre> <p>used to capture the id:</p> <pre><code>declare @idtable table ( id int ) </code></pre> <p>the insert:</p> <pre><code>insert into subscriber OUTPUT INSERTED.recid recid INTO @idtable SELECT ISNULL(MAX(recid),0)+1 FROM subscriber </code></pre> <p>list the new id:</p> <pre><code>select * from @idtable </code></pre> <p>list all the ids:</p> <pre><code>select * from subscriber </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload