Share via

FileTable Schema path_locator vs stream_id per reference support table

Alen Cappelletti 1,047 Reputation points
2023-04-04T08:37:35.62+00:00

Hi, I'm curious of the PK choice from MS in this type of table system. "stream_id" is [uniqueidentifier] but it's not the primary key... it is "path_locato"r which is a [hierarchyid]. Now inserting a record via t-sql and having the need to create a support table with the reference ID of the record, I would be inclined to use "stream_id", even if it is not the PK. Up until now, I've never had any problems. What do you recommend? Thank you, Alen

SQL Server | Other
0 comments
Accepted answer
  1. Dan Guzman 9,406 Reputation points
    2023-04-04T11:49:54.12+00:00

    The system-defined FileTable schema includes a non-clustered primary key and two non-clustered unique constraiants. Any of these will uniquely identify a file table row:

    • primary key constraint (non-clustered): path_locator (hierarchyid)
    • unique constraint (non-clustered): stream_id (uniqueidentifier)
    • unique constraint (non-clustered): parent_path_locator (hierarchyid), name (nvarchar(255))

    The main advantage of storing stream_id in your support table for the file table row locator (and perhaps foreign key) is that it's narrowest of these candidate keys. I suggest stream_id for most use cases.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.