SQL Server - concepts and capabilities of partitioned
views - Feb 27, 2010 at 11:50 AM by Shuchi Gauri
Explain the concepts and capabilities of partitioned views.
It’s a view defined by a UNION ALL of member tables structured in same way but
stored separately as multiple tables in either same SQL Server or multiples
ones (federated database servers). Therefore, they allow data in a large table
to be split into smaller member tables. Data between member tables is based on
ranges of data values in one of the columns. Data ranges for each member table
are defined in a CHECK constraint which is specified on the partitioning
column.
To execute update on a partitioned view, the partitioning column must be part of
the primary key of the base table. If the view is not updateable then we can
create an INSTEAD OF trigger on that view to allow updates on it. A local
partitioned view is one which has all the member tables on the same server,
else it’s known as Distributed partitioned view. Partitioned view thus allows
easier maintenance of member tables.
SQL Server - concepts and capabilities of partitioned
views - May 05, 2009 at 22:00 PM by Rajmeet Ghai
Explain the concepts and capabilities of partitioned views.
Partitioned view joins the horizontally portioned data. This data may belong to
one ore more servers. It makes the data appear as one table. A partitioned view
can either be local or distributed. Distributed may reside on a different
server. The original table is replaced with several smaller member tables. Each
member table is on a separate member server.
SQL Server - concepts and capabilities of partitioned
views - June 21, 2009 at 09:00 AM by Amit Satpute
Explain the concepts and capabilities of partitioned views.
Concept:
-
Using Partitioned views, a large table can be split into smaller member tables.
-
The data-partitioning between the member tables is done on the basis of the
ranges of data values in one of the columns.
-
A CHECK constraint specified on the partitioning column defines these ranges.
-
UNION ALL is used by views to combine the selects of all the member tables into
a single result set.
-
When SELECT statements referencing the view specify a search condition on the
partition column, the query optimizer uses the CHECK constraint definitions to
determine which member table contains the rows.
Capabilities:
-
Partitioned views make it easier to maintain the member tables independently.
-
The newest period can be added and the oldest dropped by changing the
definition of the partitioned view for current results.
-
The past results view can also be updated to remove and archive the oldest
period it covers.
Also read
SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns,
just like a real table..............
Catalog views can be used to return information like objects, logins permissions
etc used by SQL server database engine...............
Table, Data types, Function, Index, Constraint, Rule, Default, Stored
Procedures, Trigger, View..........
Answer - Views can be created referencing tables and views only
in the current database.......
Explain the concepts of faster differential backups.
Explain the concepts of Parallel Database consistency check (DBCC)
Define Indexed view.
Define Distributed partitioned views.
|
