• Replies 10 replies
  • Subscribers 336 subscribers
  • Views 158 views
  • Users 0 members are here
Related

SQL Server 2016 (130) compatibility level

David Frederick
$organization

Hi,

Does anyone know whether the impresario database of v14.1 or v15 can safely operate at SQL Server 2016 (130) compatibility level? We are running on SQL Server 2016, but currently using SQL Server 2014 (120) compatibility level. It would be nice to be able to use newer features, such as the STRING_SPLIT() function. 

Thanks,
David

Parents
  • $organization

    We are stuck at 2012 levels. The new Cardinality Estimator (CE) does not work well with some of our older procedures/functions. Since we are self hosted, this project will be long. Everything custom will need to be tested with queryhints or DBCC hints. 

  • $organization in reply to Troy Nelson

    Hi Troy - Microsoft added a database option called Legacy Cardinality Estimation in 2016. If you set that to ON, SQL Server uses the old cardinality estimator even if you are at 120 or 130 compatibility levels. We are using 2014 (120) compatibility level with this turned on, and it resolved the issues we had with the new cardinality estimator. I'm hoping to be able to change that at some point down-the-road. 

Reply
  • $organization in reply to Troy Nelson

    Hi Troy - Microsoft added a database option called Legacy Cardinality Estimation in 2016. If you set that to ON, SQL Server uses the old cardinality estimator even if you are at 120 or 130 compatibility levels. We are using 2014 (120) compatibility level with this turned on, and it resolved the issues we had with the new cardinality estimator. I'm hoping to be able to change that at some point down-the-road. 

Children