Hany Helmy (4/29/2014)
In the question: "If I do not want the cardinality estimator to be used," that crystal clear means to disable this feature.
but the answer states that you will run the database with an old version of Cardinality Estimator!
This is contradicting, right?
From my local help files on SQL 2014, it says
Upgrade all existing databases to use the new cardinality estimator. To do this, use ALTER DATABASE Compatibility Level (Transact-SQL) to set the database compatibility level to 120.
I "assume" that after upgrading the instance, by default this feature is disabled and only by altering the compatibility level it may start to use the new cardinality estimator.
-edit:// (added below quote)
The new cardinality estimator is enabled for all new databases created in SQL Server 2014. However, upgrading to SQL Server 2014 does not enable the new cardinality estimator on existing databases.
and... (the below is the additional reference on the trace-flag and what happens if that is enabled)
There are two new query_optimizer_estimate_cardinality XEvents to support the new query plans.
query_optimizer_estimate_cardinality occurs when the query optimizer estimates the cardinality on a relational expression.
query_optimizer_force_both_cardinality_estimation_behaviors occurs when both traceflags 2312 and 9481 are enabled, attempting to force both old and new cardinality estimation behavior at the same time.
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.