Just to add insult to injury, if you try...

No. I actually found out the "true"/"false" functionality...

My guess is there's probably a better way to formulate the problem you're trying to solve so that this isn't required, but for the sake of argument, lets say that...

What is it you're trying to optimize for while doing this? You mentioned "rebuilding FK"s several times, which isn't really a thing. Are you trying to keep up-time on the...

Perhaps make a brand new table with the structure you want (the new pk and any fk constraints) and populate it with all your data.

For instance, given your old table...

Just FYI there is a TSQL function which obviates the need for your case statement:

declare @age varchar(30) = null

select nullif(@age, 'null')

That's correct. If you do it through a view, anyone calling that view who doesn't know what's going on will experience it as any other column, but as you say,...

This is just combining what I said and what Sean just said, but you can either apply the row number when you generate the report, or create a view with...

Would a view work, with the row_number defined there?

Aww, I thought it was clever 🙂

Any way you slice it this isn't going to be super costly, but another spin on it would be to use aggregate functions rather than case or inequality statements. This...

Where did you read nolock is deprecated?

Not that I'm encouraging use of nolock, but I see it's deprecated in from clauses in updates and inserts, but not straight selects. Unless...

There may be a stealthier way to do this without the need for a tally table, but one doesn't come to mind at present. Here's how I'd attack it:

Create a...

Look at the final batch of that plan and you'll see it supports exactly what I said. 84% of the cost if coming from a clustered index lookup for the...

Just to elaborate on my theory (and it is just that) what I think might be going on is that the index I mention (the one with a bunch of...