I have a large table which has grown to 162 columns.
For better performance, I am considering Vertical partitioning to split this
single table into smaller tables
[Orders : OrderProperties : OrderExtendedProperties ] using views to combine
when needed.

I've done a lot of online research to find best practices for creating this
scenario, but I havent found much detailed information on how to use Views
and especially Triggers to automate the process of performing
Inserts,Updates,Deletes..  Can someone please offer an article that will
help me build a sound, scalable structure while avoiding pitfalls.

Thanks in advance.

This can help you with the INSTEAD OF triggers part of the show:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01a17.asp

A for what goes where, you seem to be taking the right approach.  The stuff
that is most-heavily queried should be in one table while the rest is in the
other.  There's a 1:1 relationship between them, which you can enforce with
a FK.

I have a large table which has grown to 162 columns.
For better performance, I am considering Vertical partitioning to split this
single table into smaller tables
[Orders : OrderProperties : OrderExtendedProperties ] using views to combine
when needed.

I've done a lot of online research to find best practices for creating this
scenario, but I havent found much detailed information on how to use Views
and especially Triggers to automate the process of performing
Inserts,Updates,Deletes..  Can someone please offer an article that will
help me build a sound, scalable structure while avoiding pitfalls.

Thanks in advance.

In addition to Tom's reply, not just the items which are queried frequently,
but if there are many columns which are changed frequently might also be good
candidates for the same partition.
Show quoteHide quote

Thank you both very much!  Now I just need to examine my most common queries
to see how to split up the table.
The problem is, that there is often ad-hoc types of reports that need access
to all three levels simultaneously [OrderCharge details as well as
OrderAddress details which could be in 2 separate partitions].

I worry that if there are too few places that could benefit by using only 1
partition at a time, I will have added overhead of using the Views to inner
join the ~3 partitions, without any recognizable gains.. But I guess that's
what testing is for.

Thanks again..
-Chris



Show quoteHide quote

Chris,

Because covering indexes and indexed views are available, and storage
is way cheaper, we do not use vertical split as much as we used to 10
years ago. Instead of splitting a table, you may leave it as is and
create one or several narrower covering indexes and/or indexed views.

Advantages: no need to join, no maintenance, no complex programming.
Disadvantage: sometimes more storage is needed.

sql query - sp
How can I Rearrange my Data
tring’
Query taking long-long time
FOREIGN KEY that REFERENCES a server login for a database?