Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: Partition view and Index

    I have created a horizontal partition view from 4 physical tables.
    just wondering how the index works in the partition view:
    1) If I need to build an index on a column, do I need to build
    it on all 4 physical tables? or I just build it on the view? or build
    it on view and 4 physical tables?

    2) If I build it on view, and if I add a table into view, do I need to
    recreate all indices on the view?

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    BOL:

    A view must meet these requirements before you can create a clustered index on it:
    ---
    The SELECT statement in the view cannot contain UNION operator.
    ---

  3. #3
    Join Date
    Jan 2004
    Posts
    8
    Originally posted by snail
    BOL:

    A view must meet these requirements before you can create a clustered index on it:
    ---
    The SELECT statement in the view cannot contain UNION operator.
    ---
    oh, thanks!
    btw, what's difference between the index on the view and index
    on actual table? if a query running agains a view can pick up
    the index on the table itself, why should we need to build a
    index on the view?

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    OK .. here i go again (confused)

    Code:
    use pubs 
    go
    create table a (intvalue int)
    create table b (intvalue int)
    go
    insert into a select 1
    insert into b select 1
    go
    create view c as 
    select intvalue from a union select intvalue from b
    go
    create clustered index idx_c  on c (intvalue) -will explode
    go
    create view d as select intvalue from c
    go
    create clustered index idx_d  on d (intvalue) -- should not explode but does  ...Question is why ???
    go
    drop table a
    drop table b
    drop view c
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Enigma
    OK .. here i go again (confused)

    Code:
    use pubs 
    go
    create table a (intvalue int)
    create table b (intvalue int)
    go
    insert into a select 1
    insert into b select 1
    go
    create view c as 
    select intvalue from a union select intvalue from b
    go
    create clustered index idx_c  on c (intvalue) -will explode
    go
    create view d as select intvalue from c
    go
    create clustered index idx_d  on d (intvalue) -- should not explode but does  ...Question is why ???
    go
    drop table a
    drop table b
    drop view c
    View has to be created WITH SCHEMABINDING if you are going to have indexes on it.

    Even so - clustered index has to be unique:

    Server: Msg 1941, Level 16, State 1, Line 1
    Nonunique clustered index cannot be created on view 'c' because only unique clustered indexes are allowed.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, because d is based on c which contains a UNION.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    hmmm ... need to refer the Holy book
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •