1

Topic: Aggregation of lines in one

Good afternoon.
Help to find article in which explicitly went wrong why a variant

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)

Can lead to artifacts and incorrect concatenation and it is necessary to replace it on/through XML PATH
In the first I faced incorrect operation of such algorithm, and in the second, somewhere on Internet open spaces, found article detailed where it was told at level of an engine of kernel MSSQL why so happens (because of ORDER BY)
Or if who can is detailed explain by the own words if there is no article.

2

Re: Aggregation of lines in one

Here considered:
SORT iterator or once again about "collection in a variable"
Look also the link in the first post at a subject, on which motives

3

Re: Aggregation of lines in one

X-Cite;

SELECT
@A = @A + CAST (sil. Line AS VARCHAR (MAX)) + ','

it is equivalent

SELECT
@A = Expr

where Expr it is calculated in Compurte Scalar the execution plan.
Accordingly @A = Expr lines in a resultant dial-up will be fulfilled so much time, how many.
Follow an example:

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]. <<<))
|--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]. <<<, [Expr1004] =CONVERT (varchar (10), [tempdb]. [dbo]. [t]. [id], 0) + [tempdb]. [dbo]. [t].))
|--Clustered Index Scan (OBJECT:( [tempdb]. [dbo]. [t]. [PK __ t __ 3213E83F3892A83A]))
*/
select @s;
go
set statistics profile off;
go
drop table dbo.t;
go

In the first case a customer of result Compute Scalar will be select @s =...
Will be fulfilled this Compute scalar there will be 3 times, each time with new value @s. As a result receive the aggregated line.
In  a case a customer of result Compute Scalar will be Sort. Sort - the locking operator, i.e. an output appears only when all input will be consumed.
I.e. Compute Scalar are fulfilled the same 3 times, only with same value @s. As a result receive @s = to the last received value s from the table.

4

Re: Aggregation of lines in one

Yes. Thank you very much.
It will be necessary to add in bookmarks.
There will be a powerful argument for new young employees got used to do as be able...

5

Re: Aggregation of lines in one

X-Cite;
It is treated easily enough:

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

But all the same it not an occasion to use not documentary method in the presence of the documented.

6

Re: Aggregation of lines in one

invm wrote:

X-Cite;
It is treated easily enough:

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

But all the same it not an occasion to use not documentary method in the presence of the documented.

By the way, in TOP () type BIGINT so it is possible to set more:-D

7

Re: Aggregation of lines in one

iap wrote:

By the way, in TOP () type BIGINT so it is possible to set more:-D

Laziness f- to consider:-D

8

Re: Aggregation of lines in one

invm wrote:

it is passed...
Laziness f- to consider:-D

9223372036854775807

9

Re: Aggregation of lines in one

iap wrote:

9223372036854775807

the Capacity of my storage has not enough for storage smile

10

Re: Aggregation of lines in one

invm wrote:

it is passed...
The capacity of my storage has not enough for storage smile

Therefore I each time climb in help about BIGINT! smile))

11

Re: Aggregation of lines in one

By the way, the maximum value INT can be received here so: CHECKSUM (NULL*0)

12

Re: Aggregation of lines in one

iap wrote:

the maximum value INT can be received here so: CHECKSUM (NULL*0)

it is amusing smile

13

Re: Aggregation of lines in one

iap wrote:

By the way, the maximum value INT can be received here so: CHECKSUM (NULL*0)

Select power (2.0, max_length*8-1)
from sys.types
where name = ' int'

14

Re: Aggregation of lines in one

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 it is approximated how to receive real value?
Google, Wikipedia and MSDN not to offer!

15

Re: Aggregation of lines in one

All right, found on 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'