Uncle Bill likes to entice programmers with proprietary functions.
Some of them are just so easy to use that many of us cannot help fending off the fear of vendor lock-in
to take the bait, and enjoy the food in captivity:)
Here is one of the rare dishes that looks so fresh and simple. The sequential number generator is derived from the conventional Name/Number pair concept but without using an explicit lock, at least on the surface.
1) Create a table:
CREATE TABLE [dbo].[Sequence](
[SequenceName] [varchar](16) NOT NULL,
[SequenceNumber] [bigint] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Sequence]
2) Create THE proprietary stored procedure:
CREATE PROCEDURE [dbo].[GetSequenceNumber]
, @BatchSize INT
SET NOCOUNT ON;
DECLARE @NextSequenceNumber BIGINT
SET @NextSequenceNumber = SequenceNumber = SequenceNumber + @BatchSize
WHERE SequenceName = @SequenceName
3) Try it out:
DECLARE @return_value int
EXEC @return_value = [dbo].[GetSequenceNumber]
@SequenceName = N'SequenceName1',
@BatchSize = 7
SELECT 'Return Value' = @return_value
P.S. For dessert, it would be nice if Uncle Bill can serve something like: SELECT... OFFSET m LIMIT n. It will make paging much easier.