FIX: Incorrect results if you use parentheses around SELECT in a statement that has a common table expression in SQL Server 2008

On a computer that is running Microsoft SQL Server 2008, consider the following scenarios.

Scenario 1

You specify a common table expression (CTE) by using the WITH statement. For example, you run the following query.
WITH common_table_expression AS (SELECT * FROM sys.objects)(SELECT * FROM common_table_expression)
Then, you run this query.
SELECT *FROM sys.dm_exec_query_statsWHERE statement_start_offset > statement_end_offsetANDstatement_end_offset <> -1
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 2

You 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.
CREATE FUNCTION function1() RETURNS int ASBEGINDECLARE @x intSET @x=1;WITH common_table_expression AS (SELECT @x AS column1) (SELECT @x=column1 FROM common_table_expression) OPTION(RECOMPILE)RETURN @xEND
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 3

You 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.
WITH computed_table (id) AS(SELECT id FROM this_table_does_not_exist) (SELECT id FROM dbo.computed_table) GO
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.
WITH common_table_expression AS(CTE_query_definition)
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:
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.
WITH common_table_expression AS (SELECT *FROM sys.objects)(SELECT * FROM common_table_expression)
To work around these problems, change to the following WITH statement that does not use these parentheses.
WITH common_table_expression AS (SELECT *FROM sys.objects)SELECT * FROM common_table_expressio
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:For more information about sys.dm_exec_query_stats, visit the following MSDN webpage:
sql cte code query

Αναγνωριστικό άρθρου: 2322209 - Τελευταία αναθεώρηση: 09/21/2010 03:25:00 - Αναθεώρηση: 3.0

Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Workgroup

  • kbqfe kbfix kbexpertiseadvanced kbsurveynew kbtshoot kbquery KB2322209