Are there any big disadvantages with what i've done here for generating id's ?
Table with 3 cols:
CREATE TABLE [dbo].[IdGenerator] (
[TtypeId] [int] NOT NULL ,
[TypeDescription] [varchar] (50) NOT NULL ,
[NextId] [int] NOT NULL
The use an sp to get the next id:
CREATE PROCEDURE dbo.GenerateId
(@TypeId int,
@Id int OUTPUT)
AS
BEGIN
BEGIN TRANSACTION GetId
SELECT@Id = NextId
FROMIdGenerator
WHERETypeId = @TypeId
UPDATE IdGenerator
SETNextId = @Id + 1
WHERETypeId = @TypeId
COMMIT TRANSACTION GetId
RETURN @Id
END
GO
I understand it may be a little slow for large systems as there will be waits but the system i use will not be very busy so it there any other disadvantages?