Seqential Number Generation and Keys

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 2000 Forums

SQL Server Development (2000)

Seqential Number Generation and Keys

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-19 : 19:34:52

I hope this doesn't start the flame war that Rob's article started. I jsut got done reading abotu half of the posts, and I think I'm more confused now than when I started.

Anyway, here's my problem.

I have to generate a number that users can easily type into a telephone. That number will tie sets of users together (a conference). Right Now, that number is being generated by an Identity field with a seed of 100000. This generates unique 6-digit numbers that I can use as my PK in this table, and FK in Child tables.

As far as I can tell, this is a pretty good use of identities. Thoughts?

One issue with this is that I would run into 7 digit numbers in the not too distant future, so one thought is to delete/archive the numbers somehow and reuse them. Another thought is some way of making a composite key with a CustomerID + the Identity. The Identity values could be duplicated, the the combination of CustomerID and Identity could NOT be duplicated. This would give me about 900k Id's per customer, which would work out pretty nicly. The problem with this solution is I have no idea how to tell SQL server to create the identity like this.

I'd appreciate any thoughts or ideas on this. I'm not really sure WHAT to do!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-20 : 04:18:25

For this don't use an identity. You can allocate the ID in a trigger or in the SP that adds the record. If in a trigger then you will need some way of obtaining the ID - either by other unique data or you can leave the identity on the table and use that to obtain the ID or just get the max for that customerID.

table becomes
OldID int identity
ID
CustomerID

trigger is
update tbl
set ID = (select coalesce(max(ID),99999) + 1 from tbl t1 where t1.CustomerID = tbl.CustomerID)

obtain by
select @ID = @@identity
select @ID = ID from tbl where OldID = @ID

Note that this will only work for single record inserts.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 12/20/2002 04:19:19

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-12-20 : 05:50:14

quote:

I have to generate a number that users can easily type into a telephone. That number will tie sets of users together (a conference). Right Now, that number is being generated by an Identity field with a seed of 100000. This generates unique 6-digit numbers that I can use as my PK in this table, and FK in Child tables.

Not helping your question, but presumably the users have to type an additional non-sequential number to reduce the security problems with users 'trying out' other conferences?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-20 : 11:11:58

NR,
I'm going to do some testing with your ideas. Thanks for your input.

Arnold,
Yeah there are some other key pieces to the "authentication" system such as the number you dial, and possibly a PIN depending on the type of user that is calling in.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-20 : 15:20:06

Question:

How do I make sure that the "ID" that is generated with the MAX(ID) function isn't duplicated by another user at the same time? I'm not very familiar with transactions, and how to roll them back etc. I've read teh BOL stuff time and time again, but I still don't understand what's the best way to do things.

Should I use IDENT_CURRENT() instead of @@Identity?

This is what I have so far. The table stucture is simplified:


CREATE PROCEDURE [dbo].[p_Conference_CreateConference](
@ConferenceID INT OUTPUT,
@CustomerID INT
)
AS
DECLARE @ID INT

	INSERT INTO Conference(ConferenceID, CustomerID) VALUES(NULL, @CustomerID)

	UPDATE Conference
		SET ConferenceID = (SELECT COALESCE(MAX(ConferenceID), 99999)+1 FROM Conference WHERE CustomerID = @CustomerID)
	WHERE ConferenceIDPK = @@IDENTITY
	
	SELECT @ConferenceID = ConferenceID
	FROM Conference
	WHERE ConferenceIDPK = @@IDENTITY

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources