generate guid for every row in a column

Question

I Have a stored procedure that has a table with one column and I need to generate a NEWID() for each row in that column. Would I only be able to accomplish this with a loop?

+---+    +--------------------------------------+---+
| a |    | FD16A8B5-DBE6-46AB-A59A-6B6674E9A78D | a |
| b | => | 9E4A6EE6-1C95-4C7F-A666-F88B32D24B59 | b |
| c |    | 468C0B23-5A7E-404E-A9CB-F624BDA476DA | c |
+---+    +--------------------------------------+---+

A loop is not necessary and generally speaking you should try to avoid loops in SQL Server, using set-based operations instead (as demonstrated in the accepted answer from @bluefeet). — alroc, Jul 24 '13 at 17:03
Does this answer your question? [How to generate a new Guid in stored procedure?](https://stackoverflow.com/questions/3938113/how-to-generate-a-new-guid-in-stored-procedure) — KyleMit, Nov 19 '19 at 00:50

score 11 · Accepted Answer · answered Jul 24 '13 at 16:14

11

You should be able to select from your table and include the newid() to generate the value for each row:

select newid(), col
from yourtable;

See SQL Fiddle with Demo

answered Jul 24 '13 at 16:14

Taryn

242,637
56
362
405

What if I needed to have access to the value created by newid() somewhere within the same query? – Jul 24 '13 at 17:56
1

@Joe My suggestion would be to post a new question with your new requirements. – Taryn Jul 24 '13 at 18:11

score 8 · Answer 2 · answered Jul 24 '13 at 16:23

8

You can create a column with the new guid

alter table yourtable add id varchar(40) not null default NEWID()

http://sqlfiddle.com/#!3/b3c31/1

answered Jul 24 '13 at 16:23

gariel

687
3
13

score 0 · Answer 3 · answered Jul 24 '13 at 17:38

newid() will definately work. But will also create "fragmented" values.

This may be beneficial or detrimental (think guid as a Primary Key) to your needs.

Here is a procedure I wrote a while back to "kinda help" with fragmentation.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspNewSequentialUUIDCreateSingle]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspNewSequentialUUIDCreateSingle]
GO

/*

--START TEST

declare @returnCode int
declare @ReturnUUID uniqueidentifier

EXEC @returnCode = dbo.uspNewSequentialUUIDCreateSingle @ReturnUUID output
print @ReturnUUID
print '/@returnCode/'
print @returnCode


--loop test,,,loop exists for TESTING only fyi

declare @counter int
select @counter = 1000

while @counter > 0
    begin
        EXEC @returnCode = dbo.uspNewSequentialUUIDCreateSingle @ReturnUUID output
        print @ReturnUUID
        select @counter = @counter - 1
    end


--END TEST CODE


*/

CREATE PROCEDURE [dbo].[uspNewSequentialUUIDCreateSingle] (
@ReturnUUID uniqueidentifier output  --return
)

AS


--//You can use NEWSEQUENTIALID() to generate GUIDs to reduce page contention at the leaf level of indexes.

SET NOCOUNT ON 

--      declare @ReturnUUID uniqueidentifier

declare @t table ( id int , uuid uniqueidentifier default newsequentialid() )
insert into @t ( id ) values (0)
select @ReturnUUID = uuid from @t



SET NOCOUNT OFF
GO

GRANT EXECUTE ON dbo.uspNewSequentialUUIDCreateSingle TO public

GO

generate guid for every row in a column

3 Answers3

Linked