Create index on a view

Question

Post reply

Create index on a view

chaitu_ece2000

SSC Rookie

Points: 37
More actions
March 4, 2010 at 6:42 am

#221239

Hi
I am confused if this is the best way of doing.
I have a table with 50 Million records, as the table is growing larger day by day and for better maintenance.
Since I am on standard edition, I am planning to split the table in to 5 tables with 10 Million each and create a view on top of it.
Four tables will be used for read-only and the the last one will be used for inserts and selects.
I have 3 non-clustered and a clustered on the table, to achieve the best performance of this.
The view is just union of all the tables
Select a,b,c from table1
union
Select a,b,c from table2
union
.
.
Select a,b,c from table5
Can some one add some light this if this is the best way of doing and what is the best way to create index on the view for best read/write.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

rpatil22 Ten Centuries Points: 1371 More actions · Answer 1

If it sql 2005 then you can try to use table partition.

By ur approach , I think create index on individual tables and then create view

In view use Union ALL

SELECT C1,C2 FROM T1

Union ALL

SELECT C3,C4 FROM T2

chaitu_ece2000 SSC Rookie Points: 37 More actions · Answer 2

Thanks for your response.

I have a table with > 50 Million rows.

Since I am on standard edition (cant use partitioning) I want to split the table for different purposes.

Break the table in to 5 individual tables with same schema. (Tab1,Tab2,Tab3,Tab4,Tab5)

I want to create a view with union of all the 5 tables. Only reads and updates as historic data on first 4 tables. The tab5 will be used for insert/reads.The data in tab5 is used in most of the queries

Some Stored Procs use only historic data where the first table is sufficient in providing the data. So only tab 1 will be used in select queries where applicable.

Some Stored Procs use all the data in 5 table. So I wan tot use view with 5 tables properly indexed.

Some stored Procs use only the 5th table. So only the tab5 will be used for select

After a month or 2 the 1st table containing historic data will be truncated as it will not be in use. By this time, the tab 5 will be a part of historic data and tab 6 (new table with the same schema will be created) will come in for use(inserts/reads), where the view will be updated to contain union of table 2 till table 6.

For better performance and maintenance, I want to split the tables in this way.

I am assuming using and breaking the table/table unions in this way as a view will help maintaining(re-index seperately) and for better performance, does this cause any performance degradations?

Please provide some inputs. Thanks for any helps on this.

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 3

partitioning is good option in this case..Can u provide us table schema??

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 4

chaitu_ece2000 (3/9/2010)
Since I am on standard edition, I am planning to split the table in to 5 tables with 10 Million each and create a view on top of it.

chaitu_ece2000 (3/9/2010)
Since I am on standard edition (cant use partitioning)

Sqlfrenzy (3/9/2010)
partitioning is good option in this case...

:blink: :doze: :rolleyes:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 5

See Using Partitioned Views. Note carefully the need for trusted, enabled CHECK constraints, and the use of UNION ALL - not UNION.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 6

Sqlfrenzy (3/9/2010)
partitioning is good option in this case..Can u provide us table schema??

I lost it here too..I must go to sleep :sick:

Regards,
[font="Verdana"]Sqlfrenzy[/font]