{"id":1061,"date":"2008-04-07T23:07:33","date_gmt":"2008-04-07T23:07:33","guid":{"rendered":"\/blogs\/paul\/post\/SQL-Server-2008-How-to-get-FILESTREAM-and-partitioning-to-work.aspx"},"modified":"2013-04-02T18:12:39","modified_gmt":"2013-04-03T01:12:39","slug":"sql-server-2008-how-to-get-filestream-and-partitioning-to-work","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/","title":{"rendered":"SQL Server 2008: How to get FILESTREAM and partitioning to work"},"content":{"rendered":"


\n

Now we’re back from Iceland and I have a week to catch up with some content development before the MVP Summit next week and then SQL Connections the following week.<\/P>
\n

One of the things I struggled with earlier in the year while writing a SQL Server 2008 training course for Microsoft was how to get FILESTREAM to work with partitioning. There wasn’t (and still isn’t) any information in Books Online that I could find so I had to play around to figure it out.<\/P>
\n

I should say that the CTP-6\/February CTP version of Books Online *does* have a bunch of code examples around using FILESTREAM, so I’m not going to write a blog post about that. Look in the Getting Started with FILESTREAM Storage<\/EM> section.<\/P>
\n

Back to partitioning – first I created a test database:<\/P>
\n


\n

CREATE<\/FONT><\/FONT> <\/FONT>DATABASE<\/FONT> FileStreamTestDB
<\/FONT><\/FONT>ON<\/FONT> <\/FONT>PRIMARY
   <\/FONT><\/FONT>(<\/FONT>NAME<\/FONT> =<\/FONT> FileStreamTestDB_data,
      <\/FONT><\/FONT>FILENAME<\/FONT> =<\/FONT> N‘C:\\Metro Demos\\FileStreamTestDB\\FSTestDB_data.mdf’<\/FONT>),
<\/FONT><\/FONT>FILEGROUP<\/FONT><\/FONT> FileStreamFileGroup <\/FONT>CONTAINS<\/FONT> <\/FONT>FILESTREAM
   <\/FONT><\/FONT>(<\/FONT>NAME<\/FONT> =<\/FONT> FileStreamTestDBDocuments,
      <\/FONT><\/FONT>FILENAME<\/FONT> =<\/FONT> N‘C:\\Metro Demos\\FileStreamTestDB\\Documents’<\/FONT>)
<\/FONT><\/FONT>LOG<\/FONT><\/FONT> <\/FONT>ON
   <\/FONT><\/FONT>(<\/FONT>NAME<\/FONT> =<\/FONT> ‘FileStreamTestDB_log’<\/FONT>,
      <\/FONT><\/FONT>FILENAME<\/FONT> =<\/FONT> N‘C:\\Metro Demos\\FileStreamTestDB\\FSTestDB_log.ldf’<\/FONT>);
<\/FONT><\/FONT>GO<\/P><\/FONT><\/BLOCKQUOTE>
\n

Then I tried the obvious, knowing that I can’t partition on the ROWGUIDCOL<\/FONT>:<\/P>
\n


\n

CREATE<\/FONT><\/FONT> <\/FONT>PARTITION<\/FONT> <\/FONT>FUNCTION<\/FONT> MyPartFunction <\/FONT>(<\/FONT>INT<\/FONT>) <\/FONT><\/FONT>AS<\/FONT><\/FONT> <\/FONT>RANGE<\/FONT> <\/FONT>RIGHT<\/FONT> <\/FONT>FOR<\/FONT> <\/FONT>VALUES<\/FONT> <\/FONT>(<\/FONT>1000<\/FONT>,<\/FONT> 2000<\/FONT>);<\/P><\/FONT><\/FONT>
\n

CREATE<\/FONT><\/FONT> <\/FONT>PARTITION<\/FONT> <\/FONT>SCHEME<\/FONT> MyPartScheme <\/FONT><\/FONT>AS<\/FONT> PARTITION<\/FONT> MyPartFunction <\/FONT>ALL<\/FONT><\/FONT> <\/FONT>TO<\/FONT> <\/FONT>(<\/FONT>[PRIMARY]<\/FONT>);<\/P><\/FONT><\/FONT>
\n

CREATE<\/FONT><\/FONT> <\/FONT>TABLE<\/FONT> FileStreamTest <\/FONT>(
   <\/FONT><\/FONT>TestId UNIQUEIDENTIFIER<\/FONT> ROWGUIDCOL<\/FONT> NOT<\/FONT> NULL <\/FONT><\/FONT>UNIQUE<\/FONT>,
   <\/FONT><\/FONT>Customer INT<\/FONT>,
   <\/FONT>[<\/FONT><\/FONT>Name]<\/FONT> VARCHAR<\/FONT> (<\/FONT>25),
   <\/FONT>[<\/FONT><\/FONT>Document]<\/FONT> VARBINARY<\/FONT>(<\/FONT>MAX<\/FONT>)<\/FONT> FILESTREAM<\/FONT>)
<\/FONT><\/FONT>ON<\/FONT><\/FONT> MyPartScheme <\/FONT>(<\/FONT>Customer<\/FONT>);
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

Partition scheme ‘MyPartScheme’ has been created successfully. ‘PRIMARY’ is marked as the next used filegroup in partition scheme ‘MyPartScheme’.
Msg 1921, Level 16, State 4, Line 8
Invalid filegroup ‘default’ specified.<\/FONT><\/FONT><\/P><\/BLOCKQUOTE>
\n

Eventually I worked out that you have to define a separate partitioning scheme just for FILESTREAM data. This is because the regular data is stored on non-FILESTREAM filegroups, so trying to use the regular partitioning scheme for FILESTREAM would mean telling the Engine to store the FILESTREAM data in non-FILESTREAM filegroups. Clearly a non-starter. Ok – try again with a separate partitioning scheme (the prior MyPartFunction<\/FONT> partition function and MyPartScheme<\/FONT> partition scheme already exist now remember):<\/FONT><\/FONT><\/P>
\n


\n

CREATE<\/FONT><\/FONT> <\/FONT>PARTITION<\/FONT> <\/FONT>SCHEME<\/FONT><\/FONT> MyFSPartScheme <\/FONT>AS<\/FONT> PARTITION<\/FONT> MyPartFunction <\/FONT>ALL<\/FONT><\/FONT> <\/FONT>TO<\/FONT> <\/FONT>(<\/FONT>[FileStreamFileGroup]<\/FONT>);<\/P><\/FONT><\/FONT>
\n

CREATE<\/FONT><\/FONT> <\/FONT>TABLE<\/FONT> FileStreamTest <\/FONT>(
   <\/FONT><\/FONT>TestId <\/FONT>UNIQUEIDENTIFIER<\/FONT> ROWGUIDCOL<\/FONT> NOT<\/FONT> NULL<\/FONT> UNIQUE<\/FONT>,
   <\/FONT><\/FONT>Customer <\/FONT>INT<\/FONT>,
   <\/FONT><\/FONT>[Name] <\/FONT>VARCHAR<\/FONT> (<\/FONT>25),
   <\/FONT><\/FONT>[Document] <\/FONT>VARBINARY<\/FONT>(<\/FONT>MAX<\/FONT>)<\/FONT> FILESTREAM<\/FONT>)
<\/FONT><\/FONT>ON<\/FONT><\/FONT> MyPartScheme <\/FONT>(<\/FONT>Customer<\/FONT>)
<\/FONT><\/FONT>FILESTREAM_ON<\/FONT><\/FONT> MyFSPartScheme<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

Partition scheme ‘MyFSPartScheme’ has been created successfully. ‘FileStreamFileGroup’ is marked as the next used filegroup in partition scheme ‘MyFSPartScheme’.
Msg 1908, Level 16, State 1, Line 1
Column ‘Customer’ is partitioning column of the index ‘UQ__FileStreamTest__03317E3D’. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
<\/FONT><\/FONT><\/P><\/BLOCKQUOTE>
\n

Hmm – I can’t partition on Customer<\/FONT> because there’s already a unique index over TestId<\/FONT> – UNLESS I specifically set the unique index on TestId<\/FONT> to be non-partitioned by setting a filegroup for it:<\/FONT><\/FONT><\/FONT><\/FONT><\/P>
\n


\n

CREATE<\/FONT><\/FONT> <\/FONT>TABLE<\/FONT> FileStreamTest <\/FONT>(
   <\/FONT><\/FONT>TestId <\/FONT>UNIQUEIDENTIFIER<\/FONT> ROWGUIDCOL<\/FONT> NOT<\/FONT> NULL <\/FONT><\/FONT>UNIQUE<\/FONT> ON<\/FONT> [PRIMARY],
<\/FONT><\/FONT>   Customer <\/FONT>INT<\/FONT>,
   <\/FONT><\/FONT>[Name] <\/FONT>VARCHAR<\/FONT> (<\/FONT>25),
   <\/FONT><\/FONT>[Document] <\/FONT>VARBINARY<\/FONT>(<\/FONT>MAX<\/FONT>)<\/FONT> FILESTREAM<\/FONT>)
<\/FONT><\/FONT>ON<\/FONT><\/FONT> MyPartScheme <\/FONT>(<\/FONT>Customer<\/FONT>)
<\/FONT><\/FONT>FILESTREAM_ON<\/FONT><\/FONT> MyFSPartScheme<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P><\/BLOCKQUOTE>
\n

That works! Now – the BIG issue with this setup is that switching partitions won’t work while the unaligned index is enabled. So how to disable it? First we need to find out what it’s called:<\/FONT><\/FONT><\/P>
\n


\n

SELECT<\/FONT><\/FONT> <\/FONT>*<\/FONT> <\/FONT>FROM<\/FONT> <\/FONT>sys.indexes <\/FONT><\/FONT>WHERE<\/FONT> [object_id] =<\/FONT> OBJECT_ID<\/FONT> (<\/FONT>‘FileStreamTest’<\/FONT>);
<\/FONT><\/FONT><\/FONT>GO<\/FONT><\/P><\/BLOCKQUOTE>
\n

And then disable it:<\/FONT><\/FONT><\/P>
\n


\n

ALTER<\/FONT><\/FONT> <\/FONT>INDEX<\/FONT> UQ__FileStre__8CC33161060DEAE8 <\/FONT>ON<\/FONT> FileStreamTest <\/FONT>DISABLE<\/FONT>;<\/FONT><\/FONT><\/P><\/BLOCKQUOTE>
\n

Now you can do partition switching. Here’s the catch – to re-enable the index you need to REBUILD it – which is a size of data operation! The upshot of all this is that partitioning can be made to work with FILESTREAM data but partition switching is no longer a metadata-only operation.<\/FONT><\/FONT><\/P>
\n

Hopefully this will be addressed for V2.<\/FONT><\/P><\/FONT><\/FONT><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"

Now we’re back from Iceland and I have a week to catch up with some content development before the MVP Summit next week and then SQL Connections the following week. One of the things I struggled with earlier in the year while writing a SQL Server 2008 training course for Microsoft was how to get […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41,65,86],"tags":[],"class_list":["post-1061","post","type-post","status-publish","format-standard","hentry","category-filestream","category-partitioning","category-sql-server-2008"],"yoast_head":"\nSQL Server 2008: How to get FILESTREAM and partitioning to work - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2008: How to get FILESTREAM and partitioning to work - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Now we’re back from Iceland and I have a week to catch up with some content development before the MVP Summit next week and then SQL Connections the following week. One of the things I struggled with earlier in the year while writing a SQL Server 2008 training course for Microsoft was how to get […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-04-07T23:07:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-04-03T01:12:39+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/\",\"name\":\"SQL Server 2008: How to get FILESTREAM and partitioning to work - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-04-07T23:07:33+00:00\",\"dateModified\":\"2013-04-03T01:12:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2008: How to get FILESTREAM and partitioning to work\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server 2008: How to get FILESTREAM and partitioning to work - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2008: How to get FILESTREAM and partitioning to work - Paul S. Randal","og_description":"Now we’re back from Iceland and I have a week to catch up with some content development before the MVP Summit next week and then SQL Connections the following week. One of the things I struggled with earlier in the year while writing a SQL Server 2008 training course for Microsoft was how to get […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/","og_site_name":"Paul S. Randal","article_published_time":"2008-04-07T23:07:33+00:00","article_modified_time":"2013-04-03T01:12:39+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/","name":"SQL Server 2008: How to get FILESTREAM and partitioning to work - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-04-07T23:07:33+00:00","dateModified":"2013-04-03T01:12:39+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-how-to-get-filestream-and-partitioning-to-work\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2008: How to get FILESTREAM and partitioning to work"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1061","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1061"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1061\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1061"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1061"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1061"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}