Database Reference
In-Depth Information
Some vendors (like Oracle) support the notion of
composite partition-
ing
, which combines the basic data distribution methods above. In this way,
a table can be range partitioned, and each partition can be further subdivided
using hash partitioning.
7.8 Physical Design in SQL Server and Analysis
Services
In this section, we discuss how the theoretical concepts studied in this
chapter are applied in Microsoft SQL Server. We start with the study of
how materialized views are supported in these tools. We then introduce
a novel kind of index provided by SQL Server called column-store index.
Then, we study partitioning, followed by a description of how the three
types of multidimensional data representation introduced in Chap.
5
,namely,
ROLAP, MOLAP, and HOLAP, are implemented in Analysis Services.
7.8.1 Indexed Views
In SQL Server, materialized views are called indexed views. Basically, an
indexed view consists in the creation of a unique clustered index on a view,
thus precomputing and materializing such view. We have seen that this is a
mandatory optimization technique in data warehouse environments.
When we create an indexed view, it is essential to verify that the view
and the base tables satisfy the many conditions required by the tool. For
example, the definition of an indexed view must be deterministic, meaning
that all expressions in the
SELECT
,
WHERE
,and
GROUP BY
clauses are
deterministic. For instance, the
DATEADD
function is deterministic because
it always returns the same result for any given set of argument values
for its three parameters. On the contrary,
GETDATE
is not deterministic
because it is always invoked with the same argument, but the value it returns
changes each time it is executed. Also, indexed views may be created with
the
SCHEMABINDING
option. This indicates that the base tables cannot be
modified in a way that would affect the view definition. For example, the
following statement creates an indexed view computing the total sales by
employee over the
Sales
fact table in the Northwind data warehouse:
CREATE VIEW EmployeeSales WITH SCHEMABINDING AS (
SELECT EmployeeKey, SUM(UnitPrice * OrderQty * Discount)
AS TotalAmount, COUNT(*) AS SalesCount
FROM Sales
GROUP BY EmployeeKey )
CREATE UNIQUE CLUSTERED INDEX CI EmployeeSales ON
EmployeeSales (EmployeeKey)
Search WWH ::
Custom Search