Generate C# Long Unique ID

views:

1860

answers:

+1 Q:

Generate C# Long Unique ID

Hey! I have an SQL database with a set of tables that have Unique Id's. For the longest time I have been using the unique Identifier datatype and passing in a guid from my C# interface. However for the purpose of speed when querying I have decided to switch to using a bigint and passing in a long.

Whats the easiest way to create unique longs each time I run the code so that there is no duplication of ID?

regards

+4 A:

The only way you could guarantee that the bigint is unique within the database table, is to let SQL Server generate it for you - make it an IDENTITY column.

Did you actually measure performance with uniqueidentifier and found it too slow?

John Saunders 2009-06-23 10:52:33

No, but surely a field storing an ID of value 3h38d9383-3j394juj93-3jd309d etc. would be much slower than simply using a single integer that increments from 1 up to 1000000 etc.

Goober 2009-06-23 10:54:59

We have in the past and found that even for relatively small amounts of data (like 50 to 100k records) it can be brutally slow.

Kevin 2009-06-23 10:57:16

@Goober: A Guid is also a single integer, it's only larger.

Guffa 2009-06-23 10:58:54

Its a string that can be converted to an number. For comparisons on sql server it's not counted as a number.

Kevin 2009-06-23 11:04:17

@Kevin: I'm sure you found this in your database. I wonder if Goober found it in his database, with his load, or whether he's speculating.

John Saunders 2009-06-23 11:05:21

@Kevin: how do you know how the comparisons are done on SQL Server?

John Saunders 2009-06-23 11:05:58

He said for the purpose of querying which lead me to believe that it was in a database. I would have figured that if it was in code, he would have used the word searching.

Kevin 2009-06-23 11:08:18

@Kevin: you said, "for comparisons on sql server its not counted as a number". I meant, how do you know whether sql server treats it as a number?

John Saunders 2009-06-23 11:13:42

@Kevin: you are wrong about Guid columns being "not counted as a number". Check out http://stackoverflow.com/questions/537145/is-it-a-bad-idea-to-use-guids-as-primary-keys-in-ms-sql

AgileJon 2009-06-23 11:13:47

huh-I'll be darned, I'll have to talk to the dba about that....

Kevin 2009-06-23 11:23:03

There are other options for generating unique numbers besides a seed. One possibility is to generate a number based on time, something like the ticks, or better calculate the number of seconds since the start of 2009. Then append a unique digit based on the location (or account if you are not doing it on the server) the number was created to the end of the number (the least significant digit).

So if your new unique number was created on app server 1 and it's id was 42 and it's has been 4000 seconds since the start of 2009, your new identifier would be 400042. The next one generated could be 400942, and one generated from a different server at exactly the same time could be 400943.

Kevin 2009-06-23 11:03:05

+1 A:

If speed is an issue, you can get query improvements if using a uniqueidentifier by generating it using the newsequantialID() function rather than newID(). The new method generates sequential uniqueidentifiers

Conrad 2009-06-23 11:17:10

Where do you see the slowdown? Query or Insert/Update? I ask because GUID's as primary keys aren't great for insert/update because they're not sequential like IDENTITY's and can cause some thrashing in the clustered index of a key. But SQLServer has sequential-guids now that resolve that. I hear a lot about using GUID's as keys being slow but I wonder how much this is really true. Especially on 64-bit machines - is comparing 128-bit numbers really that much slower than comparing 64 or even 32 bit numbers?

n8wrl 2009-06-23 11:17:20

ansaurus

tags:

views:

answers:

Generate C# Long Unique ID

related questions