I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a way dumbed down example, and the syntax may not be correct as I am rushing and way more interested in the answer to the underlying issue then the code to do it...
CREATE TABLE dbo.CodeNumbers( CodeNumbersID INT IDENTITY (1,1) NOT NULL PRIMARY KEY, CodeValue VARCHAR(30) NOT NULL IsUsed BIT NOT NULL DEFAULT(0) );
dbo.CodeNumbers gets populated with a monthly CSV provided, the import method of your choice is already written to get them in there. We are NEVER given a duplicate code.
Lets assume for arguments sake we have 10,000,000 rows in the table. that all follow this format when imported:
1, 'ajdirjfisofklrlfo039402', 0 all the way till 10000000, 'fkeiir9489', 0
Now in dbBar I have 2 stored procedures the first should access the first un-used code in dbFoo, return it in an out variable and mark it as used. So I have something like:
CREATE PROCEDURE GetNextUseableCode @CodeOut VARCHAR(30) OUTPUT, @CID INT OUTPUT AS SELECT @CID = CodeNumbersID, @CodeOut = CodeValue FROM dbFoo.dbo.CodeNumbers WHERE IsUsed = 0 UPDATE dbFoo.dbo.CodeNumbers SET IsUsed = 1 WHERE CodeNumbersID = @CID
The code that calls the procedure from dbBar is accessed by 200k sessions a day at various times. When
dbFoo.Codes has no more to return, its fine all is well and good, the application is simply told sorry no more check back tomorrow.
I have 3 main questions..
Is there anything special I would need to have in the code to avoid race conditions, and if so what would be best to handle for this without bringing the system to its knees.
Is their an efficient way to ensure the next code grabbed whenever the procedure is called, is the next one in chronological order within the ID column.
Is there any other concerns that I am not looking at right now that could issue big problems, and what would be an eloquent way to handle this situation?
I understand this is a long pretty open ended question, I have some coded up solutions, but I feel like there are much better ways to get the results I want.
Thanks in advance as always for all help.