Common Table Experssion (CTE) Format

Posted on 2011-10-12
Last Modified: 2012-05-12
In SQL 2008 Management Studio I developed a CTE in a view. My question or issue is that when I reopen the view I lose the begining syntax of the CTE. In other words my CTE starts as  With XYZ as (Select A, B, C etc),  I then save the view and when I reopen it the With XYZ as ( is gone, so the view now only shows Select A, B, C etc.....

I also developed a stored procedure using this same view and it works ok. So overall my application is OK, however why would I lose that syntax when reopening the CTE?
Question by:SeTech
    LVL 5

    Accepted Solution

    Hi SeTech,

    Are you creating & subsequently "opening" the view in the built in editor in SSMS? (rmb on view -> design)

    If so this may be why, the editor does some odd things to SQL, I'd suggest writing the code direct in a query and to subsequently edit it do script view as Alter


    LVL 75

    Expert Comment

    by:Anthony Perkins
    As alluded to in the previous comment, you have come across one of the many "quirks" of the Designer tool and why most SQL Developers that have used SSMS more than a week avoid it like the plague.
    LVL 13

    Expert Comment

    Hi, If you are creating something like the following, it should work.
    create view v1 as 
    with t(a,b) as
    (select 1 as a,2 as b union select 3 as a,4 as b)
    select * from t

    Open in new window


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now