Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get NEWSEQUENTIALID


Get NEWSEQUENTIALID

Author
Message
stevanuz
stevanuz
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 40
Comments posted to this topic are about the item Get NEWSEQUENTIALID
gil.milbauer
gil.milbauer
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 133
Good idea.

Maybe I'm missing something, but, I don't think you need @NextSequentialID at all, or the XxX column, or some other complexity in the table.

This seems to work:

CREATE PROCEDURE dbo.sp_NewSequentialId
(@Id AS UNIQUEIDENTIFIER OUTPUT)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @NewSequentialId AS TABLE (Id UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID()))

INSERT INTO @NewSequentialId DEFAULT VALUES;

SELECT @Id = Id
FROM @NewSequentialId;
END
GO


stevanuz
stevanuz
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 40
thanks a lot...

"INSERT INTO @NewSequentialId DEFAULT VALUES;"

actually i don't know i can use DEFAULT VALUES Smile-
stevanuz
stevanuz
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 40
Yes, that should be the right way to do.
Thanks gil.milbauer...

CREATE PROCEDURE dbo.sp_NewSequentialId
(@Id AS UNIQUEIDENTIFIER OUTPUT)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @NewSequentialId AS TABLE (Id UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID()))

INSERT INTO @NewSequentialId DEFAULT VALUES;

SELECT @Id = Id
FROM @NewSequentialId;
END
GO
Patrick2525
Patrick2525
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 180
I think I'm missing something too...

The purpose of the newsequentialid() is to be sequential in the context of a given table, yes? Therefore the SP would need to know the last uniqueidentifier used in a particular table in order to generate the next sequential uniqueidentifier, yes?

Perhaps the SP would need to accept a table name as a parameter, which it would query to find the last uniqueidentifier that would then use as the 'seed' value in the temp table, then inserting a new row to get the newsequentialid.

No?

Sorry if I'm missing something
gil.milbauer
gil.milbauer
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 133
Even though it has "Sequential" in name, the values themselves are not really adjacent to each other. It's just that the next value will be greater than any previous value from the function on this computer, so there's no need to reference the last value in the table. I guess that the "sequential" part means that if you use it as a clustered key, each row will be stored sequentially in the order they were created.
Patrick2525
Patrick2525
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 180
Ok... I guess I didn't really understand how the uniqueidentifier is generated - looks like it involves a sort of hex timestamp for the first 4 octets.

Thanks!
paul.millar
paul.millar
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 473
Hello SQL-ers,

It would seem that the very exact sounding error message is misleading...

"The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression."

One of our our devs kindly disproved this by inserting into a table with a newsequentialid() default column, just by a slightly different insert syntax.

Imagine the table has two columns, the second being the default column in question.

I was using:

insert [thetable] select 1

...which got the error msg - (oh no, I'm going to have to use newid() which causes fragmentation etc)

Whereas he succeeded using a column list in the insert defintion, ie:

insert [thetable] (col1) select 1

...which resulted in 1 in the first column and a correct newsequentialid() in the second column.

(this is all part of manually cranking the prep of tables for some merge replication crusade :-))
gil.milbauer
gil.milbauer
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 133
Paul,

It seems that you had just tried to supply the wrong number of columns in the original insert, and has nothing to do with newsequentialid() (it could have been any datatype with a default). If you don't specify the list of columns to be inserted, then SQL Server will expect all of the non-identity columns to be supplied by the subsequent value-providing clause.

I'm surprised you didn't get this error:
Column name or number of supplied values does not match table definition.


Specifying the column list (always a good practice) told SQL Server which column(s) to expect and to use the default value for any other column(s).
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8708 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search