1

I use filetable. When I delete files from SQL Server's FileTable, I wantthe files to be deleted from the folder, and when I delete file from folder, it should be deleted from the filetable.

And I have a second question: is filetable the faster way to save file in server and reading it (files larger than 1MB)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2541236
  • 11
  • 1
  • 5

3 Answers3

3

For the first question, the files should be deleted as soon as you delete the relative row (DELETE FROM ...) and commit. The same should apply at reverse (if you delete a file the relative row should disappear).

This is true for the file exposed through the network share, the physical file will be removed at later time, depending on the recovery model and the filestream's garbage collection process (see sp_filestream_force_garbage_collection stored procedure).

For the second question, the access will be always slower than a pure filesystem one because of the SQL Server overhead (the find time will be orders of magnitude faster though).

Compared to a T-SQL access, though, it all depends on the size of the blob you are storing. In a nutshell, if your blobs are smaller than 1 MB using T-SQL should be faster. Please refer here: Best Practices on FILESTREAM implementations for more detailed figures.

Francesco Cogno
  • 116
  • 3
  • Do you have an official reference for the following statement: *"The same should apply at reverse (if you delete a file the relative row should disappear). "*. I'm reading [here](https://msdn.microsoft.com/en-us/library/cc645962.aspx#Anchor_2): *"The only way to delete a row, and therefore the file, is to use the Transact-SQL DELETE statement."*. – TT. Nov 26 '16 at 12:45
  • 1
    Hi TT, the link you posted is about FileStream. The question was about FileTable. While one uses the other (FileTable uses FileStream) they are different technologies. While it's true you have to use T-SQL to remove a filestream row, you can delete a filetable-backed file with a regular Win32 command. This is, in FileTable, the filter driver issues the delete statement for you automatically (see [https://www.microsoftpressstore.com/articles/article.aspx?p=2225060&seqNum=2](https://www.microsoftpressstore.com/articles/article.aspx?p=2225060&seqNum=2)). Sorry for the confusion :). – Francesco Cogno Nov 26 '16 at 14:40
  • No probs mate, I indeed got confused, not your fault. Thanks for the input! – TT. Nov 26 '16 at 15:16
  • How Can I Reduce the time taken for Deleting phisical file? – user2541236 Nov 28 '16 at 08:49
0
DROP TABLE [ IF EXISTS ] [ database_name . [ schema_name ] . | schema_name . ]  
table_name [ ,...n ]  
[ ; ]

You can Use this code to drop the file table.if you want to delete only certain specific data use where or having clauses in the Tsql statement

Eben Varghese Paul
  • 21
  • 6
  • my question is not that what is the quary for deleting a row from filetable.my question is how can i delete the row and the physical file from specified folder at the same time – user2541236 Nov 26 '16 at 10:32
0

first thing that you must remember File Table in SQL Server is best way that you can use it because of this is manager engine for helping to developer that they want to have pattern for managing files. this manger use physical place for saving files and create table for keeping basic information of file data.when you are deleted file,File Table manager have job that run some times and it is deleted physical file.

if you want to delete physical file immediately you can use this clause:

 checkpoint;
 EXEC sp_filestream_force_garbage_collection @dbname = N'[DB Name]';

you must remember use this clause after delete row from file table with delay or use it in SQL Trigger (after delete) :

Create TRIGGER [Schema].[TriggerName]
   ON  [Schema].[TableName]
   after  DELETE

AS 

  declare @T  table ([file_name]  varchar(50),num_col int, num_marked int , num_unproce int ,last_col bigint)
BEGIN

  checkpoint;
insert into @T EXEC sp_filestream_force_garbage_collection @dbname = N'[DB Name]'; 

END
sorosh saloty
  • 1