8

I need to create a list of GUID's in SQL Server 2008 R2 and I am using the NEWID() function.

This is what I am trying but I just get only one ID:

SELECT TOP 100 NEWID()

I am new to SQL Server and I don't know if there is a way to do that or a way to create a loop for do it.

I don't need to persist those GUID's I just want to show them on screen.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ianaya89
  • 4,153
  • 3
  • 26
  • 34

4 Answers4

19

You can use an arbitrary table as "sequence-generator":

SELECT TOP (100) Guid = NEWID() 
FROM [master]..spt_values;

Demo

Note that this table contains only 2346 rows.

Worth reading: Generate a set or sequence without loops

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Slick. I've not seen this before. – Ryan Nigro Dec 09 '14 at 15:21
  • 2
    `master.sys.all_columns` is usually a good table to use for such purposes :). It has enough rows to suit most needs, and if not, CROSS JOINing it to itself usually takes care of that. – Solomon Rutzky Dec 09 '14 at 15:21
  • It contains only 2346 rows *on your machine*. Obviously this will vary across versions of SQL Server. It's good enough for a one-off effort, but for more reliable results, populate your own numbers table (see http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers for more information). Relying on particular system tables for this is icky (as well as not very intuitive to other readers). – Jeroen Mostert Dec 09 '14 at 15:24
  • @JeroenMostert: yes, you're right. You'll find more informations at the link i've provided. I just wanted to emphasize that it's a limited set but seems to meet OP's requirements. I'd also prefer a number-table in production systems. – Tim Schmelter Dec 09 '14 at 15:29
  • @JeroenMostert re "Relying on particular system tables for this is icky", I would disagree since the "this" we are talking about is only 100 rows. – Solomon Rutzky Dec 09 '14 at 15:35
  • @srutzky: the "this" I am referring to is "anything that's not a one-off and will need maintenance". For the question as asked, after generating the 100 GUIDs the entire query will likely disappear, so there's no need to care. – Jeroen Mostert Dec 09 '14 at 15:44
4

You could do this:

DECLARE @nbrOf INT
SET @nbrOf=100

;WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < @nbrOf )
SELECT
    NEWID()
FROM
    Nbrs
OPTION ( MAXRECURSION 0 )

Or with cross joins:

DECLARE @nbrOf INT
SET @nbrOf=100
;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   Nbrs(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT 
    NEWID()
FROM 
    Nbrs
WHERE
    Nbrs.N<=@nbrOf
Arion
  • 31,011
  • 10
  • 70
  • 88
  • 1
    Be careful. Using a cte to count is still a row by row type of thing. http://www.sqlservercentral.com/articles/T-SQL/74118/ – Sean Lange Dec 09 '14 at 15:23
3

Just use a loop. Try the following:

create table #GUIDS (tempID uniqueidentifier)

declare @i int = 0

while (@i < 100)
begin

  insert into #GUIDS
  select newid()

  set @i = @i + 1
end

select * from #GUIDS

drop table #GUIDS

NOTE: This is not a good solution to use for a large number of iterations, as it loops through the result in a row-by-row fashion.

Ryan Nigro
  • 4,389
  • 2
  • 17
  • 23
  • 1
    This works but a tally/numbers table is better. Or any table that has that many rows. Looping is just horrible for performance in sql server. – Sean Lange Dec 09 '14 at 15:22
  • Yeah, I'd give a different answer for a large number of iterations. For 100 rows, the performance impact is meaningless. – Ryan Nigro Dec 09 '14 at 15:23
  • Tim's answer re: the sequence generator is the way to go for sure though. – Ryan Nigro Dec 09 '14 at 15:23
  • Ryan, it's not just a matter of performance. This is a lot of code to do something rather simple. – Solomon Rutzky Dec 09 '14 at 15:24
  • True about performance for 1000 rows but...if somebody sees this and it works for 100 rows it will be copied into a million row implementation. – Sean Lange Dec 09 '14 at 15:24
  • @SeanLange How is that any different from the recursive query answers? – Mihai Dec 09 '14 at 15:26
  • I actually made a similar comment on the first of those posted. ;) – Sean Lange Dec 09 '14 at 15:27
  • @srutzky, while I agree that keeping code simple is important, I'd argue the CTE solutions lack clarity (it's harder to come behind the guy who wrote the code and understand what's going on at a glance). However, I think we're all in agreement... use a sequence generator. – Ryan Nigro Dec 09 '14 at 15:28
  • Ryan, not that I like the CTE idea, but to play devil's advocate, what about the overall impact difference of it just spitting out numbers while this loop requires not only a temp table but also an insert per each value? – Solomon Rutzky Dec 09 '14 at 15:31
1
create table #GUIDS (tempID uniqueidentifier)

INSERT INTO #GUIDS ([tempID ])VALUES (NEWID())
GO 100

select * from #GUIDS

drop table #GUIDS

this can be used to create 100 records and avoid a for loop.

biruk1230
  • 3,042
  • 4
  • 16
  • 29
user6832820
  • 11
  • 2