Check existence on other tables: performance advice
I'd probably write something like the following:

SELECT TOP 1 -- Top command optional and may not actually help speed things up
  p.ProfileId -- No need to alias to the same thing
  , p.Username
  , p.UsernameCleaned
  , IsUser = CAST(CASE WHEN u.UserId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
  , IsPage = CAST(CASE WHEN pg.PageId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
FROM dbo.Profiles p
  LEFT JOIN dbo.Users u ON u.UserId = p.ProfileId -- Left join here instead of exists above
  LEFT JOIN dbo.Pages pg ON pg.PageId = p.ProfileId
WHERE
  p.UsernameCleaned = @Token;


If you notice I basically just traded EXISTS for LEFT JOIN. This should give you reasonable performance and be fairly clear for people who review your code. Of course the standard disclaimer applies for any performance question.


  You should profile this on a representative workload to determine what is fastest, and indexes are your friend.


In general though you want to avoid subqueries in the SELECT portion of your query because they will probably be run once for each row in the result set. Here that isn't a big deal but if you have more results then the difference can be dramatic. 

Try at all costs to avoid the NOLOCK query hint. It looks nice on paper and can give you an apparent boost, but nothing is free. For more detailed information on why this probably isn't a good idea I'd advise you read this blog post by Aaron Bertrand.

Also of note rewriting your query made it clear that a profile can be both a user and a page. I don't know your use cases but that feels wrong to me especially since this query seems to be designed to tell the difference. If that is true then you might need to rework your data model.

Finally as a personal preference I like to have my aliases on the left if the column is computed/derived like IsUser above. My reasoning goes the alias can get lost in the confusion of the calculations so it is best to put it first. Of course you should go with what you and your coworkers find is most legible.