identity, default values and multiple rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

identity, default values and multiple rows

Author

Topic

talleyrand
Starting Member

35 Posts

Posted - 2008-05-27 : 15:02:54

Howdy all,
I've run into an interesting scenario that I can't seem to resolve.

We have a table that we are using to create sequence ids. It's literally a table with a single field with the identity value turned on.


create table ident (seq_id int identity(1,1) primary key NOT NULL)

If I want to grab a new sequence id, I can execute


INSERT INTO
    ident
DEFAULT VALUES
SELECT scope_identity() AS seq_id

All is well and good. However, now let's suppose that instead of just one, I'd like to get a range of values.

If the table had one other field in it, I could do the following


create table ident2 (seq_id int identity(1,1) primary key NOT NULL, placeholder char(1))
GO
;
WITH RECORDS AS
(
    SELECT
        CAST('A' as char(1)) AS col1
    UNION ALL
    SELECT
        char(ascii(col1) + 1)
    FROM
        RECORDS
    WHERE
        col1 < 'C'
)
INSERT INTO
    ident2
OUTPUT
    INSERTED.seq_id
SELECT
    NULL
FROM
    RECORDS

Can this be done if there is only the identity column and if so, someone care to educate me?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-27 : 16:01:17

I've never seen a production table defined with just an identity column and no other columns. I can't think of a way to do a multi-row insert into such a table. You could always add a bit column to solve that little issue.

But I would question your need for a sequence table at all. what are you going to use it for? Is this an ID generater? That is usually not a good idea.

Be One with the Optimizer
TG

talleyrand
Starting Member

35 Posts

Posted - 2008-05-27 : 16:12:15

Sadly, this _is_ going to be a production table. I'm quite aware that it's rather WTF but I'm not the architect of it and blessedly, I'm not the poor soul who has to code it. I was just trying to assist the one who has to implement it.

Qualis
Posting Yak Master

145 Posts

Posted - 2008-05-28 : 11:04:23

Here is a method to do it. It has several limitations. First, because of the recursion limit, you can only add 100 identities at a time. Second, there is a chance for out of order inserts to be created. An alternative to this method is a cursor and the performace will be horrible for large inserts (which this can't do anyway).

Try this:


CREATE PROCEDURE AddIdentityRange(@NumberToAdd int)
AS BEGIN
	IF @NumberToAdd > 100 BEGIN
		RAISERROR ('You cannot add more than 100 identities at a time due to the limitations of this method.', 16, 1)
		RETURN
	END

	SET IDENTITY_INSERT ident ON;

	DECLARE @Start int;
	DECLARE @End int;

	SELECT 
		@Start = ISNULL(MAX(seq_id), 0) + 1,
		@End = ISNULL(MAX(seq_id), 0) + @NumberToAdd
	FROM ident;

	WITH RECORDS AS
	(
		SELECT @Start As Col1 UNION ALL
		SELECT col1 + 1
		FROM RECORDS
		WHERE col1 < @End
	)
	INSERT INTO ident (seq_id)
	OUTPUT INSERTED.seq_id
	SELECT col1
	FROM RECORDS;

	SET IDENTITY_INSERT ident OFF;
END

Qualis
Posting Yak Master

145 Posts

Posted - 2008-05-28 : 11:18:50

Another method which would eliminate the 100 limit is:


CREATE PROCEDURE AddIdentityRange2(@NumberToAdd int)
AS BEGIN
	SET IDENTITY_INSERT ident ON;

	DECLARE @Start int;
	DECLARE @End int;
	DECLARE @cnt int

	SELECT 
		@Start = ISNULL(MAX(seq_id), 0) + 1,
		@End = ISNULL(MAX(seq_id), 0) + @NumberToAdd
	FROM ident;

	DECLARE @Idents TABLE (Ident int)

	SET @cnt = @Start
	WHILE @cnt <= @End BEGIN
		INSERT INTO @Idents SELECT @cnt;
		SET @cnt = @cnt + 1
	END

	INSERT INTO ident (seq_id)
	OUTPUT INSERTED.seq_id
	SELECT Ident
	FROM @Idents;

	SET IDENTITY_INSERT ident OFF;
END

Qualis
Posting Yak Master

145 Posts

Posted - 2008-05-28 : 11:32:42

The second one is CPU bound and uses IDENITY_INSERT. This method is IO bound and does not.


CREATE PROCEDURE AddIdentityRange3(@NumberToAdd int)
AS BEGIN
	SET NOCOUNT ON;

	DECLARE @Start int;
	DECLARE @End int;
	DECLARE @cnt int

	SELECT 
		@Start = ISNULL(MAX(seq_id), 0) + 1,
		@End = ISNULL(MAX(seq_id), 0) + @NumberToAdd
	FROM ident;

	SET @cnt = @Start
	WHILE @cnt <= @End BEGIN
		INSERT INTO ident DEFAULT VALUES
		SET @cnt = @cnt + 1
	END

	SELECT seq_id
	FROM ident
	WHERE seq_id BETWEEN @Start AND @End;
END

Qualis
Posting Yak Master

145 Posts

Posted - 2008-05-28 : 12:22:39

Here is yet another version. This one drastically increases the speed over variant 2.


CREATE PROCEDURE AddIdentityRange4(@NumberToAdd int)
AS BEGIN
	SET NOCOUNT ON;
	SET IDENTITY_INSERT ident ON;

	DECLARE @Start int;

	SELECT 
		@Start = ISNULL(MAX(seq_id), 0)
	FROM ident;

	INSERT INTO ident (seq_id)
	OUTPUT INSERTED.seq_id
	SELECT @Start + ident FROM 
	(
		SELECT ROW_NUMBER() OVER (ORDER BY S1.name) ident
		FROM 
			syscolumns As S1, 
			syscolumns As S2,
			syscolumns As S3
	) Idents
	WHERE ident <= @NumberToAdd

	SET IDENTITY_INSERT ident OFF;
END

Qualis
Posting Yak Master

145 Posts

Posted - 2008-05-28 : 12:30:08

This method takes more time, but lowers the concurrency issue probability.


CREATE PROCEDURE AddIdentityRange5(@NumberToAdd int)
AS BEGIN
	SET NOCOUNT ON;
	SET IDENTITY_INSERT ident ON;

	DECLARE @Idents TABLE (ident int)
	INSERT INTO @Idents
	SELECT ident FROM 
	(
		SELECT ROW_NUMBER() OVER (ORDER BY S1.name) ident
		FROM 
			syscolumns As S1, 
			syscolumns As S2,
			syscolumns As S3
	) Idents
	WHERE ident <= @NumberToAdd

	DECLARE @Start int;
	SELECT @Start = ISNULL(MAX(seq_id), 0)
	FROM ident;

	INSERT INTO ident (seq_id)
	OUTPUT INSERTED.seq_id
	SELECT @Start + ident FROM @Idents

	SET IDENTITY_INSERT ident OFF;
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-28 : 14:11:38

Qualis, I'm sure the OP appreciates the effor but...
The problem with your solutions that use insert_identity is that it takes away any advantage of having an identity column. Why have a table with one column (that is identity) but populate it with generated values?

The problem with all your solutions is one of concurrency - collisions between multiple, simultaneous requests. This is the classic problem with most custom built ID generaters. You could build in additional transaction handling, custom locking, or non default trans isol levels but that would just lead to longer blocking in high contention situations.

That is why I was asking th OP what the purpose of this table was but they didn't really answer other than to say it's a bad design that someone else is accountable for.

Be One with the Optimizer
TG

talleyrand
Starting Member

35 Posts

Posted - 2008-05-28 : 14:25:59

Well, Qualis and I have been bad forum users and have been chatting out of channel about this.
As to the purpose, yes it's vaguely an id generator that multiple processes will request ids from. It's not my project so I really can't tell you anymore than that. Whether it's the best design or the worst is immaterial, I was simply trying to see what the idiom would be to insert multiple rows into a table with only an identity field. At this point, there doesn't appear to be one.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-28 : 14:47:25

>>Well, Qualis and I have been bad forum users and have been chatting out of channel about this
Nothing wrong with that - the whole idea is to help question posters and allow others to see solutions. And Qualis was thoughtfull enough to post his solutions. Props to both of you :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources