February 28, 2010 at 1:25 am
RBarryYoung (2/28/2010)
Cool. That would be my choice, simpler.
Quite. The pooled keys would be allocated from a Sequence Table, naturally 🙂
March 1, 2010 at 1:39 pm
If I am not misreading the original request, I think you are trying to get multiple pseudo-identity columns in a single table.
This is a somewhat squirrely approach but it'd probably work for you as long as possible holes in your sequences are not a problem.
This code is based on a discussion some time back (it may have been here at SSC; does anyone still have the link?) about doing pretty much what you wanted-- except for the "multiple columns" requirement. Each column that needs its own pseudo identity value needs its own SneakyIdentity table... Ideally you'd lock it down and document the bejeebers out of it so nobody stumbles in and tries to figure out what an empty table is doing in the system. (and potentially mungs the identity value) Add appropriate error trapping. You may want to put the code shown in a USP.
-- create the table to start
-- create table SneakyIdentity(rowid bigint identity PRIMARY KEY)
-- drop table SneakyIdentity
-- insert bumps "next identity row" but the rollback prevents the row from getting saved
begin transaction
insert into SneakyIdentity DEFAULT VALUES
rollback transaction
-- IN USP, this would actually be an OUTPUT parm so wouldn't need to declare here...
declare @uid bigint
select @uid = Scope_Identity()
select @uid Retval
-- the table stays empty
select * from SneakyIdentity
March 1, 2010 at 3:26 pm
That is sneaky! How would you manage the locking and multi-user aspects of this, ie to prevent thousands of calls per second retrieving the same ID?
March 1, 2010 at 3:53 pm
peter-970097 (3/1/2010)
That is sneaky! How would you manage the locking and multi-user aspects of this, ie to prevent thousands of calls per second retrieving the same ID?
The value of the identity property doesn't rollback with the transaction and the insert can happen many times within the same table. It's no where's near as contentious as an UPDATE. It would be interesting to see if deadlocks occur or not on this.
It would be diffcult to reserve a range of ID's with because IDENTITIES are not guaranteed to be sequential if other people are also inserting into the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2010 at 4:12 pm
From NOT a DBA ...
sounds like what you really need is an Oracle "sequence". I have implemented similar as follows:
CREATE PROCEDURE dbo.nextval @sequence varchar(28) AS
SET NOCOUNT ON
DECLARE @sql nvarchar(100), @scope_identity int
SET @sql = 'INSERT ' + @sequence + ' default values SELECT @scope_identity=SCOPE_IDENTITY()'
BEGIN TRANSACTION NEXTVAL
SAVE TRANSACTION SEQUENCER
EXEC sp_executesql @sql, N'@scope_identity INTEGER OUT', @scope_identity OUT
ROLLBACK TRANSACTION SEQUENCER
COMMIT TRANSACTION NEXTVAL
SET NOCOUNT OFF
SELECT @scope_identity;
GO
with a created table (@sequence) that contains only an identity column, this concept allows any number of "sequences".
The upside: locking is minimized or eliminated, the table size never changes (more or less)
The downside: there maybe gaps in the sequence
SQL Server has this "useful" feature, it never rolls back an identity value assigned, so even though the transaction is rolled back the identity value is not 🙂
Phil
March 1, 2010 at 4:18 pm
My apologies to the group, I had not read through to see that others had already proposed similar solutions, mea culpa ...
March 1, 2010 at 8:05 pm
mstjean (3/1/2010)
...
begin transactioninsert into SneakyIdentity DEFAULT VALUES
rollback transaction
One difficulty with this is that it ROLLBACKs all open transactions, not just the one started here.
The correct approach is to ROLLBACK to a SAVE POINT. The following code is reasonably robust.
-- This database is guaranteed to be using
-- the SIMPLE recovery model
USE tempdb;
GO
-- Drop the table if it exists from a previous run
IF OBJECT_ID(N'dbo.SneakyIdentity', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.SneakyIdentity;
END;
GO
-- Create the hidden table used to allocate IDs
CREATE TABLE dbo.SneakyIdentity (row_id BIGINT IDENTITY)
GO
-- ==========================================
-- After table creation, you can run the test
-- multiple times from this point onward
-- ==========================================
-- Issue a checkpoint to truncate the log
CHECKPOINT;
-- Show the contents of the transaction log
-- (Will be just LOP_BEGIN_CKPT and LOP_END_CKPT
-- records, recording the start and end of
-- the checkpoint we just ran)
SELECT [Current LSN],
Operation,
Context,
[Log Record Length],
[Checkpoint Begin],
[Checkpoint End],
[Description]
FROM sys.fn_dblog(NULL, NULL);
-- Holds the assigned ID
DECLARE @ID BIGINT;
-- Table variable used with the OUTPUT clause
-- to safely read the identity value assigned.
-- Issues (bugs) have arisen in the past with
-- @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT.
DECLARE @Output
TABLE (row_id BIGINT NOT NULL);
-- Almost all errors will abort the batch
SET XACT_ABORT ON;
-- Start error handler
BEGIN TRY
-- Might not be the top-level transaction!
BEGIN TRANSACTION;
-- Save point
SAVE TRANSACTION AllocateID;
-- Allocate an ID using the 'hidden' table
INSERT dbo.SneakyIdentity
OUTPUT inserted.row_id
INTO @Output (row_id)
DEFAULT VALUES;
-- Show the content of the transaction log
-- Notice that there has been quite a bit of activity!
SELECT [Current LSN],
Operation,
Context,
[Log Record Length],
[Log Reserve],
AllocUnitName,
[Page ID],
[Slot ID],
[Description]
FROM sys.fn_dblog(NULL, NULL);
-- Rollback to the save point
ROLLBACK TRANSACTION AllocateID;
-- Correct the transaction count
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Remember the error message
DECLARE @ErrMsg NVARCHAR(2048);
SET @ErrMsg = ERROR_MESSAGE();
-- Uncommittable transaction => unconditional rollback
-- (this is the only option)
IF XACT_STATE() = -1 ROLLBACK TRANSACTION;
-- If our transaction is still alive, roll back to
-- the save point, and adjust @@TRANCOUNT
IF XACT_STATE() = 1
BEGIN
-- Rollback to the save point
ROLLBACK TRANSACTION AllocateID;
-- Correct the transaction count
COMMIT TRANSACTION;
END
-- Custom error handling goes here
RAISERROR('Error in allocation: %s', 16, 1, @ErrMsg);
RETURN;
END CATCH;
-- Capture assigned value in a variable
-- (Table variables are not affected by
-- user transactions)
SET @ID =
(
SELECT TOP (1)
row_id
FROM @Output
);
-- Show the value allocated
SELECT allocated_id = @ID;
-- Uncomment this when finished
-- DROP TABLE dbo.SneakyIdentity;
Paul
March 2, 2010 at 10:45 am
...uhoh...
Jeff Moden (3/1/2010)
...It would be diffcult to reserve a range of ID's with because IDENTITIES are not guaranteed to be sequential if other people are also inserting into the table.
Are you saying it's possible for the following statement to insert non-sequential values for the identity:
INSERT INTO TableWithIdentity SELECT TOP 100 * FROM SomeTable...Or was your comment specific to the implementation as it was shown in the referenced post?
March 2, 2010 at 8:18 pm
mstjean (3/2/2010)
Are you saying it's possible for the following statement to insert non-sequential values for the identity:
INSERT INTO TableWithIdentity SELECT TOP 100 * FROM SomeTable
Non-sequential/non-contiguous.
I feel ok commenting for Jeff on this because I think he read the same point while reviewing my pending article on this. SQL Server will typically assign non-contiguous ranges for that type of statement, when there is concurrent insert activity to the same table. It is easy to set up and test.
For clarity, an INSERT like the above might end up assigning multiple blocks of sequential ids say 1...54, 120...172, 210...257 and so on. The exact behaviour is an implementation detail and therefore not documented or guaranteed.
The cause, by the way, is the asynchronous nature of identity value assignment. I'm not going to go into it any more, because it's in my article!
Paul
March 2, 2010 at 9:02 pm
Thanks everyone for interesting input on this topic.
I came in on this discussion midway seeking a method to obtain a unique [but not necessarily sequential] NextNumber value that is used as an invoice or document reference and which is recorded in and links together multiple records of various transaction tables. At present we obtain this by updating the appropriate row in a nXtNumber table that has one row per number type. This is reliable but is a bottleneck in our application that causes unacceptable lock waits and extended query times on busy sites. Gaps in the number sequence are acceptable as long as each new number is unique for each number type. I introduced the topic of a SQL equivalent to the Oracle SEQUENCE and this has hijacked the original discussion a bit.
Thanks again for the input. We are testing various suggestions and will post the outcome.
March 3, 2010 at 5:56 am
mstjean (3/2/2010)
...uhoh...Jeff Moden (3/1/2010)
...It would be diffcult to reserve a range of ID's with because IDENTITIES are not guaranteed to be sequential if other people are also inserting into the table.Are you saying it's possible for the following statement to insert non-sequential values for the identity:
INSERT INTO TableWithIdentity SELECT TOP 100 * FROM SomeTable...
Yes. Especially if a lot of users are using the same code to try to do the same thing at the same time. The exception to the rule, of course, would be if "TableWithIdentity" were a Temp table.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2010 at 8:13 am
Jeff Moden
The exception to the rule, of course, would be if "TableWithIdentity" were a Temp table.
...and the exception to that would be if it were a global temp table 😀
Seriously, it's difficult to imagine a scenario where using a local temp table would be useful.
Paul
March 4, 2010 at 9:59 am
Paul White (3/2/2010)
mstjean (3/2/2010)
Are you saying it's possible for the following statement to insert non-sequential values for the identity:
INSERT INTO TableWithIdentity SELECT TOP 100 * FROM SomeTable(YES...) Non-sequential/non-contiguous...The cause, by the way, is the asynchronous nature of identity value assignment. I'm not going to go into it any more, because it's in my article!
Paul
Wow... that is a surprise to me... Is there a working title for this article? I've gotta keep my eyes peeled for it-- it sounds like a MUST read.
March 4, 2010 at 5:45 pm
mstjean (3/4/2010)
Wow... that is a surprise to me... Is there a working title for this article? I've gotta keep my eyes peeled for it-- it sounds like a MUST read.
It's simply called "Sequence Tables" at the moment. The final title is likely to be similar. If I remember, I'll send you a PM when I have a publication date.
October 20, 2010 at 2:53 am
I blogged it in the end 🙂
http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply