Answer to the Partitioned Index Puzzle

A few weeks ago I posted a description of an error I encountered when creating a partitioned index on a SQL Server 2005 Standard Edition instance. It had me quite literally tearing my hair out. The error was a typically vague Microsoft one:

Msg 4436, Level 16, State 12, Line 1

UNION ALL view 'PartWindow.dbo.vwPartitionedView' is not updatable because a partitioning column was not found.

I spent quite a few hours staring at my SQL statements and re-reading documentation before the solution hit me.

If you haven't had a chance to read the post, please do before you skip down to the answer. It was, unfortunately, a total "duh!" moment. But at the same time quite easy to overlook.

A Quick Partitioned View Puzzle

I needed to create a few partitions on a legacy SQL Server 2005 Standard Edition instance. As partitions are not supported on standard edition, I needed to go the partitioned index route. In my research into how to  implement them, I found some really good resources:

• SQL Server Partitioning without Enterprise Edition
• MSDN: Using Partitioned Views

I'm not going to explain the basics of partitioning here, so you may want to read the above pages if you're not familiar with it before continuing.