question

To see the performance hit you need to test your query against another solution. Create two procedures, one with this code and one with another solution and take a look at different execution plans and execution statistics. Have you figured out the other solution yet? Keep in mind that SQL server doesn't use statistics for table variables and the engine assumes only one record in table variables. That can cause inefficient execution plans if you have many records in the table variable.

The only way to know what the performance will be is to measure the performance. One way to do this, assuming we're working against a dev or test environment, don't do this in production, is to create a query window, put the two queries you're interested in, in the same window. On the standard tool bar find the little icon that shows "Client Statistics" in the hint, or click on the Query menu and select "Include Client Statistics" or hit shift-alt-s. This will turn on client statistics. Highlight the first query, run it, and you'll get some performance metrics. Highlight the second query, run that, you'll get the second queries performance metrics, side-by-side with the original, showing which values have gone up & down. For completeness, to take into account any caching that's occurred, do it one more time for each query. Now you'll have four sets of metrics that you can compare against each other. Is this the best way to do this? Not necesarily, but it is one way. There other ways with different strengths & weaknesses, set up time, etc.

The biggest problem with measuring performance of any SQL is that performance is meaningless outside the context of the data. With a SQL Query, if you are doing it right, you're actually just asking SQL Server to do something but you're not telling it HOW to do it, and the query optimizer is likely to choose different strategies according to the amount and distribution of the data. This means that a solution that works well with one set of data can be complete pants with a larger, or differently-distributed, set. When you're judging performance, to be completely confident you really need to do it on a range of trial data of various sizes and distributions. Very few people actually do that, but it is surprising how badly some SQL 'scales' to large sets of data.

CREATE TABLE Directory ( DirPath hierarchyid PRIMARY KEY, DirId int, ParentId int ) CREATE TABLE Files ( FileId int, DirId int ) -- just insert dummy values for hierarchyid, they are updated later INSERT Directory VALUES('/1/', 1, NULL), ('/2/', 2, 1), ('/3/', 3, 1), ('/4/', 4, 2) INSERT Files VALUES(1, 1), (2, 1), (3, 2), (4, 2), (5, 3), (6, 3), (7, 4), (8, 4) -- update table with hierarchy ;WITH hierarchy AS ( SELECT '/' + CAST(DirId AS varchar(MAX)) [Path], DirId FROM Directory d WHERE d.ParentId IS NULL UNION ALL SELECT CAST(hierarchy.[path] + '/' + CAST(d.DirId AS varchar(MAX)) AS varchar(MAX)), d.DirId FROM Directory d JOIN hierarchy ON (d.parentID = hierarchy.DirId) ) UPDATE d SET [DirPath] = h.[Path] + '/' FROM hierarchy h JOIN Directory d ON (h.DIRID = d.DIRID) -- once the hierarchy is up to date you can run queries against it, -- this should beat most recursive queries DECLARE @root hierarchyid; SELECT @root = [DirPath] FROM Directory WHERE DirId = 2 -- set your top level directory SELECT d.DirId, f.FileId FROM Directory d JOIN Files f ON (f.DirId = d.DirId) WHERE [DirPath].IsDescendantOf(@root) = 1 ORDER BY d.DirId, f.FileId DROP TABLE Directory, Files