Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

16 digit unique number Expand / Collapse
Author
Message
Posted Tuesday, January 20, 2009 3:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 7:41 AM
Points: 323, Visits: 464
Can have script to generate 16 digit unique number which contain both character/Number..
which should be not duplicate in future also.


_____________________________________________________________________________________________________________
Paresh Prajapati
+919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Post #639753
Posted Tuesday, January 20, 2009 3:37 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, Visits: 534
Paresh Prajapati (1/20/2009)
Can have script to generate 16 digit unique number which contain both character/Number..
which should be not duplicate in future also.


declare @random varchar(50)
set @random = newid()
print (@random)

select substring(@random,1, 16)


kshitij kumar
kshitij@krayknot.com
www.krayknot.com
Post #639763
Posted Tuesday, January 20, 2009 3:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
I think you're asking too much here and I'm not too certain this is an easy task in T-SQL. A guid will give you 32 chars which should be unique, although you'll still need a constraint as I have encountered duplicate guids a few times.
I'd go for guid and save yourself agony, although I'd actually go for an int being smaller.


The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #639764
Posted Tuesday, January 20, 2009 5:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 7:41 AM
Points: 323, Visits: 464
krayknot (1/20/2009)
Paresh Prajapati (1/20/2009)
Can have script to generate 16 digit unique number which contain both character/Number..
which should be not duplicate in future also.


declare @random varchar(50)
set @random = newid()
print (@random)

select substring(@random,1, 16)


It may possible substring(@random,1, 16) will be duplicate in future...


_____________________________________________________________________________________________________________
Paresh Prajapati
+919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Post #639846
Posted Tuesday, January 20, 2009 5:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
yes there's a stong chance anything which truncates a guid or uses the rnd function will generate a duplicate.


The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #639858
Posted Tuesday, January 20, 2009 6:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:16 AM
Points: 12,923, Visits: 32,282
how about an Identity using a bigint column that starts with the first 16 digit number ?
create table #example( bigintID bigint identity(1000000000000000,1) primary key,
morestuff varchar(30) )

also why must it be 16 digits?
::editing i just saw it needs to be letters and numbers...i have an example, but it is still based off of an identity column::


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #639878
Posted Tuesday, January 20, 2009 6:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:16 AM
Points: 12,923, Visits: 32,282
i misread the original post;
here's a sample from my snippets where someone wanted a unique alphanumeric, in order, ie AAA001 thru ZZZ999;
the nubmer gets generated based on an identity.\
you could do the same, and just pad it so that it is 16 chars:

DROP TABLE X
CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 676000)),
XCALCULATED AS   CHAR((XID/26000)%26+65) --1st Letter
      
+CHAR((XID/1000)%26+65)  --2nd Letter
      
+REPLACE(STR(XID%1000,3),' ','0'), --The 3 digit numeric part
SOMEOTHERCOL VARCHAR(30)
)


INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')
SET IDENTITY_INSERT X ON
INSERT INTO
X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SET IDENTITY_INSERT
X ON
INSERT INTO
X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SELECT
* FROM X

XID XCALCULATED SOMEOTHERCOL
1 AA001 WHATEVER
675999 ZZ999 MORESTUFF





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #639885
Posted Tuesday, January 20, 2009 6:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 7,154, Visits: 15,633
The only way you're going to ENSURE that it's unique is to keep track of all of the ones previously generated. Use whatever methodology you like to gen it, then store it in table: if it's already been gen'd then do it again.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #639887
Posted Wednesday, January 21, 2009 6:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 1, 2012 12:43 AM
Points: 63, Visits: 110
Dear Friend,

How about looking into manipulating the current system date & Time.
Use GetDate(), Remove the spaces and append with some other characters / digits of your choice (Based upon some logic, if you find comfortable).

Lets Say GetDate() Returns :- 2009-01-21 19:06:26.777
Trim The "-" Then 20090121 (8 Characters)
Remove ":" Then 190626 (6 Characters)
Remove "." Then 777 (3 Characters) Take First 2

So the Key 2009012119062677 = 16 Digits.
It can't be duplicated never ever - As the same date won't repeat back again.

Take Care
Bye
Happy SQLing...
Post #640657
Posted Wednesday, January 21, 2009 10:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
Hi,

using the current system time to get a unique value is dangerous if the underlying server follows DST (daylight saving time).
What happens if you generate a key around the switch from DST back to normal time?
In this case you end up with one hour every year where you can have duplicates...

If you don't want to store all the previous data in a table, you could use a combination of current date and a generated random code. Store the random part in a table and truncate it every midnight.

Regards
Lutz




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #640915
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse