Microsoft® SQL Server® 2012 Bible

1038

Part VII: Performance Tuning and Optimization

FROM Production.WorkOrder W

JOIN Production.Product P

ON P.ProductID = W.ProductID

JOIN Production.ScrapReason S

ON W.ScrapReasonID = S.ScrapReasonID

SELECT * FROM vScrap

Although indexed views are essentially the same as they were in SQL Server 2000, the

Query Optimizer can now use indexed views with more types of queries.

Updating Indexed Views

As with any denormalized copy of the data, the diffi culty is keeping the data current.

Indexed views have the same issue. As data in the underlying base tables is updated, the

indexed view must be kept in sync. This process is completely transparent to the user and

is more of a performance consideration than a programmatic issue.

The Columnstore Index

New to SQL Server 2012 is the Columnstore index, which is structured and behaves differ-

ently than traditional B-tree indexes. With B-tree indexes, column values are stored in rows

in a page. However, with Columnstore indexes, the column values are stored together in

segments, which allows for incredible data compression while dramatically increasing speed

of processing for scanning huge amounts of data.

The major benefi t for Columnstore indexes are for DataWarehouse style queries where a

large Fact table is joined with smaller Dimension tables and data is scanned to produce

the wanted result. When a Columnstore index is added to a table, it causes the table to be

readonly until the index is dropped, which is also ideal for DataWarehouse tables because

they are often updated infrequently and at defi ned intervals.

Consider the following script to create a Columnstore index on the dbo.FactFinance table in

the AdventureWorks DataWarehouse sample database. The syntax should be familiar; it is

practically the same as creating a nonclustered index. The only different is that in a tradi-

tional NC index, you only include the columns that you use in the index, and you need to

be careful about the order in which you defi ne those columns. However, in a Columnstore

index the recommendation is to include every column in the table, the order in which you

defi ne the columns in the index does not matter.

CREATE NONCLUSTERED COLUMNSTORE INDEX idx_CS_Finance

ON dbo.FactFinance

(

FinanceKey, DateKey, OrganizationKey, DepartmentGroupKey,

ScenarioKey, AccountKey, Amount, Date

)

c45.indd 1038c45.indd 1038 7/31/2012 10:16:44 AM7/31/2012 10:16:44 AM

http://www.it-ebooks.info