January 21, 2009 at 4:45 pm
I agree with Barry; still waiting to hear why an Identity() column, or varchar based off of an identity, or a GUID won't do the job.
Lowell
January 21, 2009 at 5:25 pm
RBarryYoung (1/21/2009)
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.
Won't @@DBTS return the same number until it's actually used by a row being updated somewhere in the database? And, it won't necessarily provide 16 characters in the early life of a database.
I do agree with all the rest, though... why wouldn't an IDENTITY or GUID do the job?
--Jeff Moden
January 21, 2009 at 5:36 pm
Crud... sorry Barry... I've been drinking hot cocoa instead of coffee. I see where you said you'd write to a temp table.
--Jeff Moden
January 21, 2009 at 5:39 pm
Using the function on the link below, this code will generate a random string that is highly likely to be unique.
However, the nature of random numbers is that they they can occur more than once, no matter how unlikely the chance, so you will still need a unique constraint.
Function to Convert Number to Base N
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109916
If you care to tweak the function by including special characters in the output string, you might be able to compress a full guid down to fewer characters, but as it is, a full GUID requires 26 characters for base 36. Of course, a uniqueidentifier datatype would be more efficient, because it will need only 16 bytes.
select Rand_Sting =
left(dbo.F_NUMERIC_TO_BASE_N ( abs(convert(bigint,convert(varbinary(8),newid()))), 36)+
dbo.F_NUMERIC_TO_BASE_N ( abs(convert(bigint,convert(varbinary(8),newid()))), 36),16)
Results:
Rand_Sting
----------------
1L1KPV7E7NBD2TI9
(1 row(s) affected)
January 21, 2009 at 5:40 pm
Jeff Moden (1/21/2009)
RBarryYoung (1/21/2009)
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.
Won't @@DBTS return the same number until it's actually used by a row being updated somewhere in the database?
Yep. That's what the second sentence is addressing.
Granted it doesn't work like NEWID(), but that wasn't the stated requirement either. (And of course most scalar functions will only return a single value within a query anyway, NEWID and NEWSEQUENTIALID are the only ones that get around that IIRC).
And, it won't necessarily provide 16 characters in the early life of a database.
Sure it does. Here's the first one: "0000000000000001". 🙂
[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 5:41 pm
As a side bar, 16 alpha-numeric combinations (26 letters, 10 digits = 26 different characters) would be many more combinations than most people would ever be able to use in a life time.
Still the question remains, what are the business reasons for wanting to use 16 alphanumeric characters instead of IDENTITY or GUID? Once we know that, we'll be much better equipped to render a proper opinion/solution.
--Jeff Moden
January 21, 2009 at 5:41 pm
Oops, I missed your reply too! Man we're getting slow... 😛
[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 5:45 pm
Responders should note that the request was for a unique number and not a random one.
Given the uniqueness of the counting numbers, that's probably a lot easier. 🙂
[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 6:05 pm
This code generates 16 character, unique strings increasing in value. There may be gaps in the sequence.
See my last post for a link to the code for F_NUMERIC_TO_BASE_N .
declare @t table ( x rowversion ,y int)
insert into @t (y) select 1
select unique_string =right('0000000000000000'+dbo.F_NUMERIC_TO_BASE_N ( abs(convert(bigint,x)), 36),16) from @t
Results:
unique_string
----------------
00000000000001KU
(1 row(s) affected)
January 21, 2009 at 7:14 pm
colin Leversuch-Roberts (1/21/2009)
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.
Thanks, Colin. Heh... it's very odd... on another thread on another forum, I recently got accused of being being a reckless and dangerous programmer.
--Jeff Moden
January 21, 2009 at 10:24 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...
I have tried this but it will also insert same number , bec i have insert into bulk..so it may hapen it will also duplicate as same recored insert in same time within milisecond..
January 21, 2009 at 10:30 pm
RBarryYoung (1/21/2009)
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.
One table has one field and it should be 16 didgit as per client requirement...
Programmer has given me this task...
January 21, 2009 at 11:12 pm
Paresh Prajapati (1/21/2009)
RBarryYoung (1/21/2009)
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.
One table has one field and it should be 16 didgit as per client requirement...
Programmer has given me this task...
Uh huh. Well then, I have given you the answer.
[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 22, 2009 at 1:46 am
I think it's time for Paresh to get a little more specific...
Several possible solution were posted, each one depending on the circumstances of usage.
E.g. Barry's solution in my opinion is the easiest and fastest way as long as it is guaranteed that the function is not supposed to be moved to a different database in the future. But without writing to a table or at least temp table you still end up with the risk of duplicates...
The suggestions involving storage in a table for lookup purposes will be efficient as long as the amount of unique values will remain in a range that can be handled. Assuming the 16 digits are not requested "just for fun"... :
The request for being alphanumeric leads to a 25 digit number of possible unique values (26 character + 10 numbers = 36 characters per digit. Assuming 16 digits there are 7.958.661.109.946.400.884.391.936 available keys (Far more than the 16 digits and I don't even know how to spell it 🙂 )
Staying with the 16 character you'll end up with thousands of terrabyte to store those unique keys. But what for?
My questions to Paresh are
1) How many keys do you have to provide in a given period of time (e.g. per day / per hour / per second)? - If less than a few thousand per day I'd use a table.
2) Is there any function later in the processs that will detect a duplicate key and could call for another key? - If yes I'd go with @@DBTS.
3) Can it be agreed within the project that "unique" is limited to the current database? - If yes I'd go with @@DBTS.
4) Is it possible that the requiremet of 16 digits comes out of a PLC process? (16 digits = 1 word in PLC language). If so, Id rather start at the very beginning and ask about the real requirement of unique values related to the process...
Btw: A process that could end up with such amount of unique numbers could be a food company (e.g. for spaghetti or rice) that overengineered there traceability requirements (give a single spaghetti a unique ID to trace it down the line... 😀 ) -> You'll never know...
Regards
Lutz
January 22, 2009 at 5:48 am
Paresh Prajapati (1/21/2009)
One table has one field and it should be 16 didgit as per client requirement...Programmer has given me this task...
But, that's not the business reason and you should ask so you can give the best answer possible. Creating such a "field" is a real PITA to create, maintain, and guarantee uniqueness for and they may not even know about IDENTITY or GUID values. A good DBA has always got to ask the question, "What is the business reason for this?" The answer of "It's a requirement" should never be good enough... especially for something as bad for the database as this.
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply