• Thank you for the suggestion. I will remove those two local variables. I did receive an answer from another source. According to my crude logging, my times dropped from 2.5 seconds per record to .005 seconds per record.

    -Vernon

    The FileTable path_locator is the primary key so the needed index exists to retrieve the singe row row efficiently. However, using the ToString method makes the expression non-sargable, requiring a scan instead of index seek.

    Below is an example that uses the HierarchyId Parse method on the parameter value rather than invoking the ToString method on the path_locator column. This will allow the primary key index to be used efficiently.

    ALTER PROCEDURE [dbo].[GetFileTablePath]

    (

    @FileTableId nvarchar(MAX)

    )

    AS

    BEGIN

    DECLARE @root nvarchar(100);

    DECLARE @fullpath nvarchar(1000);

    SELECT @root = FileTableRootPath();

    SELECT @root + file_stream.GetFileNamespacePath() AS fullpath

    FROM DocumentStore

    WHERE path_locator = HierarchyId::Parse(@FileTableId);

    END;

    GO