Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
FIX: Incorrect results if you use parentheses around SELECT in a statement that has a common table expression in SQL Server 2008
Article ID: 2322209 - View products that this article applies to.
On a computer that is running Microsoft SQL Server 2008, consider the following scenarios.
Scenario 1You specify a common table expression (CTE) by using the WITH statement. For example, you run the following query.
Then, you run this query.
In this scenario, the cached query plan for this query stores incorrect statement_start_offset and statement_end_offset values. Specifically, you notice the statement_end_offset value is smaller than the statement_start_offset value when you run sys.dm_exec_query_stats dynamic management view (DMV).
Because of this problem, a performance report may fail when the report assumes that the statement_start_offset value is always smaller than the statement_stop_offset value.
Scenario 2You specify a common table expression by using the WITH statement, and you use the RECOMPILE option. For example, you create a function in SQL Server 2008 by using the following script.
When you run a “select dbo.function1()” query, you receive the following error message:
A severe error occurred on the current command. The results, if any, should be discarded.
Additionally, this error can occur without the RECOMPILE option if the SQL server is experiencing heavy server traffic.
Scenario 3You specify a common table expression by using the WITH statement. In the WITH statement, you specify a table that does not exist. For example, you run the following query.
When you run this query, you do not receive an error for the missing table.
These problems occur because of a malfunctioning processing of a SELECT statement that follows a WITH statement. The WITH statement uses the following syntax.
This statement then creates a result set that is assigned to the common table expression name that was specified. Then, you follow this statement with a SELECT statement. If this second statement is enclosed in parentheses, and then you try to perform an operation on the results or you try to run a sys.dm_exec_query_stats dynamic management view, you receive incorrect results or an error.
Service pack information
To resolve this problem, obtain the latest service pack for SQL Server 2008. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/968382/ )How to obtain the latest service pack for SQL Server 2008
To work around these problems, you have to remove the parentheses from the statement that follows the common table expression. For example, consider the following WITH statement that uses such parentheses.
To work around these problems, change to the following WITH statement that does not use these parentheses.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
This problem was first corrected in SQL Server 2008 Service Pack 2 (SP2).
For more information about how to use the WITH statement with a common table expression, visit the following MSDN webpage:
WITH common_table_expression (Transact-SQL)For more information about sys.dm_exec_query_stats, visit the following MSDN webpage:
Article ID: 2322209 - Last Review: September 21, 2010 - Revision: 3.0