Updating multiple columns sql server

26-Feb-2015 09:30 by 7 Comments

Updating multiple columns sql server

So what could go wrong, and what can be done to put things right?We’ll go through the most common problems and explain how it might happen and what to do about it.

These are passed to the Query Optimizer which first checks to see if it has a plan already available to execute it. To do so effectively, it needs to be able to estimate the intermediate row counts that would be generated from the various alternative strategies for returning a result.

If there’s no statistics, the optimizer will have to guess row-counts rather than estimate them, and believe me: this is not what you want!

There are several ways of finding out from both the estimated and actual execution plans whether the optimizer comes across missing statistics. The there will be an exclamation mark in the graphical execution plan and a warning in the extended operator information, just like the one in Picture 1.

Solution: If you experience performance problems with queries that have to search through XML data, or filter spatial columns e.g., XML or spatial indexes may help.

But that’s another story and beyond the scope of this article.

This can occur, if you use OPENROWSET or OPENQUERY for remote data access.

But you may also face this issue out of the blue, when working with DMVs.

A certain number of SQL Server’s DMVs are no more than a shell for querying data from internal tables.

You may see a Remote Scan operator for SQL Server 2005 or a Table Valued Function operator for SQL Server 2008 in this case.

As a rule of thumb, use temporary tables (tables with a “#” as their name’s first character), rather than table variables, for temp tables with more than 100 rows.

Problem: SQL Server does not maintain statistics for XML and spatial data types. So, don’t try to find statistics for these columns, as they’re simply not there.

When selecting from table variables, the estimated row-count is always 1, unless a predicate that evaluates to false, and doesn’t have any relationship to the table variable, is applied (such as WHERE 1=0), in which case the estimated row-count evaluates to 0.