Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An alternative to GUIDs


An alternative to GUIDs

Author
Message
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 3121
Comments posted to this topic are about the item An alternative to GUIDs

LinkedIn Profile

Newbie on www.simple-talk.com
Cade Roux
Cade Roux
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 491
For independent client generation, we had good success with COMBs: http://www.informit.com/articles/printerfriendly.aspx?p=25862

The problem you mention with waiting on the database for SCOPE_IDENTITY() can be mitigated now (at least for one master child - not for a complex multi-level hierarchy or network) by using table-valued parameters for master/child data. All the data can be submitted cleanly to stored procs and easy use of SCOPE_IDENTITY() and OUTPUT INTO can avoid the round trips for that. This also mitigates any need for external transaction management around the operation from the client side since it becomes atomic from the client's perspective.
StuR
StuR
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 69
This is actually very close to the following by Tatham Oddie: http://blog.tatham.oddie.com.au/2011/07/14/released-snowmaker-a-unique-id-generator-for-azure-or-any-other-cloud-hosting-environment/

or perhaps even more close to the Twitter Unique ID generator he references: https://github.com/twitter/snowflake

If anyone was going to implement this type of ID generator I'd suggest taking careful note of the comments in the description of the Twitter ID generator that talk about NTP time servers and what can happen if your clocks are wound back automatically. Not a good thing, and actually more likely to occur in a virtual environment where clocks tend to run a little fast!



nick 2435
nick 2435
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
Some time back we needed to achieve a similar goal, but with sales order numbers, which we previously sequential and the "next" value held in a "housekeeping" table along with invoice numbers etc. The existing performance was poor though - worse than IDs - but we still needed some form of sequentiallity, and it to be human readable - and quotable over the phone by customers - therefore GUIDs were definitely not an option

We changed the SO numbering here to be a long set of letters and numbers that are part random and part sequence (based on time) which means the computer just “makes it up” rather than grabbing the next one.

What we did was build a 15 digit order number in the following format, using essentially base 30 - built from 30 easily distinguisable characters (23456789ABCDEFGHJKLMNPRTUVWXYZ)

S123-TYP-QRT-6RE

First character “S” is for Sales Order (So we can implement for Q-uotes, R-eturns etc.)

The bit in bold is the sequence part (changes every minute so)

The bit not in bold is random – with 729000000 possibilities – not much chance of a clash in the 60 seconds.

Generally we ask people phoning for the last 3 digits (i.e. customers) and you may get 2 or 3 sales orders appearing, but it’s easy to find the right one… The last 6 digits as you can see from the possibilities are HIGHLY unlikely to get duplicates anyhow.

Here is our VB.NET code (or most of it). It could be easily changed to provide unique IDs that are human readable, nearly sequential, and don't require a "server ID". All of this is also implementable in SQL if you require using stored procedures.

    Function GetSONumber() As String
Return GetSequenceNumber("S")
End Function

Function GetSequenceNumber(ByVal PrefixChar As String) As String

PrefixChar = Left(PrefixChar + "X", 1) ' in case blank

Dim Mins As Integer
Mins = DateDiff(DateInterval.Minute, CDate("2008-01-01"), GetServerTime)
Return SplitIntoThrees(PrefixChar + ConvertBase30(Mins) + RandLetts(6))

End Function

Function RandLetts(ByVal Chars As Integer) As String

' letters that can easily be distinguished visually and audibally
' need to build routine to convert S to F for example and block others
Dim V As String = "23456789ABCDEFGHJKLMNPRTUVWXYZ"
Dim O As String = ""
For I As Integer = 1 To Chars
O = O + Mid(V, RollDice(Len(V)), 1)
Next

Return O
End Function

Function GetServerTime() As Date

' taken from http://www.codeproject.com/KB/vb/NetRemoteTOD.aspx
' removed daylight saving part as we want UTC

' see http://support.microsoft.com/kb/249716 for full usage info

' reverts to local PC time if can't connect

Try
Dim iRet As Integer
Dim ptodi As IntPtr
Dim todi As TIME_OF_DAY_INFO
Dim dDate As Date
Dim strServerName As String = "servername.local" & vbNullChar
iRet = NetRemoteTOD(strServerName, ptodi)
If iRet = 0 Then
todi = CType(Marshal.PtrToStructure(ptodi, GetType(TIME_OF_DAY_INFO)), _
TIME_OF_DAY_INFO)
NetApiBufferFree(ptodi)
dDate = DateSerial(todi.tod_year, todi.tod_month, todi.tod_day) + " " + _
TimeSerial(todi.tod_hours, todi.tod_mins, todi.tod_secs)
GetServerTime = dDate
Else
GetServerTime = Now.ToUniversalTime
End If
Catch
GetServerTime = Now.ToUniversalTime
End Try

End Function

Function SplitIntoThrees(ByVal CharsIn As String) As String

Dim i As Integer
Dim O As String = ""
For i = 1 To Len(CharsIn)
If (i - 1) Mod 3 = 0 And i <> 1 Then O = O + "-"
O = O + Mid(CharsIn, i, 1)
Next i
SplitIntoThrees = O

End Function

Function RollDice(ByVal NumSides As Integer) As Integer
' Create a byte array to hold the random value.
Dim randomNumber(0) As Byte

' Create a new instance of the RNGCryptoServiceProvider.
Dim Gen As New RNGCryptoServiceProvider()

' Fill the array with a random value.
Gen.GetBytes(randomNumber)

' Convert the byte to an integer value to make the modulus operation easier.
Dim rand As Integer = Convert.ToInt32(randomNumber(0))

' Return the random number mod the number
' of sides. The possible values are zero-
' based, so we add one.
Return rand Mod NumSides + 1

End Function


adam.everett
adam.everett
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 118
nice idea,

but my concern would be clocks rolling forward/backard.

As stated above if its a Enterprise app really the whole transaction set should be passed to an SP as one atomic transaction removing the round trip/Transaction handling.

Sounds like not very good data centre management, if A fails to B and then B wants to pass control back to A what happens to the the orders taken at B. They should be persisted back to A anyhow.
Pascal Declercq
Pascal Declercq
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
Quote: "What happens after 40 years (well actually nearer 60 years)? Well, it will be job security for my great-grandchildren so I'm not going to worry about it."

I thought we IT guys had learned our lesson after the millenium bug ...
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8404 Visits: 19506
Pascal Declercq (9/15/2011)
Quote: "What happens after 40 years (well actually nearer 60 years)? Well, it will be job security for my great-grandchildren so I'm not going to worry about it."

I thought we IT guys had learned our lesson after the millenium bug ...


Does that include the lesson that 'millennium' has two n's? :-P


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Carpe Datum
Carpe Datum
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 97
Seems to be a lot of re-invent-the-wheel being suggested.

If you want unique ids (including generating across different machines) and you want random for "security" because you think they are unguessable - then you need ordinary GUIDs. Your random-unguessable requirement is always going to conflict with index performance (if you use this id as a key).

If you want to avoid the perfromance problems of using GUIDs as keys, and you do your security properly, then use sequential GUIDs which are already built into SQL Server for preciseley this purpose. If you don't want to generate the ids in-database, the same funciton is available on the client, or app layer, in windows api.

SQL: NEWSEQUENTIALID()
Client: UuidCreateSequential()
StuR
StuR
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 69
Whilst it is true sequential guids solve many of the main issues associated with databases partitioned across multiple servers or failover situations and provide random, hard to guess values, based on experience I would suggest you carefully consider how these would be used.

Due to time constraints I was forced to use these on a large project and they do work very nicely in code and database however any time a user needs to read back a key things get ugly quickly.



RichB
RichB
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1102 Visits: 1015
Uh, hang on. So what you are saying is different parts of a datetime will represent different parts of the system?

Why not just create a composite key that does that clearly?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search