Incremental GUID and unique identifier

Question

0.00/5 (No votes)

See more:

I would like to generate Guids in Order using C# and SQL Server

Eg

00000000-0000-0000-0000-000000000000
00000000-0000-0000-0000-000000000001
00000000-0000-0000-0000-000000000002
...................................
00000000-0000-0000-0000-00000000000F
00000000-0000-0000-0000-000000000010
.................
.............
4FF2EAFF-32BF-445D-8A61-2A9303828C2D

I need to be able to do this in SQL server (like setting identity spec for int)
and I need to do this in C# like Guid.NextGuid() and it increments.

Thanx

Posted 3-May-11 4:42am

L Viljoen

Add a Solution

Comments

Wannes Geysen 3-May-11 10:46am

do you really need a guid or just an index which automatically increments?

L Viljoen 3-May-11 11:12am

I am aware of int and identity specification but I need something reeeeeaaaaaly big

Pete O'Hanlon 3-May-11 15:34pm

Why do you need them to be in order? The only reason that identity autoincrements is that it is simpler to add 1 to the max value (a vast simplification of the way it works) in order to guarantee a unique value. As a Guid is inherently unique, there is no need to order it.

L Viljoen 3-May-11 15:50pm

Because one record need to be processed before the other, one of the many reasons why one would request a exceedingly long data type that can be ordered.

No worries I am going to go with jeremy's solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jeremy Hutchinson · Accepted Answer · 2011-05-03T06:27:00

I agree with the "solution" proposed by Kieth Barrow. It's you're really abusing the GUID field by doing this.

That being said you have a few options easier than what you're trying to do. How many records are do you need to uniquely identify? Chances are you can use BigInt which allows numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.

This table definition would allow for 18,446,744,073,709,551,615 unique keys.

SQL

Create table Test (
    Key    bigint identity(-9223372036854775808, 1)
)

You could use decimal which would allows -10e+38 to 10e+38

SQL

create table Test (
    Key    test1 decimal(38,0) identity(-10000000000000000000000000000000000000,1)
)

That would give you 2e+38 values, which is still a little short of the ~3.4e+38 values you could have with a guid, but I'd be shocked if you need that many.

2e+38 = 2 Undecillion = 20,000,000,000,000,000,000,000,000,000,000,000,000

Keith Barrow · Accepted Answer · 2011-05-03T04:56:00

Solution 1

That defeats the point of a GUID. They are supposed to be random to the point that they are pretty much unique. for Globally Unique ID read "Universally Unique Id (almost guaranteed :))"
By generating values sequentially like this you are breaking the point of them: if you run your code from scratch you will get the same "GUID" twice.

You should settle on either a GUID or a Auto incrementing index, you can't have both. If there is a reason behind what you are trying to achieve, can you add it to your question, and comment on this reply that you have updated it. I'll take another look.

Posted 3-May-11 4:56am

Keith Barrow

Comments

Sandeep Mewara 3-May-11 10:59am

My 5!

L Viljoen 3-May-11 11:12am

If you can point me to a data type that can hold the same amount of records as a Guid but that auto increments I would gladly accept that as the solution. I would like to use this because I need to record the last record processed on my program and then use a compare to get the records that still needs to be processed

Rick Shaub 3-May-11 12:42pm

You shouldn't be using a surrogate key to determine status. You should have some data element such as a time/date stamp for "record created" and a flag to indicate "record processed" or some other similar sdcheme.

L Viljoen 3-May-11 15:57pm

Yes i thought about using DateTime , just one problem if you had more than 1 remote terminal directly connecting to the sql server.

Their time need to be in perfect sync to ensure that the order is maintained.

If terminal 1 writes to the database and then terminal 2 then terminal 1 again it should keep that order (If their clocks are out of sync they can and will differ so the auto assign should happen on SQL )

Long story short I need the longest possible data type that order is perfectly maintained as the record get entered.

I would like to run a compare when processing these records eg get all records where RecordID > lastRecordProcessed

Follow.

Rick Shaub 3-May-11 16:12pm

I think you miss what I'm saying. Make your primary key a GUID. Have a bit or a boolean flag that indicates whether the record has been processed and a date field to indicate when the record was entered. Do a query "where isProcessed != true order by timeProcessed" to get the records that need to be processed in order.