Retrieving NEWSEQUENTIALID

Hi,
We are thinking of changing the primary key of our table from GUID type to
squential id using the new NEWSEQUENTIALID function. But the issue we are
having is how to efficiently retrieve the generated value because we need to
pass this information back to the client app. With GUID we have a stored
procedure that calls NEWID and then we assign the generated value to the
primary key column. But with NEWSEQUENTIALID it can only be used as DEFAULT
constraint. We came up with a solution to retreive the generated id but the
performance is very slow.
Has anyone come up with a better strategy in doing this?
Thanks.Roy
Read this article in the BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e06d2cab-f1ff-42f1-8550-
6aaec57be36f.htm
I think , as the BOL suggests to use a scalar UDF to rertieve the value
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> I think , as the BOL suggests to use a scalar UDF to rertieve the value
Uri,
I'm not sure if we have a different rev of BOL (I'm looking at the
December version), but mine seems to indicate that you CANNOT use a UDF on
NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
CREATE TABLE x1
(
col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
)
GO
INSERT x1
OUTPUT inserted.col
DEFAULT VALUES
GO
DROP TABLE x1
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

>
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>|||DOH!!!! Thanks Adam. I did not read it properly , ( I need to freshen up
myself in the morning)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OUOE$tzmGHA.3440@.TK2MSFTNGP03.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||Another approach is to assign the uniqueidentifier value in application code
rather than in SQL Server. That way, you don't need to retrieve the
assigned value at all because you already know it. To get a sequential GUID
value in application code, call the UuidCreateSequential RPC function.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||Thanks the response.
However, this is the solution we tried before but we saw a performance
degradation (during lots of inserts) compare to the old way of using GUIDs.
Roy
"Adam Machanic" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
>|||Thanks Dan.
We haven't tried this approach yet. We will it give it a try.
Roy
"Dan Guzman" wrote:

> Another approach is to assign the uniqueidentifier value in application co
de
> rather than in SQL Server. That way, you don't need to retrieve the
> assigned value at all because you already know it. To get a sequential GU
ID
> value in application code, call the UuidCreateSequential RPC function.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>
>