Your browser (Internet Explorer 7 or lower) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.

X

Update with WRITE on varcharmax column leads to time-outs

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.

Temp tables or table variables

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.