Option b is to insert the results of this query into a table variable or temp table, then do the self join there.

He y jeff,can you repost your list of valid points, I'd like to read them now that our homework is over!

Hence my more complicated / advanced t-sql solution .

Ya even better, use a single in statement.  Learn something new every day .

In a procedure :

CREATE TABLE #tmp (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, DesignationName VARCHAR(100), Description VARCHAR(1000))

INSERT INTO #tmp (DesignationName, Description)

SELECT DesignationName, Description FROM dbo.YourBaseTable ORDER BY ?

--the...

Dpends on the queries you build.  Many of the system views join many tables, many times even in some cases.  So if you can avoid that and do a clustered...

We need the table definition, sample data, the required output from that sample data.  We also need to know what you define as the middle rows.

And knowing for which cool...

Nope, you pretty much just answered your own question.  I'm sure that there are other factors to consider in the equation but it looks like you got the key ones...

It works.  But the session times out after like 10-15 minutes.  So you better make sure you have a pasted copy somewhere before hitting post.

In the 2nd plan, I guess that the computing is done previously to the query and that it doesn't need to be redone afterwards.

But that's still and interesting point!

Here's a question : What happens if during that select, the @@identity value is changed by another process?  Does the plan consider it to be a scalar value that will never...

Always the last one .

I had missed this one too.  Select @SomeVar from dbo.SomeTable is a valid sql statement that will scan the table.  I just thaught that this was a feature I had...

What was the problem?

you can add linked servers (sp_addlinkedserver) and use this select method :

Select *  linkedServerName.dbName.dbo.tblName  t1 inner join l1.db2.dbo.t2 t2 on ...