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?

It appears that I could store up to 4000 characters according to Microsoft Technet. Windows Dev Centre at MSDN seems to suggest that 260 characters will be sufficient.


If the path is being stored in a SQL_VARIANT field, then you might as well CAST / CONVERT to 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(500), or NVARCHAR(1000), or something like that.

  • Curious, can you explain how to exceed that 260 character default? – FilamentUnities Jul 6 '15 at 20:55
  • @Skagalak Yes. As explained in the MSDN documentation linked in the question, you would use the \\?\ prefix. Examples taken from that same page are: "\\?\D:\very long path" and "\\?\UNC\server\share". – Solomon Rutzky Jul 6 '15 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.