Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Агрегация строк в одну  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1663
Добрый день.
Помогите найти статью, в которой подробно разлаживалось почему вариант
DECLARE
 @A VARCHAR(MAX) = ''
  
SELECT
  @A = @A + CAST(sil.Line AS VARCHAR(MAX)) + ', '
FROM
  TableA AS sil
ORDER BY
  sil.Line DESC
  
SELECT LEFT(@A, LEN(@A) - 1)

может привести к артефактам и некорректной конкатенации и необходимо заменить это на/через XML PATH

Во первых я сам столкнулся с некорректной работой такого алгоритма, а во вторых, где-то на просторах интернета, нашел статью подробную, где рассказывалось на уровне движка ядра MSSQL почему так происходит (из-за ORDER BY)

Или если кто может подробно объяснить своими словами, если нет статьи.
5 фев 18, 13:43    [21166725]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
вот тут обсуждали:
SORT iterator или еще раз о "сборе в переменную"

смотрите еще и ссылку в первом посте на тему, по мотивам которой
5 фев 18, 13:49    [21166756]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
X-Cite,

SELECT
  @A = @A + CAST(sil.Line AS VARCHAR(MAX)) + ', '
эквивалентно
SELECT
  @A = Expr
где Expr вычисляется в Compurte Scalar плана выполнения.
Соответственно @A = Expr будет выполнено столько раз, сколько строк в результирующем наборе.

Возьмите пример:
use tempdb;
go

create table dbo.t (id int primary key, s varchar(100));
insert into dbo.t
values
 (1, 'a'), (2, 'b'), (3, 'c');
go

set statistics profile on;
go

declare @s varchar(max) = '';

select @s = @s + s from dbo.t;
/*
select @s = @s + s from dbo.t;
  |--Compute Scalar(DEFINE:([Expr1003]=[@s]+[tempdb].[dbo].[t].[s]))
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F3892A83A]))
*/
select @s;

select @s = '';
select @s = @s + s from dbo.t order by cast(id as varchar(10)) + s;
/*
select @s = @s + s from dbo.t order by cast(id as varchar(10)) + s;
  |--Sort(ORDER BY:([Expr1004] ASC))
       |--Compute Scalar(DEFINE:([Expr1003]=[@s]+[tempdb].[dbo].[t].[s], [Expr1004]=CONVERT(varchar(10),[tempdb].[dbo].[t].[id],0)+[tempdb].[dbo].[t].[s]))
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F3892A83A]))
*/
select @s;
go

set statistics profile off;
go

drop table dbo.t;
go

В первом случае потребителем результата Compute Scalar будет select @s = ...
Выполнятся этот Compute scalar будет 3 раза, каждый раз с новым значением @s. В результате получите агрегированную строку.

Во вотором случае потребителем результата Compute Scalar будет Sort. Sort - блокирующий оператор, т.е. выход появится только тогда, когда будет потреблен весь вход.
Т.е. Compute Scalar выполняетеся те же 3 раза, только с одним и тем же значением @s. В результате получите @s = последнему полученному значению s из таблицы.
5 фев 18, 14:13    [21166853]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1663
Да. Большое спасибо.
Надо будет в закладки добавить.
Будет весомый аргумент для новых молодых сотрудников привыкших делать как умею...
5 фев 18, 14:30    [21166927]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
X-Cite,

Лечится довольно легко:
SELECT
  @A = @A + sil.Line + ', '
FROM
  (select top (cast(0x7fffffff as int)) CAST(sil.Line AS VARCHAR(MAX)) TableA order by Line desc) AS sil
Но все равно это не повод пользоваться недокументированным способом при наличии документированного.
5 фев 18, 15:31    [21167187]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
iap
Member

Откуда: Москва
Сообщений: 46951
invm
X-Cite,

Лечится довольно легко:
SELECT
  @A = @A + sil.Line + ', '
FROM
  (select top (cast(0x7fffffff as int)) CAST(sil.Line AS VARCHAR(MAX)) TableA order by Line desc) AS sil

Но все равно это не повод пользоваться недокументированным способом при наличии документированного.
Кстати, в TOP() тип BIGINT, так что можно задать побольше
5 фев 18, 15:33    [21167194]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
iap
Кстати, в TOP() тип BIGINT, так что можно задать побольше
Лень f-ы считать
5 фев 18, 15:46    [21167248]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
iap
Member

Откуда: Москва
Сообщений: 46951
invm
iap
Кстати, в TOP() тип BIGINT, так что можно задать побольше
Лень f-ы считать
9223372036854775807
5 фев 18, 15:50    [21167258]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
iap
9223372036854775807
Емкости моей памяти недостаточно для запоминания :)
5 фев 18, 16:00    [21167308]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
iap
Member

Откуда: Москва
Сообщений: 46951
invm
iap
9223372036854775807
Емкости моей памяти недостаточно для запоминания :)
Поэтому я каждый раз лезу в справку про BIGINT!
5 фев 18, 16:02    [21167316]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
iap
Member

Откуда: Москва
Сообщений: 46951
Кстати, максимальное значение INT можно получить вот так: CHECKSUM(NULL*0)
5 фев 18, 16:15    [21167381]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
iap
максимальное значение INT можно получить вот так: CHECKSUM(NULL*0)
Забавно :)
5 фев 18, 16:29    [21167430]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
VGalamakh
Member

Откуда: Киев (Альба)
Сообщений: 66
iap
Кстати, максимальное значение INT можно получить вот так: CHECKSUM(NULL*0)


Select power(2.0, max_length*8-1) 
from sys.types
where name = 'int'
5 фев 18, 17:04    [21167546]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
VGalamakh, iap, invm
DECLARE @base DECIMAL( 30, 0 ) = 2
SELECT
  [name], 
  [min] = CASE WHEN t.[name] = 'TINYINT' THEN 0 ELSE -ss.[limit] END,
  [max] = CASE WHEN t.[name] = 'TINYINT' THEN 2 * ss.[limit] - 1 ELSE ss.[limit] - 1 END
FROM 
  sys.types t
  CROSS APPLY (
    SELECT
      [limit] = CONVERT( BIGINT, POWER( @base, t.[max_length] * 8 - 1 ) )
  ) ss
WHERE
  t.[name] LIKE '%INT'

BIGINT округляется, как получить реальное значение?
Google, Wikipedia и MSDN не предлагать!
5 фев 18, 18:00    [21167759]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк в одну  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Ладно, нашел на SO

DECLARE @base BIGINT = 2
SELECT
  [name],
  [min] = CASE WHEN t.[name] = 'TINYINT' THEN 0 ELSE -ss.[limit] - 1 END,
  [max] = CASE WHEN t.[name] = 'TINYINT' THEN 2 * ss.[limit] + 1 ELSE ss.[limit] END
FROM 
  sys.types t
  CROSS APPLY (
    SELECT
      [limit] = POWER( @base, t.[max_length] * 8 - 2 ) + ( POWER( @base, t.[max_length] * 8 - 2 ) - 1 )
  ) ss
WHERE
  t.[name] LIKE '%INT'
5 фев 18, 18:18    [21167814]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить