Code versus Int

Hi,

Web applications often use a parameter to select a specific record. Hotmail, 
for example, uses a code like "ZIaXbPiFjyPUfJG44nag9A%3d%3d" to retriev a 
specific mail message.

Question 1)
Is an Int type not prefered?

Question 2)
What is a smart way to generate unique random numbers?
Date + number??? 051320101 = 05-13-2010-1

Thanks!
Arjen 

0
Arjen
5/12/2010 9:22:14 PM
sqlserver.server 1327 articles. 0 followers. Follow

5 Replies
483 Views

Similar Articles

[PageSpeed] 32

Arjen (boah123@hotmail.com) writes:
> Web applications often use a parameter to select a specific record.
> Hotmail, for example, uses a code like "ZIaXbPiFjyPUfJG44nag9A%3d%3d" to
> retriev a specific mail message. 
> 
> Question 1)
> Is an Int type not prefered?

Not always. There are a couple of problem with using numbers. One is
that sequential numbers can be easy to guess. Which can be helpful
for intruders. 

In case of big web sites, they have farms of servers and the ids are 
generated on the web servers. Thus ids must be generated without risk
for collision. This can be difficult with numbers, even with 64-bit 
integers. A common device are GUIDs, which are 128 bits, and generated
in a random way so that the risk for collision is so small that it is
negligible. If Hotmail uses a GUID or just a hash of some sort, I don't
know.

> Question 2)
> What is a smart way to generate unique random numbers?
> Date + number??? 051320101 = 05-13-2010-1
 
In SQL Server, checksum(newid()) is a good random generator. But if
you want your series to not include duplicates, you will need to
track generated numbers in a table. If you generate 100000 random
32-bit numbers, you have a likelyhood of 0.3 of having at least one
duplicate, if memory serves. Or if it was 0.3 for not getting any
dups at all.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
5/12/2010 9:50:14 PM
"Erland Sommarskog" <esquel@sommarskog.se> schreef in bericht 
news:Xns9D76F27C26394Yazorman@127.0.0.1...
> Arjen (boah123@hotmail.com) writes:
>> Web applications often use a parameter to select a specific record.
>> Hotmail, for example, uses a code like "ZIaXbPiFjyPUfJG44nag9A%3d%3d" to
>> retriev a specific mail message.
>>
>> Question 1)
>> Is an Int type not prefered?
>
> Not always. There are a couple of problem with using numbers. One is
> that sequential numbers can be easy to guess. Which can be helpful
> for intruders.
>
> In case of big web sites, they have farms of servers and the ids are
> generated on the web servers. Thus ids must be generated without risk
> for collision. This can be difficult with numbers, even with 64-bit
> integers. A common device are GUIDs, which are 128 bits, and generated
> in a random way so that the risk for collision is so small that it is
> negligible. If Hotmail uses a GUID or just a hash of some sort, I don't
> know.
>
>> Question 2)
>> What is a smart way to generate unique random numbers?
>> Date + number??? 051320101 = 05-13-2010-1
>
> In SQL Server, checksum(newid()) is a good random generator. But if
> you want your series to not include duplicates, you will need to
> track generated numbers in a table. If you generate 100000 random
> 32-bit numbers, you have a likelyhood of 0.3 of having at least one
> duplicate, if memory serves. Or if it was 0.3 for not getting any
> dups at all.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

Thanks for your reply!

When I use GUIDs as key in a relational database then I need to copy the 
specific guid into other tables (as reference to the main table). Correct?

From a performance perspective, is sql server using int values as index for 
this guid-relationship in the background?

Thanks!


0
Arjen
5/15/2010 6:03:35 PM
Arjen (boah123@hotmail.com) writes:
> When I use GUIDs as key in a relational database then I need to copy the 
> specific guid into other tables (as reference to the main table). Correct?

Correct. This is no different from other keys.

> From a performance perspective, is sql server using int values as index
> for this guid-relationship in the background? 
 
No. Operations with GUIDs are likely to be somewhat less efficient than
operations on ints, since few computers have 128-bit architecture. However,
since GUIDs are just bits, they are far more effcient than character data.
(For which lots of rules for case-insensitity etc applies.

And it would made little sense to try to distill GUIDs to integers. As
I mentioned, with 32-bit integers you have a fair chance of collision
with 100000 values - which is not much in a relational database.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
5/15/2010 8:36:20 PM
"Erland Sommarskog" <esquel@sommarskog.se> schreef in bericht 
news:Xns9D79E5F4BF92FYazorman@127.0.0.1...
> Arjen (boah123@hotmail.com) writes:
>> When I use GUIDs as key in a relational database then I need to copy the
>> specific guid into other tables (as reference to the main table). 
>> Correct?
>
> Correct. This is no different from other keys.
>
>> From a performance perspective, is sql server using int values as index
>> for this guid-relationship in the background?
>
> No. Operations with GUIDs are likely to be somewhat less efficient than
> operations on ints, since few computers have 128-bit architecture. 
> However,
> since GUIDs are just bits, they are far more effcient than character data.
> (For which lots of rules for case-insensitity etc applies.
>
> And it would made little sense to try to distill GUIDs to integers. As
> I mentioned, with 32-bit integers you have a fair chance of collision
> with 100000 values - which is not much in a relational database.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

Erland, thanks again!

About my second question / your second answer: I will keep using GUIDs 
(unique identifiers). It was not my intention to distill GUIDs to 
integers... I thought that maybe sql server distill guids to ints when the 
guid is a relationship... in other words, when the relationship "connects" 
to guids sql server will make an index based on ints because that might be 
fast to search in.

Arjen 

0
Arjen
5/16/2010 10:01:47 AM
Arjen (boah123@hotmail.com) writes:
> About my second question / your second answer: I will keep using GUIDs 
> (unique identifiers). It was not my intention to distill GUIDs to 
> integers... I thought that maybe sql server distill guids to ints when the 
> guid is a relationship... in other words, when the relationship "connects" 
> to guids sql server will make an index based on ints because that might be 
> fast to search in.
 
Just as would make little sense to you do to so, it would make little
sense to SQL Server.

True, for longer keys, say something which is varchar(30) or even longer,
an engine could use a hashing technique, and store the hash values in 
the tables. But such a hashing would have to be relatively collision-free,
and that would call for fairly long hash values. Thus, it would not be
meaningful for a 16-byte value, even less one that can cover the full 
range of 2^128 possible values.

Whether there is any product that uses such a hashing in this vein, I 
don't know.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
5/16/2010 7:20:00 PM
Reply:

Similar Artilces:

Can I add code to a rptInvoice to compute interest to the amount o
I use a rptInvoice to bill my customers. I would like to be able to add some code to the report that would compute interest on the amount owed as the receiveable remains unpaid. The interest would probably be added on a monthly basis (1/12th of a year) to reflect an annual rate. I would like the time frame to be based on the DateInvoice field and the interest be added automatically to the amount owed on a monthly basis if possible. Any help would be greatly appreciated. -- DMainland It is possible to put a calculated field on a report, e.g. a charge 7.25% on the Amount f...

Validation code
Access 2003 upon saving a form (date entered on a form) I want to look at a field and confirm it is not empty (not looking for specific data). I know I need to use an if statement but not really sure of all the syntax form name = frmDonor Field Name =last_name if me.last_name = null Then MsgBox "You Must Enter a Last Name" else DoCmd.GoToRecord , , acNewRec docmd.close end if but this is not working so I have something wrong ALSO: If I want more then one field validated - but only one field check at a time How would I do that Thanks dave Dave, Try it like this: If IsNull(...

Security Update error code
The automatic security download from Microsoft has tried several times to download Microsoft .NET Framework 1.1 SP1 Security Update for Win2000 and WinXP KB979906 and the installation has failed. Error code is O or 0x643. I am using WinXP Home Edition, Ver 5.1 and SP3. No help from MX on-line do-it-yourself stuff. Not sure what to do next. Any help or suggestions? -- Pink Lady [Crosspost to Windows Update newsgroup] OPTION A: See the "How to obtain help" section of http://support.microsoft.com/kb/979906 For home users, no-charge support is available by calli...

Delete Button Code needed !
Before I can delete a client out of my SubForm I want a code to check that it has at least 1 other client other wise you can not delete the last client connected to that horse Table is: tblHorseDetails The 3 fields are OwnerID , HorseID & OwnerPercentage What I want is when I delete OwnerID from that HorseID (I need a code to check that, that HorseID has at least 1 OwnerID) (Never Mind about Percentage) What I am trying to do is to stop a Horse having no OwnerID because it causes HAVOC later on ---------------------------------------------------------------- Current Dode: Private Su...

Journaling output in 2K7 versus 2K3
Hey guys, It appears that the journal output in 2K7 has changed to base64 encoded winmail.dat format. I don't recall that being the case in 2K3, can anyone confirm the change? Thanks, Dan ...

Developing a code that uses a combobox for an autofilter criteria
I am developing a program using Visual Basic on Excel to automate the filter function. Excel has its autofilter option but I created a userform with a combobox and a command button and the idea is to type the filter criteria (Example: I type pumps) onto the combobox so that on clicking the command button, it automatically filters the specified item. I have a list of items on a worksheet, about 700 items. Unfortunately, I'm unable to link the criteria to the user specified filter item (via the combobox). My code isn't working. I tried first with a textbox and switched to a...

Mail Merge/Zip Code
Hello, I'm using Office XP Pro SP2 with W2K Pro SP4. When performing a mail merge in Word with an Excel sheet as the recipient list the ones with a zero as the first digit of the zip code prints out as a "0" or the "0" is missing from the zip code. For example, a zip code of 01122 will print out as 0 or 1122. The cell for the zip code in Excel is formatted as: Category - Special Type - zip code Please assist. Thank you. In the Word mail merge, after you select your Excel file as a data source, you should see a 'Confirm Data Source' dialog box. (I...

Kirk Allen Evans---? how do I use your code to update my xmlDocument?
Kirk, The other day you very kindly explained how the client/server thing works. May I ask just one more question? Could you give me an example of what code I would put in the client html to post back the user input to the server xmlDocument? You gave me: One way is to use the Request.Form collection to read the values directly out of the post. System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(Server.MapPath("data/xmlfile1.xml")); if(!IsPostBack) { System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(Server.MapPath("data/xmlfile1.xml&...

POS
When you enter an item lookup code and tab over in POS, a quantity of "1" is automatically displayed by default. Is there any way to set things where when you enter the item lookup code that you are prompted to manually enter the quantity. I've read on another post about entering a "12*" in the quantity field. What is the significance of adding the * symbol? The * tells POS to add the prior quantity, so if you enter 12* and then scan the item, it will enter the quantity of 12 for that item. You don't enter the * in the quantity field, you do it before you ...

How to open a specific worksheet via code from workbook_open 04-12-10
I have a combobox which is populated with all the worksheets within the workbook. The controlsource of the combobox is linked to cell c5 on Sheet1 My question is how do i make the Excel workbook open at the worksheet which is detailed within the combobox linked cell. I've been previously using in the Workbook_Open procedure:- Worksheets(Worksheets("sheet1").Range("c5").Value) but the above code doesnt seem to work. Hope you can help! Cheers Private Sub Workbook_Open() 'Sheet CodeName 'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm S...

Copy and paste versus copy and insert copied cells
What is the difference between these commands? Try them, it is pretty easy to see the difference -- Regards, Peo Sjoblom "Alana" <Alana@discussions.microsoft.com> wrote in message news:879FED29-5541-41B0-BB2C-7108D42ED593@microsoft.com... > What is the difference between these commands? ...

How to build / code a search form
Hello, I am a fairly novice Access programmer looking for best practices/examples on building a search form. Also, on checking for the result of a query. I would like to build a form that queries for and displays an employee record. There should be 2 ways to query: By the employee number, if it is known, or by the lastname and firstname (2 fields) if the ID number is unknown. Also, is there a way to check the result of a SQL query? For examply, if the query finds a matching record, an internal code is set to 100. If now matching row is found, the code might be set to 999. Lastly, d...

Mocro code for sending a worksheet in a mail message
Does anyone know how to write a macro code to send a worksheet as an email message (not as an attachment)? Thanks -- LN Hi Lorenzo See http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Lorenzo" <Lorenzo@discussions.microsoft.com> wrote in message news:3744CF95-D6BF-4ECC-9D4E-487AE8A6CA63@microsoft.com... > Does anyone know how to write a macro code to send a worksheet as an email > message (not as an attachment)? > > Thanks > -- > LN ...

Error code 80070103 11-14-09
I'm running windows update after a fresh install of Win 7 Ultimate and it keeps trying to install an ATI driver for my ATI gfx cards and it always fails with this error. I've updated the drivers manually with the most recent ones from ATI's site but it won't recognize that they're above the version Windows is trying to feed me. Should I just hide the update or is there another reason/fix for why this is happening? Thanks! "LordofAscension" <LordofAscension@discussions.microsoft.com> wrote in message news:9E1769BA-F32B-41DB-8B16-A3A325F2CB...

Page Break Coding
Hi Anyone. I have an excel sheet with data of 5 tables with same header like: header----------- r1.. r2.. . . . r10 header---------- r1.. r2.. . . r7 header------ r1 r2 . . r15 --How can I code to give a page break after 2 table record set. I mea I want to put 2 table record in one page. I tried but its having my page break plus also the system page brea that comes after certain number of lines. Thank -- jesmi ----------------------------------------------------------------------- jesmin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=...

error code: 800B0109
Hello, I have Vista Premium. Today I noticed this error message when starting the system, but it did not prevent using the programs. The error was associated, as the message informs, to Windows defender. As a solution, I tried also to look for updates, which was impossible and the same error code appeared (800B0109). I tried many times manually but no updates were allowed. I disabled Defender to start with Vista, but I had no success, updates still not allowed, the same error code. I must emphasize that the systems are apparently normal, and my updates are configured to automat...

Help, I can't find the right code
I was trying to code using If Then based on a post I read but I think I'm using the wrong code. In my form I want if you answer "yes" to variable 1 then variable 2 must be completed however the code I am currently using required variable 2 to be completed regardless if the answer is "yes" or "no" I am new to access and the world of coding please help! Here is the code I am using: If Not IsNull(Me.Injuries_to_Staff) And IsNull(Me.Explain_Injury) Then Cancel = True MsgBox "Explanation of Injury Is Required" Me.Explain_Injury.SetFocus End I...

Lookup code working in RMS
Hi all, I m going to implement SO in Garments store.They are already using Look up code. Let me explain how they r. Here is the look up code example: 12122 0202 101 The first 5 digits represent item code, 2nd represent color code, 3 represent fabric code.How RMS can handle this look up code.can any1 explain me??We have Deparment & category in item maintenance form.I guess we have to make setups for COLOR & FABRIC . wat would u suggest? -- With Regards Junaid Idrees Associate Project Manager Enterprise Business Solutions 4th Floor mehdi towers,shahra-e-faisal karachi,Pakistan....

Code for eConnect
Private Function fncSettingCust(ByVal strNumber As String, ByVal strName As String, ByVal strAddrCode As String, _ ByVal strClass As String, ByVal strBTAddrCode As String, ByVal strBTAddr1 As String, ByVal strBTAddr2 As String, _ ByVal strBTCity As String, ByVal strBTState As String, ByVal strBTPostalCode As String, ByVal strBTCountry As String, _ ByVal strBTPhone1 As String, ByVal strBTPhone2 As String, ByVal strBTPhone3 As String, ByVal strBTFax As String, _ ByVal strSTAddrCode As String, ByVal strSTAddr1 As String, _ ByVal strSTAddr2 As String, ByVal strSTCity As String,...

Validation code for macro to run
Hi...the code below works fine, but how do I change it so that it doesn't look for a blank cell (""), but looks for specific text in a cell? There is an If formula in cell C269 so that if 2 figures balance then this cell displays "TRUE", otherwise it displays "FALSE". The code mustn't run if Cell C269 displays "FALSE". If [C269] = "" Then MsgBox "The figures don't balance!!" Exit Sub End If Range("E1:E48").Select Selection.Copy End Sub Thanks. Rob Rob, try this, If [C269] =...

Code generation with types from other schemas
I want to be able to define types in one schema, and then use them in other schemas. So far so good. Now I also want to be able to generate classes from these schemas and use them independantly, and in their own namespaces. For example, schema1.xsd ==> schema1.cs schema2.xsd ==> schema2.cs where there are types from schema1 used in schema2. When I use xsd.exe to generate schema2.cs and an <xs:import> element, it works, but also regenerates the types from the schema1.cs. I guess the question is, is there a simple way to generate code for what I'm trying to do, or do I n...

RMS should show what bar code it is going to print
Okk when a item is made in RMS and the customer makes his own barcode and selects a barcode format to print it should show at the bottom of the screen what barcode is being created(as in the case of UPC, EAN it has a check Digit) That way the customer can put in the check digit in the number so it will scann after printing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the...

vaccinations for influenza what is the code
WHAT IS THE NAPPI CODE AND ICD10 FOR FLU VACCINATIONS ...

code or macro that would save data then clear form
I have a form built that I would like to ba able to create a database of saved data from before excuting my clear reset macro. Example would be: Transfer data from my form, save on the next available row in another worksheet then clear form. Any ideas? I'd start on Debra Dalgleish's site: http://contextures.com/xlForm02.html and you didn't ask for this: http://contextures.com/xlForm03.html Japs wrote: > > I have a form built that I would like to ba able to create a database of > saved data from before excuting my clear reset macro. > Example would be: > Transf...

SQL Server failed with error code 0xc0000000 to spawn a thread
Please help me resolve the error listed below that I received yeseterday from one of my SQL Server 2005 database servers. Error: SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Thanks, Joe K. (JoeK@discussions.microsoft.com) writes: > Please help me resolve the error listed below that I received yeseterday > from one of my SQL Server 2005 database servers. > > Error: > SQL Server failed with error code 0xc0000000 to spawn a thread to > process a new login or connection. That looks bad. Do you ...