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 12345»»»

An alternative to GUIDs Expand / Collapse
Author
Message
Posted Wednesday, September 14, 2011 9:37 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:07 AM
Points: 2,901, Visits: 1,805
Comments posted to this topic are about the item An alternative to GUIDs

LinkedIn Profile
Newbie on www.simple-talk.com
Post #1175396
Posted Wednesday, September 14, 2011 10:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:07 AM
Points: 109, Visits: 489
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.
Post #1175416
Posted Thursday, September 15, 2011 12:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:40 PM
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!



Post #1175436
Posted Thursday, September 15, 2011 1:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 6, 2011 1:24 AM
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

Post #1175444
Posted Thursday, September 15, 2011 2:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:49 AM
Points: 8, Visits: 99
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.



Post #1175460
Posted Thursday, September 15, 2011 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 7:07 AM
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 ...
Post #1175466
Posted Thursday, September 15, 2011 3:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 5,045, Visits: 11,794
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?



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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1175481
Posted Thursday, September 15, 2011 3:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:50 AM
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()
Post #1175487
Posted Thursday, September 15, 2011 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:40 PM
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.



Post #1175499
Posted Thursday, September 15, 2011 3:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:00 AM
Points: 1,069, Visits: 898
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?



Post #1175510
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse