The first CTP of SQL Server 2014 is available for almost two months. For those of you that are about to install it now on Windows Server 2012, here are some advices to make the process smooth.
The following interesting case came out from our SQL User Group member Ilian Yovchev who works for David Holding company.
They have updated their text filed types to varcharmax data type according to the SQL Server roadmap and deprecation plans. They use update with WRITE to insert data in those new fields.
But they faced time outs during some updates and on query executions against the table with varcharmax field type. They have traced those time outs and found that the reason behind them is sync stats update performing on varcharmax col. Strange, isn’t it? Well, not exactly as synch stats update (Asych Stats Update=OFF in db level) can potentially lead to query time out.
Let’s see the specifics of this case.
Some Misconceptions and main differences
Every time I perform a code review or read some blog posts that use temp tables or temp variables I find that the difference between temp tables and temp variables is not well understood. The common misconception for example is that both are created in memory, the other is that it is not recommended to use temp tables rather than table variables (vise versa is also common), or people just prefer to use one or another temp object without knowing if it is appropriate for specific case scenario.
Here are some examples in order to clarify the differences.
Time for creating a personal web site and my own technical blog. I hope it is the right place to share so much experience from my trainings and consulting projects that will help you in your daily work.
Good luck and thanks for reading! 🙂