1

How do I partition an Indexed View in MS-SQL ? I have a index view created which stores range of values. The view definition is as follows

CREATE VIEW dbo.target_individual_product WITH SCHEMABINDIN       
    AS SELECT day_dim.date_time AS Date,  
    SUM(ISNULL(order_dim.quantity,0)) AS Target_Acheived,  
    branch_dim.branch_name AS Branch_Name ,   
    product_dim.product_name AS Product_Name  
FROM dbo.day_dim INNER JOIN  
     dbo.order_fact ON day_dim.day_id = order_fact.day_id  
INNER JOIN dbo.product_dim ON order_fact.product_id = product_dim.product_id   
INNER JOIN dbo.branch_dim ON order_fact.branch_id = branch_dim.branch_id   
INNER JOIN dbo.order_dim ON order_fact.order_id = order_dim.order_id  
GROUP BY order_dim.quantity, day_dim.date_time,branch_dim.branch_name, product_dim.product_name  
GO
CREATE UNIQUE CLUSTERED INDEX target_individual_product_I on target_individual_product (Date)

Now i want to partition this table using date column. How do I do that ?

skaffman
  • 398,947
  • 96
  • 818
  • 769
Deepak
  • 6,684
  • 18
  • 69
  • 121
  • Could you display your SQL in a more readable way and without spelling errors? If you can do it in your code, you can surely do it on Stack Overflow ;) – littlegreen Nov 17 '10 at 10:40
  • Here's a nice article that describes partitioning an indexed view: http://www.sqlservercentral.com/articles/partition/64740/ – Andomar Nov 17 '10 at 10:57

2 Answers2

2

I have little experience with partitioning, but I think you are confusing things. (Gurus, please correct me if I'm wrong).

As far as I know there are three types of partitioning in SQL Server:

A partitioned table can be partitioned on a column such as date.

A partitioned view is a view specifying a UNION between similar queries from different tables.

A partioned-aligned indexed view is an indexed view that is partitioned along the same column(s) as the partitioned table to which it is linked.

I don't think it is possible to partition an indexed view without partitioning the underlying table. Therefore, I would suggest that you partition your day_dim on the date_time column, and then create a partition-aligned indexed view to match this column. See this link and scroll down to Query 11 for an example how to do this.

littlegreen
  • 7,290
  • 9
  • 45
  • 51
  • thanks for that!! if you could help me with indexing it would be very nice. Is there any equivalent for bitmap indexing in sql ? – Deepak Nov 17 '10 at 11:08
  • I don't know, sorry. But Google knows: http://social.msdn.microsoft.com/Forums/en-US/sqldatawarehousing/thread/3b7fc3a0-32e2-4c73-99c1-1357ebcc4753 – littlegreen Nov 17 '10 at 12:04
1

You appear to be looking for a "partition-aligned" index; partitioning is supported in SQL 2008 Enterprise Edition (you didn't mention your version or edition). Partitioned indexes are discussed in Books Online, but there is no discussion of partitioning view indexes that I can find, although it is possible and it's described in a white paper here (see query 11 at the end of the paper):

http://msdn.microsoft.com/en-us/library/dd171921.aspx

Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • Hah, you found the same example :) – littlegreen Nov 17 '10 at 10:59
  • Thank you!! I am using 2008 version. If there is no facility for creating on views then tel me how do i partition a table categorizing by text values. For example "text1,text2,text2" so content with text1 should go to partition1 and text2 into partition2 ... – Deepak Nov 17 '10 at 11:12
  • Follow a good tutorial. For example: http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/ – littlegreen Nov 17 '10 at 12:03