0

I have a SQL Server database which includes a Users table with the following columns:

  • UserId: nvarchar(128)
  • email: nvarchar(256)
  • ...

I want to add a users hierarchy to my system. This means, one user will be the "administrator". Other will be the children of the administrator or other users.

So, I thought of adding a new column:

  • ParentUser: hierarchyid

to the Users table. The administrator user will be the top level value of hierarchy. Other will be placed below it. For example:

\b2566df8-9129-405b-a0e6-a56da3b49c5a\79336162-022e-495c-8655-64de1abe556c
\admin-id\normal-user-id

My problem is that hierarchyid column doesn't accept nvarchar(128) (my user-id) as valid value. So, my administator user cannot get the value "\b2566df8-9129-405b-a0e6-a56da3b49c5a" in his [ParentColumn].

The error I get when I use SQL Server Management Studio:

The error

Tried those values:

  • b2566df8-9129-405b-a0e6-a56da3b49c5a
  • /b2566df8-9129-405b-a0e6-a56da3b49c5a

One possible solution is to create another unique id for each user. But, now create a userid which will be accepted by hierarchyid column. But, I want to avoid this solution and use the original user id.

How to solve this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
No1Lives4Ever
  • 6,430
  • 19
  • 77
  • 140
  • Does the datatype have to be hierarchyid? Why not use e.g. nvarchar(128) as your datatype? – ssn Aug 10 '17 at 12:11
  • You mean to use FK to UserId (same table)? If yes, then I'll give up all the recorsive functions that SQL gives. For example, In case of "\admin\manager\user" it will be much harder to get the associated admin of specific user... – No1Lives4Ever Aug 10 '17 at 12:14
  • 1
    I have never used hierarchyid before so I don't know of its advantages/disadvantages. But I guess with nvarchar you could fairly easily retrieve data again using a recursive cte and self-joins? – ssn Aug 10 '17 at 13:01
  • 1
    The constituent members of a hierarchyid "path" are numerics. For example '/1/2/' would be a valid hierarchyid. The way I've used it in the past is with an identity column identifying the individual members and then string concatenating all of the IDs with slashes. How are your UserIDs being generated today? – Ben Thul Aug 10 '17 at 14:47
  • Today we are generating user id as GUID. This is not something that can be converted to number (128bit). Therefore, the last option is to add another column as you said. – No1Lives4Ever Aug 10 '17 at 16:04

0 Answers0