Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 19 of 212

9 C H A P T E R 1 | Faster queries In previous versions of SQL Server, the best practice is to use an INT field as a predicate for your queries against columnstore tables because of the retrieval process used by the database optimizer to get rows from the internal dictionary supporting the columnstore index. SQL Server 2016 supports predicate pushdown of the varchar and nvarchar data types for columnstore indexes to improve performance for queries that might need to filter on a string value. Getting started with in-memory analytics The easiest way to get started with in-memory analytics is to add a clustered columnstore index to a disk-based table, as shown in Example 1-6. When you use the DROP_EXISTING = ON option in the CREATE COLUMNSTORE INDEX statement, the database engine drops an existing index before it creates a new one. If you omit this option and a columnstore index already exists, or if you include this option and there is no existing columnstore index, an error occurs. Notice that the syntax in this example does not include a column list because a clustered columnstore index uses all columns in the table. Example 1-6: Creating a clustered columnstore index in a disk-based table --Eliminate the DROP_EXISTING option if there is no existing columnstore index in the table CREATE CLUSTERED COLUMNSTORE INDEX ix_cci_Product ON ProductBig WITH (DROP_EXISTING=ON); Using filtered columnstore indexes A potential problem with running OLTP and analytic workloads on the same tables by using this approach is the overhead of maintaining the clustered columnstore index, which can have an adverse effect on OLTP workloads. To minimize this impact, you can instead create a nonclustered columnstore index with a filtered condition, as shown in Example 1-7. That way, the columnstore index contains only the warm data—the data that changes infrequently—and does not require updates to keep current with hot data—the frequently changing data in the table. The filter should restrict the index to warm-data rows to reduce the amount of updates required to keep the index up to date with the table. In addition, you should add a clustered index that includes the column in the columnstore index's filtered predicate. In this case, the clustered index contains every row in the table, including the hot data that is not covered by the columnstore index, as shown in Figure 1-3. An analytic query transparently combines both warm and hot data as needed to produce the correct result set. Example 1-7: Creating a nonclustered columnstore index with a filtered condition --Create the columnstore index with a filtered condition CREATE NONCLUSTERED COLUMNSTORE INDEX ix_salesorderheaders_ncci ON Sales.SalesOrderHeader (CustomerID, AccountNumber, SalesOrderID, SubTotal, ShipDate) WHERE ShipDate < '2014-01-01'; --Create the clustered index on column with a filtered condition --(but first drop existing clustered index, not shown) CREATE CLUSTERED INDEX ix_salesorderheaders_ci ON Sales.SalesOrderHeader (ShipDate); -- The following query returns rows from NCCI and hot rows not in NCCI SELECT TOP 5 Year(ShipDate) as ShipYear, AccountNumber, SUM(SubTotal) as TotalSales FROM Sales.SalesOrderHeader WHERE ShipDate > '2013-10-01' GROUP BY Year(ShipDate), AccountNumber;

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016