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

This article introduces the relevant knowledge of "what is the difference between the old and new versions of SQL Server CE". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Compare the difference between CE7 and 2014 CE12:

1. How to estimate join columns in table joins

The old CE estimates the statistics step of all participating join columns one by one. The new CE only collects and estimates the maximum and minimum step statistics, and the old CE has a more accurate estimation when the values of the join columns are unevenly distributed.

two。 Multi-conditional estimation for where statements

The old CE believes that each condition is calculated independently and there is no correlation, and the estimation result is based on the condition with the highest selectivity. The new CE estimation method is conditional 1 selective sqrt (conditional 2 selective) sqrt (conditional 3 selective), in which conditional 1 selectivity > conditional 2 > condition 3. This correlation estimation has a more accurate estimation for multi-conditional statements, and the new CE is more accurate.

Estimation of 3.where condition beyond the boundary of statistical information

The old CE estimates the where condition beyond the statistical information boundary simply using the value corresponding to the statistical information boundary step, while the new CE estimates the total number of rows * row density, which is more accurate.

4. Estimation of multiple conditional equivalent connections in connection condition on

The old CE uses the selectivity of single conditional columns for simple multiplication, and the value is often too small. The new CE uses the smaller columns after repeating the two conditional columns to multiply the average density of the two tables, and the estimated value is too large.

5. Multi-conditional non-equivalent connection estimation.

The old CE simply multiplies the selection rate of the join condition columns, and the estimated value is on the low side. The new CE assumes that the join is an one-to-many relationship between the small table and the large table, and calculates the input estimate of the large table, the estimated value is on the high side.

6. Non-join condition column estimation:

The old CE thinks that the non-join condition columns are independent of each other, and selects the condition columns with larger estimated values and thinks that they meet both ends of the join at the same time, and the estimated value is too high. The new CE thinks that the non-join conditions of different tables are not related, so first estimate the join selectivity, and then apply the non-join condition column filtering.

7. Repeat operation after connection (distinct,group by)

The old CE estimates many-to-many connections before de-duplication, which is on the high side, while the new CE first removes weights and then uses smaller results to estimate connections.

This is the end of the content of "what is the difference between the old and new versions of SQL Server CE". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.