skip to Main Content

FILESTREAM and Partitioning

There is a requirement for a large number of large PDF documents to be managed by a SQL Server database without the contents of the documents actually being stored in the database, for size reasons. FILESTREAM meets these requirements.

There is an additional requirement to store documents of different types in different folders so as to be able to take advantage of multiple disk drives. Partitioning allows a single table to be allocated to multiple file groups according to values in certain columns and so again this meets the requirements.

Combining FILESTREAM and partitioning, however, turned out not to be as simple as expected.

I started by enabling FILESTREAM on SQL Server using:

sp_configure ‘filestream access level’, 2


I then created a database with two FILESTREAM file groups using:


( NAME = N’FileStreamTest’, FILENAME =N’c:\FileStreamExample\FileStreamExample.mdf’),


( NAME = N’FStream1′, FILENAME = N’c:\FileStreamExample\FStream1′),


( NAME = N’FStream2′, FILENAME = N’c:\FileStreamExample\FStream2′)


( NAME = N’FileStreamTest_log’, FILENAME =N’c:\FileStreamExample\FileStreamExample_log.ldf’)

Note that each FILESTREAM file group can only one have one folder associated with it, hence the need for multiple file groups.

I then proceeded to setup partitioning by creating a partitioning function. The document type will be either 1 or 2 and each one of these should be assigned to a different file group:




Next I assigned created two partitioning schemes, one for the FILESTREAM content and one for the other columns in the table. I need both because the table and the FILESTREAM need to follow the same partitioning scheme:



TO (FStream1, FStream2)





Then I created the table:

CREATE TABLE FSPartExampleTable (

ID uniqueidentifier not null ROWGUIDCOL,

DataType int NOT NULL,

TheData varbinary(max) filestream NOT NULL)

ON PartScheme(DataType)


But I got error: A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

So I added a PRIMARY KEY on the ID column as below:

CREATE TABLE FSPartExampleTable (

ID uniqueidentifier not null ROWGUIDCOL PRIMARY KEY,

DataType int NOT NULL,

TheData varbinary(max) filestream NOT NULL)

ON PartScheme(DataType)


But then I got the error: Partition columns, DataType in this case, for a unique index must be a subset of the index key. I tried again with a UNIQUE constraint and got the same error.

It seemed like there were two conflicting requirements here: Firstly the ID column must be unique and secondly the unique column must contain the partitioning column. So I tried to create a primary key based on both ID and DataType:

CREATE TABLE FSPartExampleTable (

ID uniqueidentifier not null ROWGUIDCOL,

DataType int NOT NULL,

TheData varbinary(max) filestream NOT NULL,

PRIMARY KEY (ID, DataType ))

ON PartScheme(DataType)


But then I was back to the first error: But I got error: A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

So FILESTREAM is pretty insistent on having a unique index on the ID column, whereas partitioning wants to have a composite index if the index is to be partitioned. That’s when I realised what the issue was, the unique indexes are being associated with the partitioning scheme of the table and actually I do not need this to be the case. So I changed my primary key to be non-clustered, so it is separate from the table, and associated it with a different file group, [PRIMARY] in this example, and the issue was solved:
CREATE TABLE FSPartExampleTable (

ID uniqueidentifier not null ROWGUIDCOL


DataType int NOT NULL,

TheData varbinary(max) filestream NOT NULL)

ON PartScheme(DataType)



Now the FILESTREAM data for DataType 1 and 2 are stored in different folders as required:

INSERT INTO FSPartExampleTable

VALUES (NEWID(), 1, CAST (‘Part 1 …data’ AS varbinary(max)))

INSERT INTO FSPartExampleTable

VALUES (NEWID(), 2, CAST (‘Part 2 …data’ AS varbinary(max)))

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to [email protected]

About the Author

Geoff Ballard

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).


Our Technologies



Ballard Chalmers is one of the UK’s leading software development companies, specialising in engineering enterprise applications on the Microsoft Platform. We are principally dedicated to custom software development and integration for the Microsoft Cloud, as well as on-premises and hybrid systems. Our expertise is in Microsoft .NET and Azure, SQL Server, BizTalk Server, SharePoint Server and Xamarin.

01342 410223

[email protected]



This form collects your name and details so that we may answer you. Check out our Privacy Policy for full details on how we protect and manage your data.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

© 2020 Ballard Chalmers Ltd. All Rights Reserved

Back To Top