This bug was first described by Thomas Glörfeld here.
Related Microsoft Connect item can be found here.
The bug has to do with invoking the NEWID function in a query defining a table expression (derived table, CTE, view, inline table-valued function), and then joining the table expression with another table. If the relationship between the table expression and the other table is 1:M, each row from the table expression can appear multiple times in the result of the join. The bug is that depending on the optimizer’s choices in terms of physical processing, SQL Server may end up evaluating the NEWID function once per each target row instead of once per each source row.
This bug was tested on SQL Server versions 2000/SP4, 2005/SP2 and 2008/RC0.
As an example, the following code creates the tables t1 and t2 that are related in a 1:M relationship, and a view that invokes the NEWID function per each row from t1:
set nocount on;
use tempdb;
go
if object_id('dbo.v1', 'V') is not null drop view dbo.v1;
if object_id('dbo.t2', 'U') is not null drop table dbo.t2;
if object_id('dbo.t1', 'U') is not null drop table dbo.t1;
go
create table dbo.t1(id int not null primary key);
insert into dbo.t1(id) values(1);
insert into dbo.t1(id) values(2);
create table dbo.t2(id int not null references dbo.t1);
insert into dbo.t2(id) values(1);
insert into dbo.t2(id) values(1);
insert into dbo.t2(id) values(2);
insert into dbo.t2(id) values(2);
go
create view dbo.v1
as
select id, newid() as newid_val
from dbo.t1
go
Consider the following query:
select a.id as a_id, b.id as b_id, a.newid_val
from dbo.v1 as a
join dbo.t2 as b