Neil Franken (3/1/2010)

---

Hi There

Cool article but here is what I have found with newid().

...

Right lets say the profile(salary) matches 1,5 million rows in the database. SQL server will return all 1,5 million rows then ORDER BY. Now I mentioned that a GUID is very very random. This causes a high cpu load on the server as the poor Server now has to first sort the 1,5 million rows then it can return the top 1000. Think about it. It first has to sort before it can return. I have tested this and it does not matter if I return 1 row or 750 000 out of the 1,5 million rows that matches the query it constantly runs at the same speed. The top can only be applied once the sorting is done. Granted for small tables and non mission critical queries this technique can work well I would not use it on large tables as you will create a bottle neck.

...

create table #lookup_table(row_num int)
declare @ctr int, @samplesize int
set @ctr = 0
set @samplesize = 1000 -- for example, a sample size of 1,000 is needed
while @ctr < @samplesize
BEGIN
  insert into #lookup_table select abs(checksum(newid()))
  set @ctr = @ctr + 1
END
create clustered index idxc on #lookup_table(row_num)

adish (3/1/2010)

---

I've been using GUID as PKs, but this is a novel way of using it. Great.

GabyYYZ (3/1/2010)

---

[quote]One option, especially if you have an indexed identity column on your source table, is to generate a separate table of random row numbers, create a clustered index on it, and join with the original table.

Paul White (3/1/2010)

---

Not a clustered Primary Key, I trust?

Paul White (3/1/2010)

---

Not a clustered Primary Key, I trust?

Jonathan Kehayias (3/1/2010)

---

It probably was clustered, its common for App Developers to do this kind of thing. It happened at Microsoft around the Windows 7 RC downloads...

SQLBOT (3/1/2010)

---

It's the random insertion, not the datatype that causes the problem.
What's the differece if the data inserted is Johnson, Jonsonn, Johnsen or three guids?
Under the hood, there's not a difference.

Paul White (3/1/2010)

---

Ah yes, I remember that one
Highly amusing at the time...

adish (3/1/2010)

---

It is clustered. Am I doing something terribly wrong? Some light on this please, I am not an expert in SQL and would appreciate guidance from SQL gurus.
Thanks