0

Assume a standard use case of customers and documents, where a customer can have many documents. Also assume that there will be a number of large files (anywhere from a couple of kilobytes up to a couple gigs) associated with each customer.

The DocumentStore table must have, at least...

DocumentId INT IDENTITY PRIMARY KEY, 
Stream VARBINARY(MAX) FILESTREAM NOT NULL,
DocGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
    UNIQUE DEFAULT NEWID

What are the pros and cons of fields such as CustomerId, FileExtension, FileSize, Hash, CreatedBy, CreatedDate, ... goitf in the same table or a different table (with a reference to the same DocumentId, of course)?

This question mainly stems from the uncertainty of performance and paging, etc., when querying columns. (Assume an additional index on CustomerId, but probably no others.)

emragins
  • 4,607
  • 2
  • 33
  • 48

1 Answers1

0

  1. FILESTREAM columns are in a separate filegroup, so there is no performance nor operational advantage.

  2. None of the FILESTREAM best practices mentioned that you gain some performance when you use separate table for FILESTREAM column

    https://msdn.microsoft.com/library/hh461480 https://blogs.msdn.microsoft.com/blogdoezequiel/2011/02/11/best-practices-on-filestream-implementations/ https://www.mssqltips.com/sqlservertip/1875/best-practices-when-using-filestream-feature-of-sql-server/

  3. Even it's not the case for regular VARBINARY columns

    What is the benefit of having varbinary field in a separate 1-1 table? SQL Server varbinary(max) and varchar(max) data in a separate table

  4. I tested by myself, and I had the same execution plan and the same statistics. enter image description here

DaNeSh
  • 1,022
  • 1
  • 14
  • 24