What is the best choice of field type to cast to when storing a UNC path in a
sql_variant in Microsoft SQL Server 2008?
If the path is being stored in a
SQL_VARIANT field, then you might as well
NVARCHAR(4000) on the way out as that is the most that can be stored by a
SQL_VARIANT. While the typical upper limit for a path is 260 characters, it is possible to exceed that and go up to 32,767 characters (even if that is unlikely to happen in practice). Since this scenario concerns storing the value in a local variable or returning in a result set (as opposed to how it is stored in the table), there is no reason to use less than 4000, and no use in specifying
MAX since the value stored in the table couldn't hold that data anyway.
However, why would a path be stored as a
SQL_VARIANT in the first place? Even if the total length never exceeds even the default 260 characters, it would still be best to store as
NVARCHAR(1000), or something like that.