arturmariojr (2/24/2011)

---

But now you will have as many tables as you have keys in original table and, for each increment on some key, you will add a new register in the key-table... if you have thousands of increments by day, you will have thousands of new registers and only the last one is neccessary.

May be the solution could be:

CREATE PROCEDURE [dbo].[USP_Get_Value_For_Key]

(

@key NVARCHAR(50),

@value INT OUTPUT

)

AS

BEGIN

begin transaction

SELECT @value = 0

UPDATE tbl_kvp SET column_value += 1 WHERE column_key = @key

SELECT @value = column_value FROM tbl_kvp WHERE column_key = @key

commit tran

END

The outer procedures don't need to implement the transaction for get the last id.