Первичный ключ – GUID или автоинкремент?

IF EXISTS( SELECT * FROM sysobjects WHERE name='ParentTable' AND (xtype='U') )
DROP TABLE dbo.ParentTable
GO
IF EXISTS( SELECT * FROM sysobjects WHERE name='TestTable' AND (xtype='U') )
DROP TABLE dbo.TestTable
GO

create table TestTable ( id int identity(1,1) not null primary key clustered, sequence int not null );
go
create table ParentTable (
id int identity(1,-1) not null primary key clustered,
ParentTableId int not null INDEX idx_ParentTableId
);
go
alter TABLE dbo.ParentTable with nocheck add CONSTRAINT FK_ParentTable FOREIGN KEY (ParentTableId) REFERENCES dbo.TestTable(id);
GO

--— NEWID
create table TestTable ( id uniqueidentifier default newid() not null primary key clustered, sequence int not null );
create table ParentTable (
id uniqueidentifier default newid() not null primary key clustered,
ParentTableId uniqueidentifier not null,
);
go
alter TABLE dbo.ParentTable with nocheck add CONSTRAINT FK_ParentTable FOREIGN KEY (ParentTableId) REFERENCES dbo.TestTable(id);
GO

--— NEWSEQUENTIALID
create table TestTable ( id uniqueidentifier default newsequentialid() not null primary key clustered, sequence int not null );
go
create table ParentTable (
id uniqueidentifier default newsequentialid() not null primary key clustered,
ParentTableId uniqueidentifier not null
);
go
alter TABLE dbo.ParentTable with nocheck add CONSTRAINT FK_ParentTable FOREIGN KEY (ParentTableId) REFERENCES dbo.TestTable(id);
GO

— тестируем вставкой 2,000,000 записей

declare count int = 0;
while count < 2000000 begin
INSERT INTO TestTable (sequence) VALUES ( count);
set count = count + 1;
end;
INSERT INTO ParentTable (ParentTableId) SELECT id FROM TestTable
go

select max(sequence) from ParentTable p join TestTable t on p.ParentTableId = t.id
where exists (select * from TestTable s where p.ParentTableId = s.id)

--смотрим что получилось:

--— Get the number of read / writes for this session…
select reads, writes
from sys.dm_exec_sessions
where session_id = @@spid;

--— Get the page fragmentation and density at the leaf level.
select index_type_desc, index_depth, page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent, record_count
from sys.dm_db_index_physical_stats(db_id(), object_id('TestTable'), null, null, 'detailed')
where index_level = 0;
go