January 20, 2009 at 3:23 am
Can have script to generate 16 digit unique number which contain both character/Number..
which should be not duplicate in future also.
January 20, 2009 at 3:37 am
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
January 20, 2009 at 3:39 am
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.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 20, 2009 at 5:44 am
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...
January 20, 2009 at 5:51 am
yes there's a stong chance anything which truncates a guid or uses the rnd function will generate a duplicate.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 20, 2009 at 6:15 am
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
January 20, 2009 at 6:21 am
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:
[font="Courier New"]
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 [/font]
Lowell
January 20, 2009 at 6:22 am
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?
January 21, 2009 at 6:43 am
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...
January 21, 2009 at 10:01 am
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
January 21, 2009 at 10:07 am
Like this:
Select @@DBTS
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 21, 2009 at 12:23 pm
Matt Miller (1/20/2009)
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.
Exactly... and to generate all manner of random stuff, try the following... then, lemme know how it worked for you...
http://www.sqlservercentral.com/Forums/Topic623503-263-2.aspx#bm625689
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2009 at 12:31 pm
amartha_dutta (1/21/2009)
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...
Not true... if you return more than one GETDATE() in the same select, I guarantee they will all be identical.
If you RUN two selects with GETDATE() within the same 1.65 milliseconds, I guarantee those will be the same as well. Try it...
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2009 at 2:14 pm
I knew you'd be a voice of reason Jeff! I truly don't know any way you can do this in T-SQL and I'm not sure you could do this in the clr either - even a uniqueidentifier still needs a constraint to be on the safe side.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 21, 2009 at 3:00 pm
Well @@DBTS can technically do what the OP asked. Of course that is only within the scope of a single database (no mention of scope in the original request), and you do have to wrap it in a transaction that actually writes to something like a temp table... But yes, technically it can do it.
Of course a better question for Paresh is: "Why do you want to do this? What functional need does it fulfill?"
Because if you really need guaranteed uniqueness across all human space and time, then you are going to need a GUID. Way bigger than 16 printable characters, but the reason that GUIDs are so big is because something smaller, like 16 printable characters, is not nearly sufficient to cover that much scope and possibility.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply