SQL Server Performance

View and Inner View

Discussion in 'SQL Server 2005 General Developer Questions' started by Sandy, Mar 23, 2009.

  1. Sandy New Member

    Hi All,
    As per my requirement, I need a VIEW which gives a resultset from multiple tables.
    But my requirement is little different, what I wants that the Select statement should join with another select statement to get my resultset for which I need to create one more inner view which I don't wants.
    Is it possible to write more than one select statement in a view?
    If not then how can I achieve my goal now...?
    I don't really need an extra view to get the required output.
    Can someone please help me for this?
    Even I know it can be achive by CTE but i want to use a view rather than sql statements because i am using this view for my report purpose.

  2. FrankKalis Moderator

    Not sure if I understand you correctly, but no a view can only return one resultset. You can however create and refer to a CTE inside the view definition.
  3. satya Moderator

    Is there any reason for not to use CTE to achieve this goal?
    CTEs can be useful when queries need to select from a set of data that does not exist as a table within the database. Views are generally used to break down large queries so they can be queried against in a much more readable manner.
    One solution is to create a view that gathers the aggregated data first and then write a query against that view. Another option is to query against the aggregated data using a derived table. You can do this by moving the SQL statement into the FROM clause and querying against it.
  4. Sandy New Member

    FrankKails & Satya,
    I am not sure about CTE can be used inside view or not.
    If it’s possible it is easier for me to do my task. Satya, I think your second option is not suitable for my condition as because of I need a direct select query from Report script so I think 1st option would be better.

    I have achieved by writing some complex query but it can be simpler as I believe in CTE.
    Let me try if it’s possible in inside View.
  5. FrankKalis Moderator

    It is possible. I use this here. However, I don't think the designer will let you do this, so you have to code this yourself.

Share This Page