How to "index" on Partitioned-View?

Question

How to "index" on Partitioned-View?

HankHo

SSC Enthusiast

Points: 161
More actions
January 3, 2005 at 4:16 am

#61209

Now I build a local partitioned-view [X_View](use UNION ALL)
But when I make "index" on [X_View]
I always get the error "Cannot index the view X_View. It contains one or more disallowed constructs"
I have followed "Creating Indexed View" Rules
And I know that error may arise from "UNION ALL"
But I have to build local partitioned-view to use "UNION ALL"
Can anyone solve my problem?
To use index on local Partitioned-view
Thanks
Hank

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Osoba SSCommitted Points: 1964 More actions · Answer 1

I got this to work by building the index on the 1st select pass and commenting out the 2nd (and other union all passes). I then uncommented out the Sql passes with the union all.

Hope that helps

HankHo SSC Enthusiast Points: 161 More actions · Answer 2

Dear Osoba:

I know what you mean, thanks!

But my local partitioned-view is created from 12 "the same" Table

For example

Create View [X_View]

WITH SCHEMABINDING

AS

select [c1],[c2],[c3] from [owner].[test_01]

union all

select [c1],[c2],[c3] from [owner].[test_02]

union all

.

select [c1],[c2],[c3] from [owner].[test_12]

So all the "select pass" are the same,

I can't comment out any field "select pass with union all"

Anyone help?

Thanks

vinny_1973 SSCommitted Points: 1832 More actions · Answer 3

I think u've answered ur own question there ... in that you cannot have an indexed view that contains a UNION statement! if u actually look at the rules for Indexed views, u can barely use SELECT, FROM, tablename !!!

As such, fundamentally, u cannot have a Indexed, Partitioned view!

HankHo SSC Enthusiast Points: 161 More actions · Answer 4

Thanks , Vinny.

If "Index" on Partitioned-View is impossible,

I think I got the answer!

Thanks, every guy!