Накопление в переменную SQL Server

#mysql #sql #sql_server

В MySQL есть такая интересная штука, как накопление в переменную. Это когда мы прямо
в запросе можем декларировать, инициировать, менять значение и использовать переменную.

SELECT @I := @I + Value
FROM Table ORDER BY OrderValue


При этом порядок изменений значений переменной соответствует предложению ORDER BY,
если оно есть. Это иногда очень красиво заменяет отсутствующую в MySQL рекурсию.

В SQL Server такая возможность частично тоже есть. Т.е. мы можем использовать накопление
в переменную:

SELECT @I += Value
FROM Table


Однако, в отличии от MySQL, такие запросы не возвращают датасэт, и использовать эти
переменные внутри запроса нельзя(в добавок мы должны декларировать переменные заранее,
но это не суть). Т.е. либо инициализируем переменную, либо используем.

Вопрос. Влияет ли сортировка на порядок обновления переменной в SQL Server?

--Имеет ли смысл ORDER BY?
SELECT @I += Value
FROM Table
ORDER BY OrderValue


Если не имеет, то гарантирует ли уникальный кластерный ключ таблицы Table порядок
обновлений?

Если тоже нет. То зачем SQL Server оставил возможность накопления в переменную?

Аккумулирую вопросы:

1) влияет ли сортировка на порядок обновления переменной в SQL Server?

2) (если ответ на 1 нет)гарантирует ли уникальный кластерный ключ таблицы Table порядок
обновлений?

3) (если ответ на 2 нет)зачем SQL Server оставил возможность накопления в переменную?
    

Ответы

Ответ 1

В Sql Server, по-хорошему, если в select происходит присвоение значений переменным,
то это должно быть присвоение им единственного значения; переменные не должны использоваться
для накопления сумм, конкатенации, и вообще какого-либо агрегирования набора строк,
поскольку такой способ не является надёжным, не гарантирует стабильного поведения и,
как следствие, результата.

Примеры нестабильного поведения (проверено на SqlServer версиях 10.50 и 12.0)

Вычисление суммы:

declare @sum int = 0

select @sum = @sum + x
from (values (1), (10), (100), (1000)) data(x)
where x between 1 and 100
order by x
--order by 1 - x desc

select @sum


В зависимости от выбранной сортировки (order by x либо order by 1 - x desc), мы будем
получать разный результат: 111 либо 100.

Строковая конкатенация:

declare @str varchar(100) = ''

select @str = @str + s
from (values ('a'), ('b'), ('c'), ('d')) strdata(s)
where s between 'a' and 'c'
order by s
--order by nullif(s, '')

select @str


Аналогично, в зависимости от выбранной сортировки (order by s либо order by nullif(s,
'')), результат получается разный: abc либо c.

Переменные в select являются частным случаем скалярных выражений, которые (по внутренним
соображениям query processor-а) могут быть вычислены для каждой строки, а могут - один
раз для всего запроса.

Таким образом:


  влияет ли сортировка на порядок обновления переменной в SQL Server?


Да, и, как видим - подчас неожиданно.


  гарантирует ли уникальный кластерный ключ таблицы Table порядок обновлений?


Я бы не стал на это рассчитывать, тем более, что сам подход агрегирования посредством
переменной не отличается стабильностью.


  зачем SQL Server оставил возможность накопления в переменную?


Думаю, это просто частный случай более общего синтаксиса select @a = 
..., где  может быть <@var> + , а <@var> может быть любой переменной,
которая определена ранее, в том числе и @a.


Ответ 2

SQL Server 2014:

create table test(A varchar(10))
insert into test values('a'),('b'),('c'),('d')

declare @I varchar(100) = ''
select @I+=A from test;
select @I

-- Результат: abcd

 declare @I varchar(100) = ''
 select @I+=A from test order by A desc;
 select @I

-- Результат: dcba


Так что, как минимум в 2014 сортировка влияет на накопление значения переменной.

Fiddle MS SQL 2008