Shulou(Shulou.com)05/31 Report--

This article mainly shows you "SQL Server 2011 storage method is what", the content is simple and easy to understand, organized clearly, I hope to help you solve doubts, let Xiaobian lead you to study and learn "SQL Server 2011 storage method is what" this article bar.

When using a table with 1 terabyte of data and 1.44 billion records, Microsoft claims that column-based queries will increase CPU time by 16 times and usage time by 455 times. In real life, this means that a query that would have taken 501 seconds now takes only 1.1 seconds to complete. This test was performed on a computer with 32 logical processors and 256GB of memory.

Microsoft achieved this amazing improvement by segregating each column into its own set of pages. When a query is executed, only columns in the result set are loaded from disk. Pages containing other columns are ignored.

This approach is equivalent to creating an alternative index for every conceivable combination of columns. However, this approach doesn't consume a lot of disk space, and it actually takes up less space than traditional tables. Because SQL Server compression occurs at the page level, and because data in columns is more likely to duplicate than in rows, tables that use columns to store indexes will have a higher compression rating.

But we can't decide to use columns to store indexes just yet. First and foremost, they are non-renewable. Once the column store index is created, no inserts, updates, or deletes are allowed on the table. Microsoft expects more stores to refresh their data daily, otherwise they will need to make it read-only. During the refresh cycle, we delete the index, update the data, and then rebuild the index. Since this is certainly an expensive operation, we can use vertical partitioning to restrict operations to subsets of logical tables.

Using column-stored indexes also results in performance degradation. If you use most of the columns, regrouping will cost a lot of resources. This means that OLTP style queries should avoid this approach, which is advantageous for OLTP style queries. Or in other words, if you are writing SELECT* or fetching data one row at a time, column storage indexes are not for you.

The above is "SQL Server 2011 storage is what" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.