Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest
навеяно вот этой темой: баг или фича?
Гавриленко Сергей Алексеевич
Гадя Петрович
Glory
Бага - это когда _документированный_ способ в каких то случаях работает неправильно.
А работа недокументированного способа не может быть багой потому, что нигде не написано, что это вообще должно работать.

способ мутный, но неоднозначность поведения сервера я лично склонен считать багом
работа с переменными прекрасно документирована, сортировка тоже, ошибок никаких не выдается

BOL
SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

не хочу здесь разводить голосование по поводу, считать ли "неоднозначность поведения сервера" багом
или оспаривать очевидное
Гавриленко Сергей Алексеевич
не вижу ни одной причины писать так вместо того, чтобы использовать агрегатные функции.

но ведь дело все же не в the variable is assigned the last value that is returned, так?
я вообще в своем примере не буду использовать значения, хранящиеся в таблице.
просто предлагаю обсудить то, что всего 1 раз опубликовал MS,
и даже не считают нужным обновлять версии серверов, к к-ым это применимо (а ведь применимо же не только к 2005).
я молчу, что их "объяснение" -- это просто тыканье носом, на что обратить внимание (order by).
т.е. по факту объяснения нет.

репро:
declare @s int = 0;

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

select @s;
---
--1000000

vs
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

картинка:

К сообщению приложен файл. Размер - 74Kb
9 апр 15, 10:45    [17492480]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest
единственное объяснение, что мне удалось отловить,
оказалось от участника нашего же форума, непонятно почему только оно лежит на совсем другом форуме.
захотелось поделиться в явном виде (не просто ссылкой)
и услышать мнение SomewhereSomehow по этому поводу.
ну и, возможно, поставить точку в еще одном "misterious behaivior" :)

оригинал сообщения тут

К сообщению приложен файл. Размер - 38Kb
9 апр 15, 10:49    [17492502]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
мимо
Guest
Да вроде всё понятно, сервер строит некий план выполнения запроса и выполняет его.
В БОле тоже расписано, что так агрегировать нельзя (одно значение присваивается) .
Другое дело: надо ли генерить ошибку, если возвращается более одного значения? Чтоб избежать неоднозначностей.
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;
9 апр 15, 11:28    [17492715]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
0-0
Guest
o-o,

пример у вас искусственный уж очень
Но интересно услышать мнение SomewhereSomehow
9 апр 15, 11:38    [17492781]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest
мимо
В БОле тоже расписано, что так агрегировать нельзя (одно значение присваивается) .

1. выполните мой первый кусок кода (без ) и убедитесь в неверности утверждения "одно значение присваивается"
2. покажите, где в БОЛ пишут "так агрегировать нельзя"

еще раз уточню, если может это не было явно озвучено:
SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.
относится к конструкции
select @s = n from tab -- где n -- имя колонки таблицы tab


а про
select @s = @s +... --  @s = local variable 
вообще ничего не написано, или я плохо ищу?
в связи с чем еще раз процитирую:

Glory
Бага - это когда _документированный_ способ в каких то случаях работает неправильно.
А работа недокументированного способа не может быть багой потому, что нигде не написано, что это вообще должно работать.
9 апр 15, 11:42    [17492815]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest
0-0,
именно что пример ни при чем, а при чем только SORT и SomewhereSomehow по возможности :)
мне никаким боком не сдалось жерезж-ое агрегирование, о чем сказано в первом посте.
я хочу разобраться, сделал ли погоду именно SORT
9 апр 15, 11:45    [17492841]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Ребят, тему читаю, дебаггер подключаю, картинки для поста нарезаю - немного потерпите, ведь я еще и на работе =)
9 апр 15, 11:59    [17492922]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
мимо
Guest
o-o, план надо смотреть. Все ж пишут, скл - декларативный язык. Процесс скрыт - главное результат. Вот если результат не "бьет" - тогда бага.
А есть ордер, нет ордера - какая разница, важно куда он(оптимизатор) его запихнет.
Ещё раз:
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;


Насчет того, что нельзя так агрегировать: программисты - они ж не юристы, у них что задокументировано - то можно, а что нет - то значит нельзя. Т.е. что не запрещено, то разрешено - тут не катит.
9 апр 15, 12:09    [17492997]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest
мимо
o-o, план надо смотреть. Все ж пишут, скл - декларативный язык. Процесс скрыт - главное результат. Вот если результат не "бьет" - тогда бага.
А есть ордер, нет ордера - какая разница, важно куда он(оптимизатор) его запихнет.

вот же блин, глаза у меня есть, план я вижу, а вы???
на картинке что, не видно, что в первом случае СОРТА ПРОСТО НЕТ???
и в ответе, о чудо, то, что и ожидалось.
у меня закомментирован даже order by n, к-ый не приведет к появлению сорта в плане,
(у меня и без того кластерный по n)

ладно, все, я жду ответ SomewhereSomehow
9 апр 15, 12:18    [17493046]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
Заинтересовался
Guest
Пример достаточно искусственный, учитывая странный вид выражения в order by. Но действительно показывает, что подобный метод агрегирования может приводить к ошибкам. Можно ли провести пример (если он есть) с аналогичной ошибкой для конкатенации строк (где это метод реально часто используют)?
9 апр 15, 13:02    [17493316]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
мимо
Guest
o-o
мимо
o-o, план надо смотреть. Все ж пишут, скл - декларативный язык. Процесс скрыт - главное результат. Вот если результат не "бьет" - тогда бага.
А есть ордер, нет ордера - какая разница, важно куда он(оптимизатор) его запихнет.

вот же блин, глаза у меня есть, план я вижу, а вы???
на картинке что, не видно, что в первом случае СОРТА ПРОСТО НЕТ???
и в ответе, о чудо, то, что и ожидалось.
у меня закомментирован даже order by n, к-ый не приведет к появлению сорта в плане,
(у меня и без того кластерный по n)

ладно, все, я жду ответ SomewhereSomehow

Чё кипятиться? Я к тому, что важно, где будет компьютскаляр. Если в конце , т.е. перед селектом, то посчитает сумму(в независимости от наличия или отсутствия ордера, о примере надо подумать). Ежели нет - то как фишка ляжет.
А так да, все ждем SomewhereSomehow.
9 апр 15, 13:44    [17493673]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest
мимо
Я к тому, что важно, где будет компьютскаляр. Если в конце , т.е. перед селектом, то посчитает сумму(в независимости от наличия или отсутствия ордера, о примере надо подумать)

да конечно давайте спокойно.
вы считаете, что важна позиция COMPUTE SCALAR vs SELECT,
я считаю, что важна позиция COMPUTE SCALAR vs SORT.
нету сорта -- нет проблемы
или приведите пример того, с чем еще можно переставить COMPUTE SCALAR,
чтобы получился неверный результат.

у меня не зря пример выбран такой, что его план проще нЕкуда.
ну не с чем переставлять COMPUTE SCALAR в отсутствии SORT.
мне казалось, это очевидно...
9 апр 15, 13:53    [17493728]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
мимо
Guest
o-o,
Про расчет скаляра, несколько не в тему. Со скаляром, малость, не всё понятно
9 апр 15, 14:12    [17493822]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest
мимо,

спсб, но в том плане куча итераторов,
так что действительно, есть возможность вычислить выражение когда угодно, раз свобода выбора предоставлена, начиная с 2005.
на deffered scalar evaluation как раз SomewhereSomehow недавно мне глаза и открыл: 17471054
меня этим уже и не удивишь :)
но в данном-то случае еще раз "внимание на экран" (см. мою картинку):
разница лишь в SORT-е
попробуйте еще чем-то вызвать этот эффект, не добавляя в запрос ничего, что в план сортировку добавит.
у меня не вышло,
а я на слово не верю ни за что и никогда :)
9 апр 15, 14:50    [17494045]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Еще раз:
1. Для каждой строки результирующего набора построчно а) вычисляется @s + 1; б) результат из а присваивается @s. В противном случае никакого агрегирования бы не было.
2. Если между а и б будет любой блокирующий оператор (в данном случае это sort), выражение @s + 1 будет вычислено для всех строк набора с одним и тем же значением @s до какого-либо присваивания в @s.
9 апр 15, 15:06    [17494166]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
Заинтересовался
Guest
invm
Еще раз:
1. Для каждой строки результирующего набора построчно а) вычисляется @s + 1; б) результат из а присваивается @s. В противном случае никакого агрегирования бы не было.
2. Если между а и б будет любой блокирующий оператор (в данном случае это sort), выражение @s + 1 будет вычислено для всех строк набора с одним и тем же значением @s до какого-либо присваивания в @s.


Можно привести пример неправильной работы агрегации через переменную без наличия order by?
9 апр 15, 15:24    [17494287]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Я честно говоря как-то пропустил тему, а про другой форум вообще не знаю, но invm, абсолютно правильно объяснил физику процесса. Кому лень читать дальше – можно остановиться тут.

Но насколько я понял, в ответе на вопрос требуется тяжелая артиллерия, не в виде меня конечно, а в виде дебаггера, чтобы народ знал, что так оно и есть на самом деле (в данной версии сервера – я привожу результаты для 2014 CU1).

Предлагаю, для начала, упростить репро до нескольких строк (это тот случай, где можно это сделать). Кроме того, сделаем репро интересней и загадочней =)

+
/*
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

Query NoORDER BYSort operatorAggregated
1NONOYES
2YESYESNO
3YESNOYES
4YESYESYES
5YESYESNO


Как видно из результатов, комбинации самые разные (ниже будут даны объяснения каждого из примеров).
Давайте сформулируем вопросы.
1. Влияет ли оператор сортировки на результат
2. Если влияет, то как
3. Влияет ли предложение ORDER BY на появления оператора сортировки

1.
Из планов, можно заметить, что само по себе наличие сортировки в плане не обязательно влияет на результат (Запросы 4 и 5), но влияет последовательность операторов. В планах, где агрегация происходит, вычисление скаляра идет строго после сортировки, в планах где нет агрегации, сначала (читаем план справа налево или снизу вверх) идет вычисление скаляра, потом сортировка.

Ответ на этот вопрос – сама по себе сортировка не обязательно приведет к однозначному результату, влияет последовательность действий при выполнении.

Может ли быть так, что вычисление скаляра идет после сортировки, но результат не агрегирован. Иными словами, может ли план номер 4 выдать другой результат. Может из-за того, что для скалярных выражений место их выполнения не определено в плане и в более сложном плане – это возможно.

Craig Freedman
Moreover, you may recall from my last post, that SQL Server defers the execution of most scalar expressions until they are actually evaluated.


Вот еще пример, почему не стоит полагаться на положение Compute Scalar.
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;

В первом случае результат 1, во втором 4. Т.к. во втором случае, сервер решил сначала вычислить выражение n*2+3 (поскольку мы же запросили top с сортировкой, т.е. он заранее должен вычислить выражение, по которому отбирает top), потом отсортировать и потом заняться переменной. Можно еще понапридумывать разных примеров, где результат будет неожиданным.

2.
Как и почему влияет положение сортировки. Как уже было сказано invm, Sort блокирующий итератор, но что это значит. Чтобы это продемонстрировать, давайте возьмем дебаггер.
Поставим точку останова в дебаггере на Clustered Index Scan и конкретно метод, получающий строку (это, в данном случае (есть еще несколько), метод sqlmin!CQScanTableScanNew::GetRow). Выполним два запроса, первый и второй.

Первый
Картинка с другого сайта.
Запрашивающий итератор CQueryScan – спрашивает строку (GetRow), это напрямую транслируется в итератор CQScanTableScanNew, который также запрашивает строку, далее, Storage Engine делает еще много интересных вещей (блокировки, латчи и т.д.), но нас это не интересует.
Мы видим поток:
Картинка с другого сайта.

Второй:
Картинка с другого сайта.
Видите, у сортировки нет пока GetRow, вызов метода будет позже, когда сортировка прочитает все данные из нижестоящего оператора, у которого она сама вызывает GetRow. Это и есть понятие «блокирующий», т.е. если кто-то спрашивает сортировку – «дай строку», то она спрашивает нижестоящие операторы, сортирует, но на время сортировки блокирует того, кто ее вызывал.

Графически представить можно так:
Картинка с другого сайта.

Где же Compute Scalar в дебаггере, можно спросить, и ответ будет как раз – то самое отложенное выполнение. Он может выполнится в любом операторе (который это поддерживает), и это (выполнение) не обязательно будет отражено в плане.

3.

Предложение ORDER BY влияет на требование сортировки. Без него, сортировка не гарантирована. Но что важно в данном случае, что требование сортировки заставляет оптимизатор искать наиболее дешевый способ выполнения сортировки. А нет более дешевого способа, чем просканировать индекс в порядке ключа индекса и получить отсортированный набор. Если мы используем подсказку with(index(0)) – не многие знают – что даже если есть кластерный индекс, форсируется сканирование по цепочкам IAM, а значит после этого требуется сортировка. Но в случае 4, она идет перед вычислением скаляра, т.е. строки сначала читаются из индекса, потом сортируются, а потом идет накопление в переменную.

TF 9259 – почему если включен этот trace flag, то не происходит накопления? Это влияние упрощения запроса. Начиная с 2005 сервера в упрощении запроса появляется такая стадия как нормализация проекций (проекция – проектирование из множества (таблицы/индекса) в результат). Этот флаг отключает фазу нормализации. И скаляр остается там, где он был согласно дереву запроса, не нормализуется и не перемещается, вычисляется перед сортировкой и не является накапливающим.

Резюмируя

1. Влияет ли оператор сортировки на результат

Да, влияет. Но влияет его положение. При этом, может быть так, что даже при условно «правильной» последовательности итераторов – результат будет отличный от ожидаемого из-за отложенного выполнения скалярных операторов.

2. Если влияет, то как

Сортировка заставляет сервер сначала прочитать все строки и отсортировать их, в том числе, выполнив присвоение переменной и только потом начать отдавать строки.

3. Влияет ли предложение ORDER BY на появления оператора сортировки

Влияет, но могут сервер может выбирать как сделать сортировку, сканируя ли индекс, либо предоставляя явный оператор сортировки.

Какие еще могут быть неожиданные случаи без ORDER BY, где нельзя предугадать результат:
- Nested Loop Join – Optimized property – неявная Batch сортировка
- Оконные функции – могут сортировать как удобно без явного order by во внешнем запросе
- Index Spool – сортирует в порядке ключа индекса, абсолютно неважно наличие ORDER BY – является внутренней оптимизацией
- Параллельные планы, множество вариантов, начиная от Repartitioning Hash и заканчивая сортирующим Gather Streams.
- Локальная-глобальная агрегация – то же что и с параллельными планами
- Выбор индекса – определяющий факто стоимость, покрывающий индекс, но требующий сортировки, может быть выгоднее не покрывающего без нее
- Уникальность индекса
- Предложение TOP, EXISTS, FAST N
- Миллион причин о которых я забыл или еще не знаю.

Holly war
Ввязываться в холивар про "документировано" или нет – не хочу. С одной стороны, вроде есть KB который описывает это поведение, с другой, читает ли их юзер, так ли их легко найти?

Сами себе ответьте – пока не столкнетесь с проблемой – будете читать все KB? Конечно кто-то читает (например, я и многие другие тут на форуме, причем читаем по каждому CU или SP), ну а кто не так вовлечен в сиквел? Их это вина, или вина слабой документации. Кто использует поиск по интернет документации в BOL? Я лично – ничего так не нахожу – точнее нахожу массу нерелевантных статей. Для меня лучший способ это гугл, спасибо MS что все индексируется.

Тема для холивара – конечно. Но участвовать и комментировать не хочу.
Просто не пользуйтесь этим способом =)
9 апр 15, 15:41    [17494394]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,

Пятый пример не репрезентативный, в данном репро не влияет, но вообще - влиять может. Надо экспериментировать с вычисляемыми колонками.
9 апр 15, 15:58    [17494501]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
SomewhereSomehow,

шикарно! Почти готовая статья :)
9 апр 15, 15:59    [17494511]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Гадя Петрович,

Да, только куча опечаток =)

Ну и c TF 9259 получилось не совсем то, что хотел сказать. Что-то мне подумалось, что на простом примере можно показать, что может быть так:
Картинка с другого сайта.
Сейчас понимаю, что надо репро посложнее, но не переписывать же весь пост =)
Просто помним, что скаляры могут перемещаться и упрощаться и такое может быть - лишний повод - не пользоваться таким способом.
9 апр 15, 16:10    [17494560]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest
SomewhereSomehow
Да, только куча опечаток =)

лучше по делу, но с опечатками, чем литературный трактат ни о чем, это же очевидно :)
так что не надо переживать по этому поводу.
огромное спасибо за потраченное время.

и invm спасибо, хоть и не нам адресовалось.
если проблема в отдельных хамящих личностях, то я буду на место ставить, не надо нас вашими объяснениями обделять,
наш форум все равно лучше :)
9 апр 15, 16:22    [17494635]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest
Заинтересовался
Пример достаточно искусственный, учитывая странный вид выражения в order by. Но действительно показывает, что подобный метод агрегирования может приводить к ошибкам. Можно ли провести пример (если он есть) с аналогичной ошибкой для конкатенации строк (где это метод реально часто используют)?

пример без искусственного order by
и без хинта, просящего насильно выбрать такой-то индекс:
просто нужно добавить индекс гораздо уже того, что сейчас используется в запросе.
т.е. если у нас кластерный индекс в нужном нам порядке, но таблица широченная,
появление узкого индекса с нужными полями, но с "неподходящей" сортировкой,
приведет к смене плана и неверному результату (в плане появится сорт между select и compute scalar).
пример взят из книги T-SQL Querying
поле filler олицетворяет кучу не интересующих нас полей,
ну и заполняю таблицу для улучшения воспроизводимости не через dbo.GetNums(), а через row_number()
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;


К сообщению приложен файл. Размер - 83Kb
27 ноя 15, 16:47    [18482596]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
o-o
Guest


К сообщению приложен файл. Размер - 125Kb
27 ноя 15, 16:47    [18482597]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
BERSERC
Member

Откуда:
Сообщений: 442
Удалось ли кому-то получить неверные результаты при использовании TOP (SELECT 100) ?
declare @s int;
set @s = 0;
SELECT TOP (SELECT 100) percent @s = @s + 1 from dbo.nums
order by n* 2 + 3;
select @s;


насколько я понимаю такая конструкция гарантированно заставляет сначала отработать сортировку а уже потом скаляр.
18 апр 17, 14:54    [20410974]     Ответить | Цитировать Сообщить модератору
 Re: SORT iterator или еще раз о "сборе в переменную"  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
BERSERC
насколько я понимаю такая конструкция гарантированно заставляет сначала отработать сортировку а уже потом скаляр.
Ничего нельзя гарантировать при использовании недокументированных возможностей.
18 апр 17, 16:30    [20411454]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить