I am using MSSQL 2012 and its feature called File Table to store some big amount of files stored in hierarchical directories. I am referencing the entries on the file from other custom tables via the column stream_id, which is unique for every record on the file table. Sometimes I need to move the files on the file table to some other location on the same file table. So far I have noticed that the stream_id does not change if I move the file to another directory. However, now in the production environment the stream_id does change after the move, so my custom table is referencing a not existing entry on the file table.
For moving the Files I am using File.Move(source, target);
Is there something wrong with the deployment of the file table in my production environment or is it just a feature that the stream_id can sometime change if I change the location?
I haven't found any reference in the internet regarding the stream_id and its lifetime.
The stream_id is the pointer to the blob, the path_locator is the PK of the table. The former refers to the file no matter where it is in the system, the latter refers to whatever file is currently occupying the space at that path location.
My understanding is that the stream_id will not change for the life of that file. If you are seeing the "same" file with a different stream_id, consider whether an application (like MS Word), created a temp file then renamed the temp file to the current file when you saved. This would result in the new file having the same path_locator but a different stream_id.