Suggest performance tradeoffs I have a an inner query that needs outer query column values as input. It looks something like this

CREATE TABLE TISSET(    COL1 int ,COL2 int,COL3 int)
INSERT INTO  TISSET VALUES (1,1,1);
INSERT INTO  TISSET VALUES (1,2,1);
    SELECT TTOuter.Col1,(
        SELECT CASE WHEN SUM(TTInner.Col3 - TTOuter.COL2 + TTOuter.COL1 ) &gt; 0 THEN 'BIG' ELSE 'SMALL' END D
        FROM TISSET TTInner)
    FROM TISSET TTOuter
DROP TABLE TISSET;


But this raises the following error.

"Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression."

In order to overcome this, after referring to this Stack overflow discussion, I have to modified the code as following.

CREATE TABLE TISSET(    COL1 int ,COL2 int,COL3 int)
INSERT INTO  TISSET VALUES (1,1,1);
INSERT INTO  TISSET VALUES (1,2,1);
    SELECT TTOuter.Col1,(
        SELECT CASE WHEN SUM(TTInner.Col3 - TTInner.COL2 + TTInner.COL1 ) &gt; 0 THEN 'BIG' ELSE 'SMALL' END D
        FROM (
            SELECT TTInner.Col3, TTOuter.COL2, TTOuter.COL1 
            FROM TISSET TTInner
        ) TTInner)
    FROM TISSET TTOuter
DROP TABLE TISSET;


I understand that using functions can be very costly, so dont want to create functions. 

Please suggest performance factors to consider if there are any to make this better.
I have not applied this yet on the actual table, as that table would contain upto several million rows.
Here are some suggestions on comparing different queries, since that is what you will most likely end up doing. I have made these types of remarks in the past. These recommendations are somewhat based on those comments from the community. 

Create the Table TISSET

CREATE TABLE TISSET(    COL1 int ,COL2 int,COL3 int)


Create Random Data

As @peer mentionned in his comments, you should run your query on actual data. One important step on top of that, is to query against data that closely matches the actual production data you will be using. In the step below, I create random data but that is more evenly distributed. In other words, every N insertions, the values repeat themselves. 

-- create random data
DECLARE @ROWNUM as bigint = 1
WHILE(1=1)
BEGIN
    set @rownum  = @ROWNUM + 1
    insert into TISSET values(1 + (CHECKSUM(NEWID())) % (30*60),
    1 + (CHECKSUM(NEWID())) % (24*60), 1 + (CHECKSUM(NEWID())) % (60*60))

    if @ROWNUM &gt; 50000
        BREAK;  
END


The amount of data and how its distributed affects the way in which the Query Optimizer chooses which execution plan to use. Having a lot of records is not sufficent, you must also consider how its distributed: number of unique values, one column having more duplicates than another. 

After adding so much data, you should also consider page splits and fragmentation.
This query comes from Paul White. 

 SELECT
    DDIPS.index_type_desc,
    DDIPS.alloc_unit_type_desc,
    DDIPS.index_level,
    DDIPS.fragment_count,
    DDIPS.avg_fragment_size_in_pages,
    DDIPS.page_count,
    DDIPS.avg_page_space_used_in_percent,
    DDIPS.record_count,
    DDIPS.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'TISSET', N'U'),
        NULL,
        NULL,
        'DETAILED'
    ) AS DDIPS;


There are no indexes in TISSET, but if you did create a clustered index and/or non-clustered index, you would get back the level of fragmentation. Having heavingly fragmented data can skew results and dramatically slow things down.

Clear out the cache

Here we are cleaning out the cache and running a checkpoint. When comparing queries, it can be helpful to know the elapsed time when a query is in cache and when it is being read from disk into cache. 

-- Cold cache
DBCC DROPCLEANBUFFERS
CHECKPOINT
CHECKPOINT


DBCC DROPCLEANBUFFERS cleans all buffers. Do not use this command on a production server as you will seriously slow your server down as it reads data back into memory from the disk.

CHECKPOINT - All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or since the last checkpoint), regardless of the state of the transaction that made the change. 

STATISTICS IO and TIME

The statistics IO statement will send back interesting information 

Logical pages read, was tempdb used.

Here is the BOL page on STATISTICS IO

The statistics TIME statement will send back Execution times, both CPU and User. 

Here is the BOL page on STATISTICS TIME

Output from your query

STATISTICS IO

Table 'TISSET'. Scan count 49898, logical reads 6834930, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I tend to look at logical reads (Number of pages read from the data cache.) and Worktable (tempdb may have been used). A closer examination of the execution plan would be necessary. Looking at yours, the use of the Sort operator indicates that tempdb was probably used, if not enough memory was available to perform the sort.



STATISTICS TIME 

SQL Server Execution Times:
   CPU time = 819333 ms,  elapsed time = 113509 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

-- compare queries
set statistics io on
set statistics time on

-- first query

   SELECT TTOuter.Col1,(
        SELECT CASE WHEN SUM(TTInner.Col3 - TTInner.COL2 + TTInner.COL1 ) &gt; 0 THEN 'BIG' ELSE 'SMALL' END D
        FROM (
            SELECT TTInner.Col3, TTOuter.COL2, TTOuter.COL1 
            FROM TISSET TTInner
        ) TTInner)
    FROM TISSET TTOuter


-- second query goes here

set statistics io on
set statistics time on


Then of course there is the Execution plan which you can review by simultaneously pressing CTRL+M or under the Query Menu in SSMS, choose Display Acutal Execution Plan



If you were analyzing multiple queries, each one would get a batch cost. This is not an exact metric, but I find it useful when the cost difference between two queries is significant. 

Although this information won't answer every question, it can be used as a starting point for comparing queries and deciding which one is best.