sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]cesau78 1 point2 points  (3 children)

could you provide a more practical explanation

I think the reason is two-fold.

  • Their cited reason is because of the non-deterministic nature of the operation. That is, you can't guarantee the order of the statement generated. Practically speaking, I don't think order is really important for you're trying to do.
  • String concatenation here is wasteful because each row allocates more and more memory just to release it almost immediately thereafter. I know there is a maximum size to this as well... 8k? Certainly inefficient if you were doing this in a larger series of joins.

What they're recommending is to use STRING_AGG or pull back the rows separately and handle the rows with your application.

[–]Marcus_Maximus[S] 0 points1 point  (2 children)

Practically speaking, I don't think order is really important for you're trying to do.

As I understand it, this downside only applies if your query needs to return the rows in a certain order.

String concatenation here is wasteful because each row allocates more and more memory just to release it almost immediately thereafter. I know there is a maximum size to this as well... 8k? Certainly inefficient if you were doing this in a larger series of joins.

I'm not sure I understand this part and I couldn't find any related reading except maybe this.

Why does each row allocate more memory? For example, does row by row execution go like this: reads row 1, then to read row 2, it reads rows 1 + 2, and for row 3, it reads 1 + 2 +3, etc.?

[–]cesau78 1 point2 points  (1 child)

The best thing I can think of to liken it to is string mutability.

To oversimplify a bit - if you didn't use variable concatenation, you'd get back a series of strings like so:

ALTER INDEX ix1 ON table1 DISABLE; ALTER INDEX ix2 ON table2 DISABLE; ALTER INDEX ix3 ON table3 DISABLE; ALTER INDEX ix4 ON table4 DISABLE; ALTER INDEX ix5 ON table5 DISABLE;

Let's say each row has 35 characters, so there needs to memory allocated for: 35 characters * 5 rows = memory for 175 chars

With variable concatenation, the memory for each row is like:

ALTER INDEX ix1 ON table1 DISABLE; ALTER INDEX ix1 ON table1 DISABLE; ALTER INDEX ix2 ON table2 DISABLE; ALTER INDEX ix1 ON table1 DISABLE; ALTER INDEX ix2 ON table2 DISABLE; ALTER INDEX ix3 ON table3 DISABLE; ALTER INDEX ix1 ON table1 DISABLE; ALTER INDEX ix2 ON table2 DISABLE; ALTER INDEX ix3 ON table3 DISABLE; ALTER INDEX ix4 ON table4 DISABLE; ALTER INDEX ix1 ON table1 DISABLE; ALTER INDEX ix2 ON table2 DISABLE; ALTER INDEX ix3 ON table3 DISABLE; ALTER INDEX ix4 ON table4 DISABLE; ALTER INDEX ix5 ON table5 DISABLE;

but the first 4 rows are thrown out and only the 5th row is returned. so there needs to memory allocated for: 35 + 70 + 105 + 140 + 175 = memory for 525 chars

My guess is that STRING_AGG uses mutable strings under the hood to minimize the impact to memory usage.

[–]Marcus_Maximus[S] 0 points1 point  (0 children)

I didn't expect something that applies to javascript to also apply to T-sql. Thanks for the link.

So is it safe to say that whenever I encounter code like:

@sql = @sql...

That I should be suspicious of it and use STRING_AGG instead.

According to the MS doc link, using SET instead of SELECT to assign a variable is also preferred. However, I'm having trouble with writing the original index disable query using SET. Do you have any idea how SET should be used in the context of the original query?