declare @s int = 0;

select @s = @s + 1
from dbo.nums;

select @s;
---
--1000000

declare @s int = 0;

select @s = @s + 1
from dbo.nums
-- order by n; -- no sort at all [PK_nums_n]
order by n * 2 + 3; -- sort!!!

select @s;
---
--1

if OBJECT_ID('tempdb..#t') is not null drop table #t;
select top 100 number
into #t
from spt_values as s
where s.type = 'P';
go

declare @s bigint = 0;
select @s = @s + 1
from #t;

select @s;
go

declare @s bigint = 0;

select @s = @s + 1
from  #t
-- order by n; -- no sort at all [PK_nums_n]
order by number * 2 + 3; -- sort!!!

select @s;
go

declare @s bigint = 0;

select @s = @s + 1
from (
select top 100000 *
from #t
)  z
-- order by n; -- no sort at all [PK_nums_n]
order by number * 2 + 3;


select @s;

go

declare @s bigint = 0;

select @s = @s + 1
from (
select top 100000 *
from #t
)  z
order by number * 2 + 3
option (force order); 

select @s;
go
if OBJECT_ID('tempdb..#t') is not null drop table #t;

select @s = n from tab -- где n -- имя колонки таблицы tab

select @s = @s +... --  @s = local variable 

if OBJECT_ID('tempdb..#t') is not null drop table #t;
select top 100 number
into #t
from spt_values as s
where s.type = 'P';
go

declare @s bigint = 0;

select top 10000 @s = @s + 1
from #t
order by number * 2 + 3
; 

select @s;


if OBJECT_ID('tempdb..#t') is not null drop table #t;

/*
use test; -- <-- Don't like tempdb, because the behavior might be different
go
create table dbo.nums(n int primary key);
insert dbo.nums(n) values (1),(2),(3),(4);
go
*/
set statistics profile on
go
-- 1
declare @s int = 0;
select @s = @s + 1 from dbo.nums;
select @s;
/*
plan:
 select @s = @s + 1 from dbo.nums
  |--Compute Scalar(DEFINE:([Expr1002]=[@s]+(1)))
       |--Clustered Index Scan(OBJECT:([test].[dbo].[nums].[PK__nums__3BD019936C90F14E]))
result: 
	4
*/
go
-- 2
declare @s int = 0;
select @s = @s + 1 from dbo.nums order by n * 2 + 3;
select @s;
/*
plan:
	select @s = @s + 1 from dbo.nums order by n * 2 + 3
	  |--Sort(ORDER BY:([Expr1003] ASC))
		   |--Compute Scalar(DEFINE:([Expr1002]=[@s]+(1), [Expr1003]=[test].[dbo].[nums].[n]*(2)+(3)))
				|--Clustered Index Scan(OBJECT:([test].[dbo].[nums].[PK__nums__3BD019936C90F14E]))
result: 
	1
*/
go
-- 3
declare @s int = 0;
select @s = @s + 1 from dbo.nums order by n;
select @s;
/*
plan:
	select @s = @s + 1 from dbo.nums order by n
	  |--Compute Scalar(DEFINE:([Expr1002]=[@s]+(1)))
		   |--Clustered Index Scan(OBJECT:([test].[dbo].[nums].[PK__nums__3BD019936C90F14E]), ORDERED FORWARD)
result:
	4
*/
go
-- 4
declare @s int = 0;
select @s = @s + 1 from dbo.nums with(index(0)) order by n;
select @s;
/*
plan:
	select @s = @s + 1 from dbo.nums with(index(0)) order by n
	  |--Compute Scalar(DEFINE:([Expr1002]=[@s]+(1)))
		   |--Sort(ORDER BY:([test].[dbo].[nums].[n] ASC))                                        
				|--Clustered Index Scan(OBJECT:([test].[dbo].[nums].[PK__nums__3BD019936C90F14E]))
result:
	4
*/
go
-- 5
declare @s int = 0;
select @s = @s + 1 from dbo.nums order by n * 2 + 3 option(querytraceon 9259);
select @s;
/*
plan:
	select @s = @s + 1 from dbo.nums order by n * 2 + 3 option(querytraceon 9259)
	  |--Sort(ORDER BY:([Expr1003] ASC))
		   |--Compute Scalar(DEFINE:([Expr1002]=[@s]+(1), [Expr1003]=[test].[dbo].[nums].[n]*(2)+(3)))
				|--Clustered Index Scan(OBJECT:([test].[dbo].[nums].[PK__nums__3BD019936C90F14E]))
result:
	1
*/
go
set statistics profile off

declare @s int;
set @s = 0;
select @s = @s + 1 from dbo.nums order by n * 2 + 3;
select @s;
set @s = 0;
select top(10) @s = @s + 1 from dbo.nums order by n * 2 + 3;
select @s;

USE tempdb;
IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
col1 INT NOT NULL IDENTITY,
col2 VARCHAR(100) NOT NULL,
filler BINARY(2000) NULL DEFAULT(0x),
CONSTRAINT PK_T1 PRIMARY KEY(col1)
);
INSERT INTO dbo.T1(col2)
SELECT top 100 'String ' + CAST(row_number()over(order by @@version) AS VARCHAR(10))
FROM sys.all_columns AS Nums;

DECLARE @s AS VARCHAR(MAX);
SET @s = '';
SELECT @s = @s + col2 + ';'
FROM dbo.T1
ORDER BY col1;
PRINT @s;

CREATE NONCLUSTERED INDEX idx_nc_col2_i_col1 ON dbo.T1(col2, col1);
GO
DECLARE @s AS VARCHAR(MAX);
SET @s = '';
SELECT @s = @s + col2 + ';'
FROM dbo.T1
ORDER BY col1;
PRINT @s;

declare @s int;
set @s = 0;
SELECT TOP (SELECT 100) percent @s = @s + 1 from dbo.nums
order by n* 2 + 3;
select @s;