To turn on the SQL Server Denali: Field Storage Index

The last three versions of SQL Server have been labeled with unofficial labels, each of which is tailored to the user base that most of the new features are targeting. For example, SQL Server 2005 is considered to be a developer-oriented version, while SQL Server 2008 is considered to be a data-oriented version of the administrator. SQL Server 2008 R2 with Microsoft powerpivot data analysis tools and improved SQL Server Analysis Services and Reporting services features is a well-known business intelligence ( BI) version.

Microsoft published the next version of SQL Server on professional association for SQL Server (pass) Summit 2010, held in Seattle in October (code Denali) The first Community Technology Preview version (CTP). It seems that this release will no longer be labeled with traditional labels; instead, Denali has features that are suitable for everyone to use. and several new features enable Denali to get people to start evaluating and testing it now, so you have to be prepared to update the business version at the end of the year or the next.

Fields Store Indexes

The field store is a new type of index for the SQL Server engine. The field storage index is supported by a new data architecture called VERTIPAQ, which is designed to achieve significant performance improvements relative to the regular index.

In a regular index, the index data from each row of records is kept on one page, and the data for each field is distributed across all pages. The field index packs the data from each field together, so each page contains the data for one field. In addition, the index data for each field is compressed, and because the field always contains a highly repeatable value, you can achieve a higher compression ratio. If you select only a small number of fields, this means fewer pages in the index and fewer pages to scan.

Because the nature of the data stored in different instances of SQL Server differs, it is difficult to know how fast data retrieval can be achieved in real-world applications, but Microsoft says it can be as fast as several times to hundreds of times times faster.

From a business point of view, this feature can change the way users use the Data warehouse. Traditionally, tables in the Data warehouse are too large to be retrieved in real time, so they are not suitable for temporary retrieval: The data in these tables must be aggregated, databases and reports generated, and 24x7 caching to provide a timely response to users.

Suppose you have 1,000 dollars in your current account and you can make a cheque of 500 dollars. But after checking the account information online, you'll find that the balance still shows up at 1,000 dollars. Banks sometimes need a certain amount of time to refresh their account balances, because it is not feasible and almost impossible to implement real-time updates for large datasets. Similarly, the data warehouse must always run extraction, transformation, and load (ETL) processes at night to update and summarize data.

However, a storage field index can be completed in a matter of minutes in the original one hours to complete the search, but also in a few seconds to complete the original number of minutes to complete the search, so your business can be converted to real-time retrieval methods. This means that we don't have to wait a few days for data collection and rollup time. What's the bottom line? It's easier for your business users and executive decision-makers to get the data they need.