Search code examples
sqlsql-serversql-variant

What is the best way to define a column in SQL server that may contain either an integer or a string?

I have a situation where two objects of the same type have parents of different types. The following pseudo code explains the situation the best:

TypeA a1, a2;
TypeB b;
TypeC c;
a1.Parent = b;
a2.Parent = c;

To complicate things even further TypeB and TypeC may have primary keys of different types, for instance, the following assertion may be true:

Assert(b.Id is string && c.Id is int);

My question is what is the best way to define this parent-child relationship in SQL Server? The only solution I can think of is to define that TypeA table has two columns - ParentId and ParentType, where:

  • ParentId is sql_variant - to be able to hold both numbers and strings
  • ParentType is string - to keep the assembly qualified name of the parent type.

However, when I defined a user data type based on sql_variant, it specified the field size as fixed 8016 bytes, which seems to be way to much.

There have to be a better way. Anyone? Thanks.

Solution

  • If NEITHER column will EVER be involved in any mathematical operations, make them CHAR() or VARCHAR(), as you will be dealing with a sequence of characters, not numbers. '1' is just as valid in that case as 'A'.