February 2, 2009 at 7:02 am
Hi all. I had written the good folks at SQLDev.net about porting their SQL 2000 function to generate a sequential id on demand and having it work in SQL 2005. Gert Drapers responded, and I am NOT taking credit for this, but it's such a simple idea to get around the default newsequentilid() conundrum, I had to post this.
create proc dbo.seqguid @g uniqueidentifier output
as
begin
set nocount on
declare @guids table ( guid uniqueidentifier default newsequentialid() )
insert @guids(guid) values(default)
select @g = guid from @guids
return @@rowcount
end
GO
declare @g uniqueidentifier
exec dbo.seqguid @g output
select @g
GO
Sometimes the best solutions are the simplest ones. I have never been able to find an adequate answer online on how to generate a sequential GUID on demand until now.
Thanks Gert!
Gaby A.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
February 2, 2009 at 8:45 am
It works like a charm. But may I ask why you are concerned that a guid be sequential? I ask because when there is a need to preserve an entry sequence, I've always favored using an integer id, because of the smaller column size and the ease of testing for gaps where rows have been deleted.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 2, 2009 at 9:40 am
But may I ask why you are concerned that a guid be sequential?
guids are "replication friendly", identity integers are not.
* Noel
February 2, 2009 at 10:44 am
noeld (2/2/2009)
But may I ask why you are concerned that a guid be sequential?
guids are "replication friendly", identity integers are not.
Sequential is necessary for me to preserve any order, but sometimes I need a little more control than ...default newsequentialid() provides in a table definition.
Gaby A.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
February 2, 2009 at 5:02 pm
[font="Verdana"]I'd be interested in learning what sort of scenario you use that for.[/font]
February 4, 2009 at 6:50 am
Bruce W Cassidy (2/2/2009)
[font="Verdana"]I'd be interested in learning what sort of scenario you use that for.[/font]
It would be useful if I have a uniqueidentifier column that is not a primary key and as such may be NULL once in awhile. With newsequentialid(), I'd have all entries fill automatically. If this column would ever get indexed, it would probably be nonclustered.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
February 4, 2009 at 1:50 pm
[font="Verdana"]Ah, that makes sense!
You could always do the "sixth normal form" thing, and have a related table into which you fill in the GUIDs as/when you need them (as distinct from having a nullable column). But I think your method is probably easier.
[/font]
February 4, 2009 at 2:49 pm
Hey Gaby, I'm still curious. Why would you want a uniqueidentifier for a row to ever be null, even if it isn't the primary key? I understand how the proc in question solves your problem of allowing nulls, but I am scratching my head trying to imagine the scenario in which you would need that.
I assume you are assigning the value with an update at some point after the row has been created, but it seems redundant to have two columns which uniquely identify a row. By "two columns", I mean your column which can contain nulls, and the column which has the primary key.
Thanks for your patience.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 5, 2009 at 6:28 am
Bob Hovious (2/4/2009)
Hey Gaby, I'm still curious. Why would you want a uniqueidentifier for a row to ever be null, even if it isn't the primary key? I understand how the proc in question solves your problem of allowing nulls, but I am scratching my head trying to imagine the scenario in which you would need that.I assume you are assigning the value with an update at some point after the row has been created, but it seems redundant to have two columns which uniquely identify a row. By "two columns", I mean your column which can contain nulls, and the column which has the primary key.
Thanks for your patience.
Bob
I definitely agree with you Bob. Unfortunately, I have to contend with production designs (both in 2000 and 2005) whose normal forms were set (or not set) aeons ago. Sometimes I have to do queries on tables with no identity column (yes, unfortunately they do exist). This would be a way to keep the tables indexed in an order different from how they are arranged which may involve me looking at subsets of the table and having that special GUID applied there before continuing on to the rest of the table.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
February 5, 2009 at 7:43 am
Thank you for sharing, Gaby. I hate to come across as being dense. I guess I've just been fortunate enough to have the freedom to make those kinds of changes and corrections when I had to.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 5, 2009 at 8:46 am
Bob Hovious (2/5/2009)
Thank you for sharing, Gaby. I hate to come across as being dense. I guess I've just been fortunate enough to have the freedom to make those kinds of changes and corrections when I had to.
No worries, you didn't come across as that at all. As in all things SQL, there is always a simpler way to do things, but I decided to go this route to once and for all figure out how to generate the sequential GUID's on demand.
Cheers!
Gaby A.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply