My goal is to create a stored procedure that generates new GUIDs using NEWSEQUENTIALID that can then be used when inserting data into a different table in the same database. For example, it would be like the stored procedure described in this StackOverflow answer.

Thus, my question is: are there any negatives, cons, or issues in creating a stored procedure like this and using the GUIDs it generates for insertions into a different table?

For example, would generating GUIDs like this be non-atomic and cause duplicate GUIDs values? Or, for example, is a GUID generated by NEWSEQUENTIALID() guaranteed to be unique only for the table it was generated in and thus, inappropriate to reuse in another table?

In other words, even though such an implementation might be a kludge, is it "safe" to generate GUIDs like this?

The only potential concerns I've been able to find are described in the previously linked NEWSEQUENTIALID documentation:

1. If privacy is a concern, do not use this function. It is possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID.

2. The UuidCreateSequential function has hardware dependencies. On SQL Server, clusters of sequential values can develop when databases (such as contained databases) are moved to other computers. When using Always On and on SQL Database, clusters of sequential values can develop if the database fails over to a different computer.

And from UuidCreateSequential documentation:

3. In this case, the generated UUID is a valid identifier, and is guaranteed to be unique among all UUIDs generated on the computer. However, the possibility exists that another computer without an ethernet/token ring address generated the identical UUID. Therefore you should never use this UUID to identify an object that is not strictly local to your computer. Computers with ethernet/token ring addresses generate UUIDs that are guaranteed to be globally unique.

The privacy concern described in #1 is negligible for my purposes. I think concern #2 and #3 are negligible, as well, because the table where the GUIDs will be inserted is within the same database that would generate the GUIDs.

CodePudding user response：

That should be fine. But you can simplify the generating procedure like this:

CREATE OR ALTER PROCEDURE GenerateNewSequentalId  @id as uniqueidentifier OUTPUT
AS
BEGIN
    set nocount on;
    declare @returnid table (id uniqueidentifier);
    declare @test table(id uniqueidentifier default newsequentialid());
    INSERT INTO @test 
    output inserted.id into @returnid
    default values;

    select @id = r.id from @returnid r;
END
GO

/* Test the Procedure */
declare @myid uniqueidentifier
exec GenerateNewSequentalId @myid output
select @myid