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.
I identified the tables to be partitioned and the columns to use as the partitioning keys. As is quite common, I decided to break the tables down by a date field. I created the new tables - one for each month. I then created the column constraints on the date column of each new table to serve as the partitioning column. Finally, I created the partitioned view. To test I ran an insert of 100 rows from the source table into the view. and BLAM:
Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'PartWindow.dbo.vwPartitionedView' is not updatable because a partitioning column was not found.Huh? I definitely defined the partitioning column. So I searched round the web, revisiting pages a number of times to find what I did wrong. Minutes turned to hours...It was time to go home. Then the next day, after all my morning DBA tasks were done, I came back to my partitioned views. I stared at my code, I compared it with the code in BOL, on other people's blogs, I reread forum after forum. I reran my script time and again in vain.
I was just about to post a question on sqlservercentral's forum when I thought, let me take one more look at this thing. And just then I noticed my mistake. Wow, I was embarrassed and extremely thankful I hadn't posted on the forum. In an effort to see how thick I am, let me know how long it took you to find my error.
Run the dynamic SQL below to get the scripts for the view, tables and the insert statement and write a quick comment about what the error is and how long it took to discover it.
See my next post for the answer!
No comments:
Post a Comment