Sql-server – Using a 512 bit binary column as a bit field

Pros - It is the natural key, it makes sense and it will likely be searched on, I presume?

Cons - The default behavior (which is totally changeable) is for a primary key to be the clustered index. An alphanumeric doesn't make the best candidate because inserts can cause page splits because they aren't set on an ever increasing value like an identity column. The Int identity column will take less space (4bytes) compared to the character data (40+bytes for the unicode) . This makes your other indexes larger since the clustered key is part of them. If you ever change how you identify your customers and make customer codes, this all breaks - going with a surrogate insulates you from those type of changes.

In this situation, I tend to optimize for the insert performance and go with an identity column more often than not for the clustered key and primary key. I really like integer clustered indexes. (Now I know your question was not about clustered index, it was about primary key... You could still choose some other column to be the clustered index and make this your primary key, you could also put a unique constraint on this and treat it as a natural key but not make it your primary key).

I would at the very least index this with a unique constraint and treat it like a natural key. I just don't know if you really need to make it your primary key.

Kimberly Tripp is a trusted resource who has a lot to say about primary keys and (more so) clustered keys on her blog - https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/

This is all just my opinion - YMMV.

You could merge the subqueries using this model:

SELECT bool_or(B.tags&1<>0) as "has_children_tag_1",
       bool_or(B.tags&2<>0) as "has_children_tag_2",
       bool_or(B.tags&4<>0) as "has_children_tag_3",
       bool_or(B.tags&8<>0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions]

bool_or is an aggregate function that evaluates to true if at least one value is true, so that should lead to the same results as the multiple count(*)>0 in the question, except as one single subquery.

To push these columns into an otherwise unrelated query, you may add the above subquery as a CTE in a WITH clause, and just put it in the FROM list. For instance, modifying your original query:

WITH flags AS
(SELECT bool_or(B.tags&1<>0) as "has_children_tag_1",
       bool_or(B.tags&2<>0) as "has_children_tag_2",
       bool_or(B.tags&4<>0) as "has_children_tag_3",
       bool_or(B.tags&8<>0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions] )
SELECT 
  id, 
  aaaa, 
  bbbb,
  tags,
  has_children_tag_1,
  has_children_tag_2,
  has_children_tag_3,
  has_children_tag_4
FROM flags,A
LEFT JOIN Z
    ON A.id = Z.id