|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Today @ 4:13 PM
Points: 2,766,
Visits: 1,442
|
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:10 PM
Points: 105,
Visits: 481
|
|
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.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, February 06, 2013 10:30 PM
Points: 7,
Visits: 63
|
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Thursday, October 06, 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
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:37 AM
Points: 6,
Visits: 78
|
|
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.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, October 26, 2011 6:06 AM
Points: 8,
Visits: 25
|
|
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 ...
|
|
|
|
|
SSCarpal Tunnel
       
Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 4,324,
Visits: 9,665
|
|
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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, June 14, 2013 6:39 AM
Points: 5,
Visits: 77
|
|
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()
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, February 06, 2013 10:30 PM
Points: 7,
Visits: 63
|
|
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.
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:24 AM
Points: 1,028,
Visits: 759
|
|
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?
|
|
|
|