none
Common table expression defined but not used.. why?

    Question

  • Out of curiosity.. why do you get an error if you define a common table expression but don't use it in your query?

    Thanks! Josh

    Tuesday, July 30, 2013 3:30 AM

All replies

  • Hello Josh,

    I am not sure I understood your question clearly. But the below work fine with me:

    Drop Table T11
    Create Table T11(Col1 int)
    Insert into T11 Select 100
    Go
    Alter Proc sp1
    AS
    Begin
    	;With CTE
    	As
    	(
    		Select * From T11 Where col1 = 100
    	)
    	--Select * From cte
    
    	Select * From T11 Where Col1 = 100
    End	


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 5:27 AM
  • Hi Josh,

    I can't understand your problem. But below code working fine to me...

     
    create Proc sp1 
    AS
    Begin
    	;With CTE
    	As
    	(
    		Select * From T11 Where col1 = 100
    	)
    	Select * From cte
    	
    End	
    --exec sp1 

    Thanks

    Jayamani.E




    Tuesday, July 30, 2013 6:31 AM
  • WITH CTE
    AS
    ( SELECT 1 )
    
    SELECT 1
    
    -- Error

    Strange that it works inside a procedure.. the above errors though?

    Thanks! Josh

    Tuesday, July 30, 2013 6:42 AM
  • WITH CTE
    AS
    ( SELECT 1 )
    
    SELECT 1
    
    -- Error

    Strange that it works inside a procedure.. the above errors though?

    Thanks! Josh

    Since you don't have the relevant column name above, can you try this?

    WITH CTE
    AS
    ( SELECT 1 as col1)
    SELECT col1
    from cte;
    


    Many Thanks & Best Regards, Hua Min

    Tuesday, July 30, 2013 6:52 AM
  • Hi Josh,

    You defined cte table not used why?. thats the reason error showing  like this

    Msg 422, Level 16, State 4, Line 22
    Common table expression defined but not used.
    

    so you have to use the cte....

    Thanks

    Jayamani.E

    Tuesday, July 30, 2013 6:52 AM
  • WITH CTE
    AS
    ( SELECT 1 )
    
    SELECT 1
    
    -- Error

    Strange that it works inside a procedure.. the above errors though?

    Thanks! Josh


    A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

    A CTE can be used to:

    • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

    • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

    • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

    • Reference the resulting table multiple times in the same statement.

    Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

    CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

    A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

    The basic syntax structure for a CTE is:

    WITH expression_name [ ( column_name [,...n] ) ]

    AS

    ( CTE_query_definition )

    The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

    The statement to run the CTE is:

    SELECT <column_list>

    FROM expression_name;


    Many Thanks & Best Regards, Hua Min

    Tuesday, July 30, 2013 6:58 AM
  • Hi Josh,

    Use this code inside of the procedure .You have to give the alias name on that column and call the CTE.

    WITH CTE
    AS
    ( SELECT 1 AS VALUES1)
    SELECT * FROM CTE

    Thanks

    Jayamani.E

    Tuesday, July 30, 2013 7:05 AM
  • Hi,

    It looks like many of us misunderstood Josh's question. 

    The below works:

    Drop Table T11
    Create Table T11(Col1 int)
    Insert into T11 Select 100
    Go
    Alter Proc sp1
    AS
    Begin
    	;With CTE
    	As
    	(
    		Select * From T11 Where col1 = 100
    	)
    	--Select * From cte
    
    	Select * From T11 Where Col1 = 100
    End	

    However, without Proc it will not work

    	;With CTE
    	As
    	(
    		Select 1 'Col1' --From T11 Where col1 = 100
    	)
    	--Select * From cte
    
    	Select 1 
    

    However, Josh, it looks like the below works as it supress the error message. I dont have any clue why this happens but few of my tweaking as below:

    	;With CTE
    	As
    	(
    		Select 1 'Col1' --From T11 Where col1 = 100
    	)
    	--Select * From cte
    
    	Select 1  From T11 Where Col1 = 100
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 8:19 AM
  • Thanks latheesh u understand me. Obviously I understand what a CTE is.. After years of reading

    Celko it sinks in :)

    your second query is most intersting.. You are not referencing the cte however the addition of the where clause seems to prevent the error. My question is obviously why in a query we must reference a declared cte but in the example you give.. The error doesn't occur! Most interesting !


    Thanks! Josh

    Tuesday, July 30, 2013 8:26 AM
  • Aha...May be am wrong, but want to share...

    May be, SQL Server expects the usage of CTE if you give a table as next statement and consider the statement as part of CTE definition. If the table is not specified in the statement, there is no way to use the CTE defined, hence it gives you an error message as "DEFINED BUT NOT USED".


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 8:34 AM
  • I don't get an error if CTE is not used but defined in SQL Server 2012:

    USE AdventureWorks2012;
    GO
    
    WITH CTE AS ( SELECT ProductName=Name FROM Production.Product)
    SELECT * FROM Sales.SalesOrderHeader;
    GO
    -- (31465 row(s) affected)
    
    CREATE PROC zTestCTE AS
    WITH CTE AS ( SELECT ProductName=Name FROM Production.Product)
    SELECT * FROM Sales.SalesOrderHeader;
    GO
    -- Command(s) completed successfully
    EXEC zTestCTE;
    GO
    -- (31465 row(s) affected)


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Tuesday, July 30, 2013 9:11 AM
    Moderator
  • Hi Kalman, Try the below and see if you get any error in 2012 please:

    WITH CTE AS ( SELECT ProductName=Name FROM Production.Product)
    SELECT1


    The below is what am thinking on it...

    May be, SQL Server expects the usage of CTE if you give a table as next statement and consider the statement as part of CTE definition. If the table is not specified in the statement, there is no way to use the CTE defined, hence it gives you an error message as "DEFINED BUT NOT USED".


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 9:16 AM
  • That gives an error also in SQL Server 2012.

    Pretty much a nuisance issue. Why would you design a CTE for no usage?


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Tuesday, July 30, 2013 10:06 AM
    Moderator
  • It's true that it's a nonsense issue but I was just wondering what the reasons are for the error


    Thanks! Josh

    Tuesday, July 30, 2013 10:54 PM
  • We need to get an answer from someone from MS SQL Server team here. Say, if David B sees this thread and answers it, it would be good.

    I also got an error in SQL 2012 with the statement above.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 30, 2013 11:19 PM
    Moderator
  • Hi Josh,

    First, please compare the following codes.

    USE AdventureWorks2012;
    GO
    
    WITH CTE
    AS
    ( SELECT 1 as col1)
    
    SELECT 1
    --Common table expression defined but not used.
    
    
    WITH CTE
    AS
    ( SELECT 1 as col1)
    
    SELECT 1 from Sales.SalesOrderHeader;
    -- no error
    I think to first piece of query codes, since the “select” command follows a CTE table definition and there is no “from” phase specified, SQL Server takes these codes as uncompleted, to the second piece of codes, despite the “select” command follows a CTE table definition, there exists “from” phase, SQL Server take these codes as completed.

    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support


    Wednesday, July 31, 2013 3:38 AM
    Moderator
  • Yes the behaviour is as described.

    However why does the error occur, a SELECT statement is legal without a FROM clause.. ?

    Just curious about the consistency of the error handling here?


    Thanks! Josh


    • Edited by Josh Ashwood Wednesday, July 31, 2013 3:55 AM asdfasd
    Wednesday, July 31, 2013 3:54 AM
  • My share of thought....

    This isue happens I think, only when a constant is specified in the main query of CTE without referencing the CTE (I don't check with a UDF). When specify a OPTION (RECOMPILE) all these scripts will work, for example:

    WITH CTE
    AS
    (SELECT 1 as col1)
    SELECT 1
    OPTION (RECOMPILE);
    -- No errors
    
    During compilation Query Engine expands the CTE definition and plugg in to the main query and keep this generic plan in cache. However in case of a constant in the SELECT of main query, I think a constant scan is trying (constant folding?) by the optimizer, but the presence of CTE prevent this to make a generic plan and cause an error 'Common table expression defined but not used.'. When the RECOMPILE option is specified query engine is not trying for a generic plan to keep in cache and always a constant scan is executed without any issue.

    Hope a Microsoft guy will show up with a reasonable answer for this.


    Krishnakumar S

    Wednesday, July 31, 2013 7:45 AM