CODEWITHSUNDEEP
sql-serverfiletable
user2541236
user2541236

Reputation: 11

SQL Server FileTable : delete file

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)?

Upvotes: 1

Views: 3012

Answers (3)

sorosh saloty
sorosh saloty

Reputation: 1

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

Upvotes: 0

Francesco Cogno
Francesco Cogno

Reputation: 116

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.

Upvotes: 3

Eben Varghese Paul
Eben Varghese Paul

Reputation: 21

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

Upvotes: 0

Related Questions