Search code examples
sqlt-sqlimplementsfiletable

Implementing FileTable

I've got a quick question which I hope I can get some help with. I need to create a database and then implement File Tables for the following text files:

  1. testFile1.txt
  2. testFile2.txt
  3. testFile3.txt

Once this process is finished, I simply have to delete testFile3.txt using t-sql statement.

My question is, how do I implement the text files? I understand how to create them and how to create the database, but am unsure of how to move the files into the database.

Solution

  • Generally, you can choose one of the following techniques:

    • Upload the file in specific folder in your server and store in your database only the URL to the file. Latter, use the URL for displaying the content or downloading the file
    • Upload the file and store in the database in BLOB (Binary Large OBject) field

    Each of these, has advantages and disadvantages and it is up to your situation and you to decided which technique for use.

    Fortunately, when we are using SQL Server for storing files we have one more option which can lead to better performance - Filestream Storage.

    This type of storage is recommended in the following situations:

    • Objects that are being stored are, on average, larger than 1 MB.
    • Fast read access is important.
    • You are developing applications that use a middle tier for application logic

    If your files are larger then 1 MB, I believe you should use this technique but:

    For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

    You can check the following this tutorial in order to activate the storage.