This should perform better than either a self-join or a recursive CTE.  The first CTE isn't part of the solution - I just used it because I don't have a...

You could drop the CROSS and change the WHERE to ON.  Same query, just avoiding the dreaded cross join syntax.

John

This is a problem with Management Studio, I think, not the database engine.  Where are you running SSMS?  Not on the server, I hope.  In order to avoid situations like...

This is a SQL Server Agent job, right?  What login does it run under?

The syslogins view has been deprecated for six versions now.  You should use sys.server_principals instead. ...

You only found one?!  My search yielded about 1,500,000 results.

Have you looked at the page_count column of dm_db_index_physical_stats?

John

I typed the name of this topic into my favourite search engine, and got all sorts of interesting results.  You could have done the same, and had an answer in...

Sam

So you're taking the whole set and comparing the value of each column in each row to the values of each column in the previous row?  The trouble...

David

Des's solution using dynamic SQL is right.  The reason your solution fails is that you get a parse-time error.  The query parser doesn't take account of control-flow logic...

What results were you expecting?  Since no matter how you slice it, you have only one row in each partition, and you set the default value (the third parameter to...

If you need two rows per ID then you're going to need something like a CROSS JOIN or CROSS APPLY.  But I'm afraid I still don't understand your requirement.  Please...