1

I have a select query that is big in terms of number of lines . It is made big by a large sub query it needs. I do not want to see the sub query so often now. I want to scroll over it when further developing my main query. I have Intellisense enabled. I want to be able to collapse my sub-query with a +/- next to it.

The subquery is: 

inner join (select Dave.ID, .........) as Dave
on Dave.ID = Mike.ID

Is there anything I can write before and after the above sub-query (Dave) that will fold it so I do not have to see the sub-query (Dave) all the time?

Dasal Kalubowila
  • 103
  • 5
  • 22
  • I am assuming you have the Tools>Options>Text Editor> intellisense>Outline statements option checked! – DhruvJoshi May 23 '18 at 08:41
  • 1
    If a query is so long that it requires folding, it's *too* long and too complex. Fix it instead. Break it apart into separate views or CTES, *eliminate* subqueries etc. It will also be *slow* because one clause will end up preventing another from using indexes, or force the server to spill data into tempdb. – Panagiotis Kanavos May 23 '18 at 08:42
  • What does the query do? Reporting queries for example are infinitely faster and easier to write if you move the data into a reporting schema first. Adding a Calendar table can eliminate a *lot* of date, month, year and name calculations *and* speed up the query. Windowing functions can eliminate subqueries that calculate running totals, averages etc – Panagiotis Kanavos May 23 '18 at 08:44
  • BEGIN and END enables folding in SSMS. – EzLo May 23 '18 at 08:44
  • @EzLo Can we use BEGIN END inside sub query? – DhruvJoshi May 23 '18 at 08:45
  • I misread, the OP is doomed. – EzLo May 23 '18 at 08:46
  • Is there a simple solution to collapse part of a query? I work in Finance and not a db admin. The query/sub query is not that enormous. It still runs in zero seconds. Yes, outline statements ticked. – Dasal Kalubowila May 23 '18 at 08:56
  • I've re written the code as a CTE. How do I expand and collapse that? – Dasal Kalubowila May 23 '18 at 10:48
  • @Dasal Kalubowila I don't think SSMS allows partial collapsing of a query, all you can do is reduce scrolling by moving the sub-query out of the way with a CTE.. – pacreely May 23 '18 at 15:33

2 Answers2

1

An alternative to folding is to use a CTE structure to reposition the subquery.

;WITH Dave AS (
    SELECT Dave.ID,.....
    )
SELECT
    *
FROM
    Mike    
        INNER JOIN Dave
        ON Dave.ID = Mike.ID
pacreely
  • 1,881
  • 2
  • 10
  • 16
0
  1. Write a CTE or Temp table.
  2. Index the table if its a temp table.
  3. Use it in the main query where ever needed
Dheerendra
  • 284
  • 1
  • 7