locked
Multiple FILESTREAM columns in one table to point toward different FileStream groups? RRS feed

  • Question

  • I was wondering if it is possible to assign different columns in a table to different FileStreamGroups? Also, Is there a way to point FileStream data to a certain directory based on a condition?

    EXAMPLE:

    Agencies

    AgencyA

    AgencyB

    AgencyC

    FILESTREAM Groups

    AgencyGroupA

    AgencyGroupB

    AgencyGroupC

     

    If there are files coming from AgencyA to store on this table, files go into AgencyGroupA

    If there are files coming from AgencyB to store on this table, files go into AgencyGroupB

    If there are files coming from AgencyC to store on this table, files go into AgencyGroupC

     

    Thursday, July 8, 2010 3:30 PM

Answers

  • According to theory you cannot have multiple FILESTREAM filegroups per partition.  You may be interested in partitioning the table.  See
    http://msdn.microsoft.com/en-us/library/cc949109(SQL.100).aspx for details.  I've posted below a syntax example from the whitepaper.

    CREATE PARTITION FUNCTION DocPartFunction (INT)
    AS RANGE RIGHT FOR VALUES (100000, 200000);
    GO
    CREATE PARTITION SCHEME DocPartScheme AS
    PARTITION MyPartFunction TO (Data_FG1, Data_FG2, Data_FG3);
    GO
    CREATE PARTITION SCHEME DocFSPartScheme AS
    PARTITION MyPartFunction TO (FS_FG1, FS_FG2, FS_FG3);
    GO
    CREATE TABLE DocumentStore (
        DocumentID INT IDENTITY PRIMARY KEY,
        Document VARBINARY (MAX) FILESTREAM NULL,
        DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
           UNIQUE DEFAULT NEWID () ON Data_FG1)
    ON DocPartScheme (DocumentID)
    FILESTREAM_ON DocFSPartScheme;
    GO
    
    

    Thursday, September 16, 2010 7:50 AM