use master;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create database TestSortingOnInsert;
alter database TestSortingOnInsert set recovery full;
backup database TestSortingOnInsert to disk = 'nul:';
go

use TestSortingOnInsert;
go

create table dbo.t (id uniqueidentifier primary key clustered DEFAULT NEWSEQUENTIALID ( ), filler char(500) null);
create table dbo.s (id uniqueidentifier DEFAULT NEWSEQUENTIALID ( ), filler char(500) null);

declare @c int = 100000;

insert into dbo.t
 (filler)
 select top (@c)
  'a'
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.s
 (filler)
 select top (@c)
   'b'
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go

begin tran;
set statistics xml, time on;
insert into dbo.t (id, filler) select id, filler from dbo.s option (/*Запрещаем требовать сортировку строк источника*/querytraceon 8795);
set statistics xml, time off;

/*Сколько было сожрано места в журнале транзакций*/
select
 'Без сортировки', db_name(dbt.database_id) as [БД], dbt.database_transaction_log_bytes_used + dbt.database_transaction_log_bytes_used_system as [Сожрано в ЖТ]
from
 sys.dm_tran_current_transaction ct join
 sys.dm_tran_database_transactions dbt on dbt.transaction_id = ct.transaction_id;

rollback;
go

begin tran;
set statistics xml, time on;
insert into dbo.t (id, filler) select id, filler from dbo.s order by id;
set statistics xml, time off;

/*Сколько было сожрано места в журнале транзакций*/
select
 'С сортировкой', db_name(dbt.database_id) as [БД], dbt.database_transaction_log_bytes_used + dbt.database_transaction_log_bytes_used_system as [Сожрано в ЖТ]
from
 sys.dm_tran_current_transaction ct join
 sys.dm_tran_database_transactions dbt on dbt.transaction_id = ct.transaction_id;

rollback;
go

use master;
alter database TestSortingOnInsert set single_user with rollback immediate;
drop database TestSortingOnInsert;
go