Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To know for sure about performance you need to test. I have done some testing using your version (slightly modified) and a recursive CTE versions suggested by others.</p> <p>I used your sample table with 2048 rows all in one single folder hierarchy so when passing 2048 as parameter to the function there are 2048 concatenations done.</p> <p><strong>The loop version:</strong></p> <pre><code>create function GetEntireLineage1 (@id int) returns varchar(max) as begin declare @ret varchar(max) select @ret = folder_name, @id = parent_id from Folder where id = @id while @@rowcount &gt; 0 begin select @ret = @ret + '-' + folder_name, @id = parent_id from Folder where id = @id end return @ret end </code></pre> <p>Statistics:</p> <pre><code> SQL Server Execution Times: CPU time = 125 ms, elapsed time = 122 ms. </code></pre> <p><strong>The recursive CTE version:</strong></p> <pre><code>create function GetEntireLineage2(@id int) returns varchar(max) begin declare @ret varchar(max); with cte(id, name) as ( select f.parent_id, cast(f.folder_name as varchar(max)) from Folder as f where f.id = @id union all select f.parent_id, c.name + '-' + f.folder_name from Folder as f inner join cte as c on f.id = c.id ) select @ret = name from cte where id is null option (maxrecursion 0) return @ret end </code></pre> <p>Statistics:</p> <pre><code> SQL Server Execution Times: CPU time = 187 ms, elapsed time = 183 ms. </code></pre> <p>So between these two it is the loop version that is more efficient, at least on my test data. You need to test on your actual data to be sure.</p> <p><strong>Edit</strong></p> <p><strong>Recursive CTE with <code>for xml path('')</code> trick.</strong></p> <pre><code>create function [dbo].[GetEntireLineage4](@id int) returns varchar(max) begin declare @ret varchar(max) = ''; with cte(id, lvl, name) as ( select f.parent_id, 1, f.folder_name from Folder as f where f.id = @id union all select f.parent_id, lvl + 1, f.folder_name from Folder as f inner join cte as c on f.id = c.id ) select @ret = (select '-'+name from cte order by lvl for xml path(''), type).value('.', 'varchar(max)') option (maxrecursion 0) return stuff(@ret, 1, 1, '') end </code></pre> <p>Statistics:</p> <pre><code> SQL Server Execution Times: CPU time = 31 ms, elapsed time = 37 ms. </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POWhat is the most efficient way to concatenate a string from all parent rows using T-SQL?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMikael Eriksson
    1. USMikael Eriksson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhat is the most efficient way to concatenate a string from all parent rows using T-SQL?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWow, thanks so much for the effort you've put into this, I truly appreciate it. This place is awesome.
      singulars
      1. PO
      1. USw.brian
    2. COSo, I implemented this in my actual database and it seems that the string concatenation query (select @ret = @ret + '-' + name) isn't concatenating the name from all rows. I queried all rows from the cte and all the rows were there as expected, just not concatenated as expected by the concatenation query.
      singulars
      1. PO
      1. USw.brian
    3. COWell, I've narrowed it down and I'm a bit confused. I do "select * from cte order by lvl;" and it returns all expected rows ordered appropriately by level. When I do the concatenation query "select @ret = @ret + '-' + name from cte order by lvl" it does not concatenate all rows, just the last row. When I leave out the order by clause, it concatenates all the rows appropriately, I just can't be guaranteed they're in the correct order.
      singulars
      1. PO
      1. USw.brian
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload