Generate a unique number for a record

Question

Post reply

Generate a unique number for a record

Esteban-786736

SSC-Addicted

Points: 400
More actions
August 14, 2008 at 8:04 pm

#96517

Hi,
I'm trying to generate a unique id for every record ni a table. The tricky part is, each number is composed of
'EN' + CURRENT YEAR + a unique 10 digits number.
for example
EN20085135485162
EN20089452185694
...
Each number is a unique id for the record.
Any body done that before?
Thanks in advance
Bye

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 1

Add an IDENTITY column to the table, and make your "unique" column as a persisted computed column.

Also see this topic

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

for more calculations on IDENTITY column.

N 56°04'39.16"
E 12°55'05.25"

Esteban-786736 SSC-Addicted Points: 400 More actions · Answer 2

Esteban-786736

SSC-Addicted

Points: 400

August 15, 2008 at 6:59 am

#858665

Thanks a lot.

alogic11 SSC Enthusiast Points: 105 More actions · Answer 3

Esteban-786736 (8/14/2008)
Hi,
I'm trying to generate a unique id for every record ni a table. The tricky part is, each number is composed of
'EN' + CURRENT YEAR + a unique 10 digits number.
for example
EN20085135485162
EN20089452185694
...
Each number is a unique id for the record.
Any body done that before?
Thanks in advance
Bye

:hehe:

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

Try

Select 'EN'+CONVERT(varchar,DATEPART(yy,getdate()))+

CONVERT(varchar,datepart(mm,getdate())) +

CONVERT(varchar,datepart(dd,getdate()))+

CONVERT(varchar,datepart(HH,getdate()))+

CONVERT(varchar,datepart(MI,getdate()))+

CONVERT(varchar,datepart(SS,getdate()))+

CONVERT(varchar,datepart(MS,getdate()))

this will never repeat unless you change server time.

Thanks
Parthi

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

parthi-1705 (4/27/2011)
Try
Select 'EN'+CONVERT(varchar,DATEPART(yy,getdate()))+
CONVERT(varchar,datepart(mm,getdate())) +
CONVERT(varchar,datepart(dd,getdate()))+
CONVERT(varchar,datepart(HH,getdate()))+
CONVERT(varchar,datepart(MI,getdate()))+
CONVERT(varchar,datepart(SS,getdate()))+
CONVERT(varchar,datepart(MS,getdate()))
this will never repeat unless you change server time.

I'm not sure why you posted that on a 3 year old topic. :unsure:

In any case, that will not guarantee a unique number, because two different sessions can run that code at the same time and get the same number.

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

Not to mention that most servers have the time changed twice a year. Daylight saving time. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

consultingforce SSC Enthusiast Points: 101 More actions · Answer 7

If you are seeking a unique number like in Oracle when you use a sequence (which I so wish SQL server had) you can do what I have done....

Create a table that only has a date.

Use scopeidentity to retrieve the value of the newly inserted date

You now have a unique number

What sux about SQL Server in the scopeidentity paradigm is that you must be performing an insert to retrieve it.....what if I want a number to link but I am not doing an insert? You are stuck like chuck.....

Oracle nailed that one....create sequence SEQ_TableName; SEQ_TableName.nextval....you are there.

B

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 8

consultingforce (9/15/2011)
If you are seeking a unique number like in Oracle when you use a sequence (which I so wish SQL server had) you can do what I have done....

Which is enabled in Denali.

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

{Edit} Post withdrawn... wrong post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Sean Lange (4/27/2011)
Not to mention that most servers have the time changed twice a year. Daylight saving time. :w00t:

Nor that the part that's supposed to be a 10 digit number will vary between 6 digits and 13 digits depending on the date and time. :w00t:

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Michael Valentine Jones (4/27/2011)
In any case, that will not guarantee a unique number, because two different sessions can run that code at the same time and get the same number.

And two sessions running months apart can generate the same number too.

For example, 20111211054170 is generated from both 2011-12-01T01:10:54:17.000 and 2011-01-21T01:10:54:17.000, and it's easy to see how the same sort of thing can hapen with minutes and hours, hours and days, and so on. Look at 2135433 - is that Feb 1 03:05:43.003 or Feb 13 05:04:03.003 or Feb 1 03:54:03.003, you can see the thing repeats all over the place.

Tom

kadampatil.bk Newbie Points: 7 More actions · Answer 12

Try This...
SELECT 'AB99'+CAST(RIGHT('00000000' + CAST(ABS(CHECKSUM(NEWID())) % 99999999 AS VARCHAR(8)), 8) AS VARCHAR) AS RANDOM

drew.allen SSC Guru Points: 76944 More actions · Answer 13

kadampatil.bk - Wednesday, September 5, 2018 5:34 AM
Try This...
SELECT 'AB99'+CAST(RIGHT('00000000' + CAST(ABS(CHECKSUM(NEWID())) % 99999999 AS VARCHAR(8)), 8) AS VARCHAR) AS RANDOM

You do realize that this thread is ten years old and that the OP hasn't been active in seven years.

Also, this does not guarantee a unique number.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA