I am considering using GUID for my primary keys in a SQL Server 2005 DB, but regarding all performance issues I would like to use the new newsequentialid() function. Since it can only be used as a DEFAULT constraint and not from CLR, I would have to generate it directly on the server. I red the thread at http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1547&HighLight=1 where it is clearly explained that "newid() can't be used to generate GUIDs in a query, as there is no way to read the value generated back properly". However, a new OUTPUT statement would make this much easier:

insert Employee (EmployeeName) output Inserted.EmployeeID values ('Name')

Are there any plans to support newsequentialid() in LLBLGen? IMHO It would be a VERY valuable addition to an already excellent tool

Regards,

Denis

However, what's the downside of adding 1 line of code to add the GUID yourself? The routine used by Guid.NewGuid() in .NET and the GUID creation routines in sqlserver are the same (though are ran on different machines perhaps). THe thing is that with a GUID in 2005, special code has to be added to generic code to pull back the value, so a special case routine has to be added for this, which is not that great, unless it's unavoidable.

Could you elaborate a bit why you need it in the db instead of adding the guid in .NET code?

Otis wrote:

However, what's the downside of adding 1 line of code to add the GUID yourself? The routine used by Guid.NewGuid() in .NET and the GUID creation routines in sqlserver are the same (though are ran on different machines perhaps). THe thing is that with a GUID in 2005, special code has to be added to generic code to pull back the value, so a special case routine has to be added for this, which is not that great, unless it's unavoidable.

Could you elaborate a bit why you need it in the db instead of adding the guid in .NET code?

Because Guid.NewGuid() in .NET will generate an "ordinary", not a sequential GUID. As we all know, using GUID as PKs can cause serious performance problems with larger tables. On the other hand, I must use GUIDs because of various replication/distributed arch. issues.

SQL Server 2005 addresses many of the current performance problems associated with using a "ordinary" GUID-based index by adding the newsequentialid() function. Newsequentialid() addresses some of the performance problems inherent in the current implementation of newid() by generating a GUID that will sort in an increasing order, making it more suitable for indexing.

newseqientialguid() is available ONLY in a DEFAULT constraint, you cannot use it in SPs and .NET languages for obvious reasons. To make things worse, pulling back the newly added GUID PK is only a part of the problem: since LLBLGen doesn't allow me to put a newid() and related DEFAULT values, I cannot use sequential GUIDs at all!

The performance enhancement is a good point. I've added it to the list of sqlserver 2005 features to support in v2.0. Thanks for the explanation

You can also use COMBS which is talked about in this arcticle below.

combs article

From what one user posted, they are better then the sequential version in Yukon...

I'm aware of the COMB approach, as I was using it in some previous projects. However, I think that we should switch to the "official" route and use newsequentialid.

susacd wrote:

I'm aware of the COMB approach, as I was using it in some previous projects. However, I think that we should switch to the "official" route and use newsequentialid.

I agree, though at the moment, newsequentialid() isn't supported in llblgen pro yet. So either use GUID's, or COMB's, to get better performance and switch to newsequentialid() once it is supported.

OK, thanks. Do you have any estimates on when the new release will be ready? I know you get this question too often In other words, if at least a beta release will not be released during the month of January, I will be forced to use COMBs.

susacd wrote:

OK, thanks. Do you have any estimates on when the new release will be ready? I know you get this question too often In other words, if at least a beta release will not be released during the month of January, I will be forced to use COMBs.

I expect to have something which is close to beta around the end of march 2006.

The query I will execute looks like the following:

declare @insertedTmp table(id uniqueidentifier)
insert into newsequentialtester (Name) 
output inserted.ID INTO @insertedTmp
VALUES ('lalala');
select top 1 @newID=id from @insertedTmp

Only for PK guid fields which aren't changed. This has to be done this way, as the core can deal with output parameters for inserts, but not with returned sets, and output inserted.id is returning a set (of 1 field and 1 row).

Haven't tested it from ADO.NET. If it doesn't work from ado.net, it's dropped.

(edit) ok works like a charm. Supported in v2.0 for sqlserver 2005, pk's with guid.