Indexed views

Question

Indexed views

Igor Micev

SSC-Dedicated

Points: 33109
More actions
March 6, 2013 at 1:52 pm

#271964

Hi All,
I want to create indexed views. I don't want to have those heavy clustered indexes on the tables the view is created over. Can I create unique non-clustered keys with filter "not null" for the tables' keys instead of the clustered keys and then create a clustered key on the view?
Thanks in advance,
IgorMi
Igor Micev,My blog: www.igormicev.com

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no requirement that the base table for an indexed view has any indexes at all.

That said, I'm curious as to why you want a heap plus a nonclustered index for the base table rather than just a clustered index with no need for a secondary structure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

GilaMonster (3/6/2013)
There's no requirement that the base table for an indexed view has any indexes at all.

You're right.

That said, I'm curious as to why you want a heap plus a nonclustered index for the base table rather than just a clustered index with no need for a secondary structure.

To save space.

Thanks

IgorMi

Igor Micev,My blog: www.igormicev.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

IgorMi (3/6/2013)
That said, I'm curious as to why you want a heap plus a nonclustered index for the base table rather than just a clustered index with no need for a secondary structure.
To save space.

Err.....????

Heap + nonclustered index (what you suggest) means you have two structures, the table (which is the full size of the table), plus the nonclustered index, which will be smaller than the table, but still takes extra space.

Clustered index means you have one structure only, the clustered index, which is the size of the table.

Heap + nonclustered index is going to be larger, not smaller.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

GilaMonster (3/6/2013)
IgorMi (3/6/2013)
That said, I'm curious as to why you want a heap plus a nonclustered index for the base table rather than just a clustered index with no need for a secondary structure.
To save space.
Err.....????
Heap + nonclustered index (what you suggest) means you have two structures, the table (which is the full size of the table), plus the nonclustered index, which will be smaller than the table, but still takes extra space.
Clustered index means you have one structure only, the clustered index, which is the size of the table.
Heap + nonclustered index is going to be larger, not smaller.

You're absolutely correct here. I overlooked that.

Thanks

IgorMi

Igor Micev,My blog: www.igormicev.com