tags:

views:

888

answers:

3

Is there any way to get the functionality of the Sql Server 2005+ Sequential Guid generator without inserting records to read it back on round trip or invoking a native win dll call? I saw someone answer with a way of using rpcrt4.dll but I'm not sure if that would be able to work from my hosted environment for production.

Edit: Working with @John Boker's answer I attempted to turn it into more of a GuidComb generator instead of being dependent on the last generated Guid other than starting over. That for the seed instead of starting with Guid.Empty that I use

public SequentialGuid()
{
    var tempGuid = Guid.NewGuid();
    var bytes = tempGuid.ToByteArray();
    var time = DateTime.Now;
    bytes[3] = (byte) time.Year;
    bytes[2] = (byte) time.Month;
    bytes[1] = (byte) time.Day;
    bytes[0] = (byte) time.Hour;
    bytes[5] = (byte) time.Minute;
    bytes[4] = (byte) time.Second;
    CurrentGuid = new Guid(bytes);
}

I based that off the comments on

// 3 - the least significant byte in Guid ByteArray 
        [for SQL Server ORDER BY clause]
// 10 - the most significant byte in Guid ByteArray 
        [for SQL Server ORDERY BY clause]
SqlOrderMap = new[] {3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10};

Does this look like the way I'd want to seed a guid with the DateTime or does it look like I should do it in reverse and work backwards from the end of the SqlOrderMap indexes? I'm not too concerned about their being a paging break anytime an initial guid would be created since it would only occur during application recycles.

+3  A: 

this person came up with something to make sequential guids, here's a link

http://developmenttips.blogspot.com/2008/03/generate-sequential-guids-for-sql.html

John Boker
+2  A: 

First of all - what is a purpose for this?

I'm asking this because the whole purpose of Guid is to be random and unique. If you just need something incremental, you can use Int type field and set it to be AutoIncremental.

Vitaly
from http://www.yafla.com/dennisforbes/Sequential-GUIDs-in-SQL-Server/Sequential-GUIDs-in-SQL-Server.html :As discussed in the entry on using GUIDs in your database, GUIDs in SQL Server 2000 are, at least from the user's perspective, "random". This can lead to a fragmentation and splits in your data, and it's a common reason to avoid GUIDs in the first place.
John Boker
This is a comment....
Austin Salonen
This does not help with a solution to the problem.
John Boker
Well, that actually might help. I'd say that there's probably a misunderstanding of the whole purpose of GUIDs. If Chris just needs something incremental, there's another good solution called Int + AutoIncrement. There's no need for GUID in this case.Can someone explain me why someone would need to have incremental Guids?!
Vitaly
Vitaly, you are missing the point. SQL200x does have a Sequential Guid generator to optimize indexing based on Guids. Plenty terms to seearch on.
Henk Holterman
Henk, optimization has to be done only by results of speed tests. There're many examples when there were made wrong assumptions and "optimization" based on these assumptions didn't really work.I was wondering if there's any real purpose for sequential guids besides assumptions that this is the best way to go for any table structure and any queries by that table. Also check this article with speed tests on Guid which compares integer vs sequential guids: http://www.sql-server-performance.com/articles/per/guid_performance_p2.aspx
Vitaly
The purpose of this to have a guid that will generate keys close to each other from my application to be sent to the database that i can have a clustered index on my primary key column (the guid).
Chris Marisic
@Chris, I see what you mean, this is exactly what I was saying about. Guids have pros and cons. Pros of Guid is absolute uniqueness. So for example if you merge 2 databases, you don't need to create new ids (because they will never intersect). Cons are that Guid is more consuming than integer. You can use Sequential Guids and get a better performance than random ones, but you will lose the advantage: absolute uniqueness. And that could be ok: we don't merge databases too often, but you have another alternative - integer keys that support autoincrement, so please consider using them.
Vitaly
Depending on the database to generate my keys for me is not acceptable IMO.
Chris Marisic
@Chris, we're talking about SQL Server 2005+ here.
Vitaly
A: 

As far I know NHibernate have special generator, called GuidCombGenerator. You can look on it.

Mike Chaliy