Generating Custom IDs / Primary 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)

Generating Custom IDs / Primary Keys

Author

Topic

iwaters
Starting Member

13 Posts

Posted - 2003-06-16 : 09:15:35

I need to generate an ID for each record I am inserting into the database. It needs to be of the format: number/year. The number should reset to 0 at the beginning of the year. My first question is what is the best way to achieve this? Obviously I will have a unique constraint on this field but my next question is should this record be the primary key for the table or should I use an Identity column?

RichardEttinger
Starting Member

7 Posts

Posted - 2003-06-17 : 13:15:58

I have the same question. Except my id needs to be in the format Year/Month/Day/Number where number is a counter and needs to be reset to 0 each night at midnight.

Have you found a solution to your question?? I would be most appreciative if you have if you could share it.

Thank you

Richard

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 13:19:02

In order to determine the answer, we would need to know more about your database design. To generate this kind of number, you are going to have to write code to do it and maintain it. What is your business requirement for this?

Tara

RichardEttinger
Starting Member

7 Posts

Posted - 2003-06-17 : 13:31:52

We need to create a custom ID for items posted to an auction site in the items table. Everytime an item is added to the table we want to create a unique meaningful id.

We were hoping to create an id like Year+Month+Day+(Number of items posted today)as a unique identity. So today numbers would be 030617 (+)plus the number of items posted today. So if it were the first item posted today we would want a recordid of 0306171. This way the id is unique and meaningful as well..

We would need to reset the counter every night at midnight to 0.
We are using SQL server 7.

Is this doable, or is it terribly inefficient. If it is not suggested, we are completely open to ideas any other ideas which would be greatly welcome without resorting to an straight identity like 1001,1002,etc

Other Auction sites seem to use a similar type id for their items.

Thank you so much..

Richard

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 13:53:32

quote:

We need to create a custom ID for items posted to an auction site in the items table. Everytime an item is added to the table we want to create a unique meaningful id.

But why are you even trying to do this? IMO, an identity column would be better than what you are proposing. The ID doesn't have to be meaningful it sounds like, so why try to make it meaningful?

To answer your question on how to implement this, well you are going to have to build your custom ID each time and also setup a job that resets it to 0. You will most likely need a table that has the current number of posts. Using that number you will need to concatenate to the date. Then after you get the custom ID into the table, you need to go back to the table that has the current number of posts and increment that by 1. You will want to lock this table during the insert (this is where the solution could be a problem). You'll probably want to create a stored procedure that gets the current value and one that updates the value for this table. And don't forget about the job. You will need to create a sql job that resets it to 0 when the clock strikes midnight (is this going to be based upon GMT, PST, EST, etc...?).

I would not recommend the above solution though because it is going to add a lot of overhead. Hopefully some other forum members will respond to your thread to give you some other ideas as well.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-17 : 14:31:24

Can do the update/retrieval of the value and the reset in one statement.

Create a table with one row

create table NextID
(
dte datetime ,
id int
)
insert NextID select '190101', 0

then in the insert SP.

declare @id int, @dte datetime

update NextID set @dte = dte = convert(varchar(8), getdate(),112), @id = id = case when dte = convert(varchar(8), getdate(),112) then id+1 else 1 end

insert tbl select convert(varchar(8),@dte,112) + convert(varchar(10), @id) ,
data

==========================================
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 06/17/2003 14:31:59

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 15:00:01

Very nice Nigel. But what is your opinion about implementing this?

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-17 : 15:22:52

I think you summed it up.

You could always present the data as a the rec num per day by storing the first identity for each day and subtracting that.

==========================================
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.

RichardEttinger
Starting Member

7 Posts

Posted - 2003-06-18 : 16:27:20

Thank you tara and nr for your input..

I agree this seems inefficient, but I just dont like the 1,2,3,4 if it isn't a big deal to impliment a custom solution..

Is it possible to just insert the string date '030617' into one column with convert(varchar(8),current_timestamp,12) "date" and have an identity column and merge them together by having a compound primary index on these two columns?? It is very easy to reset the identity each night with DBCC CHECKIDENT ('mytable', RESEED,0)).

I tried this but I could not figure out how to select rows on this index..

Or is there another way to merge these two columns (one being an identity) on an insert..

I am considering nr's solution, but it just seems the should be a more efficient means..

sql 2000 solves this problem, with IDENT_CURRENT('table_name')but we cannot quite afford it right now..

Thank you for you input..

I may resort to a straight identity as I have spent a full day trying to figure this out..

Richard

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-18 : 17:08:40

You can have a compound PK and merge them - could even use a view so it's transparent.

Resetting the identity each night isn't so easy if you have a 24 hr system though.
How do you get it to happen exactly at midnight? If you're slightly out an insert can get the wrong date for the identity. It's also a problem finding the rec just inserted. If you are near midnight and there was 1 the previous day (3 connections now insert). You get id 2 - but it's after midnight when the insert completes - there are now 2 recs before midnight and 2 after - how do you tell which you just inserted.

Even if you're not 24 hr and the reset job fails you will be unable to insert until it is run successfully.

Don't think ident_current would help.

==========================================
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.

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-18 : 18:57:22

I believe this is thread theft...but no matter.

I think all comes down to:

quote:

So today numbers would be 030617 (+)plus the number of items posted today.

Sooooooo....

Todays numbers need to be represented by a "key", no?

KeyId datatype_whatever
postdate datetime

Sooooooo....

SELECT KeyId, Count(*)
FROM MyTable
WHERE DatePart(d,Postdate) = DatePart(d,getdate())

Or something like that..I'm at home and don't have sql set up....

Damn I hate being lazy...hey but with three kids...(and a beautiful wife..)

The last part was for the marriage gods....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources