Nulls vs Guid.Empty in SQL Hierarchical Table

Question

Scenerio:

I have a folder table with hierarchical records. The ID of the row is of type GUID. A ParentID on the tables stores the parent folder ID:

• ID (Guid)
• FolderName (varchar)
• ParentID (Guid)

Question:

What is the best practice for inserting a top-level folder? Should I make the parentID column null, or create a "top level" record (ID = Guid.Empty) and use that ID for top-level folders?

Answer 1

guid.empty is not a SQL value. Make ParentID null for the root level.

You would have to hardcode {00000000-0000-0000-0000-000000000000} to select the root value, whereas SQL natively knows about null:

select * from mytbl where parentid is null

Also, look into SQL Server 2008's new hierarchyid data type if you're building a Parent-Child table. It can save you a bit from having to build out the recursive CTE to traverse your tree.

Answer 2

Definitely don't make the column nullable. In the past I've made the top-level node point to itself as its parent, but an empty guid would be more or less the same as far as requiring a special check.

Answer 3

I would either create a static guid that would represent the root, or assign the ID to the ParentID as a self-reference to signify the folder contains itself.

Answer 4

Using null for special value is a bad practice and an anti-pattern.

Null already has meaning in database which is no value. All database systems are programmed for this (via SQL standard which never evaluates operations with null to true (except for special case "is [not] null").

So choose the special value that is the best for your case and use it.