If SQL Server USER_NAME() Is A sysname, Why Does It Return NVARCHAR(256)?

Question

I'm working with a column I want to hold the value of the USER_NAME() function in SQL Server 2008 R2. In BOL, that function is documented as having a return type of NVARCHAR(256).

However, in one of the examples, they do this:

SELECT name FROM sysusers WHERE name = USER_NAME(1)

and the sysusers.name column has type sysname, which is (as I understand it) effectively an NVARCHAR(128) NOT NULL. This inconsistency is further solidified in the documentation for CURRENT_USER, which says "This function is equivalent to USER_NAME()", but indicates its type is sysname (of course, it then goes on to have an example where it uses CURRENT_USER as a default for a VARCHAR(30) column...)

What gives? Is the BOL wrong about the type of USER_NAME()? And is it safe for me to use NVARCHAR(128)?

Answer 1

Well, let's take a look:

SELECT name, system_type_id, user_type_id, max_length
  FROM sys.types WHERE name IN (N'sysname', N'nvarchar');

Results:

name      system_type_id  user_type_id  max_length
--------  --------------  ------------  ----------
nvarchar  231             231           8000
sysname   231             256           256

sysname is just an alias type for nvarchar with a defined number of characters of 128 (not 256). The reason sys.types says 256 is that nvarchar has 2 bytes per character - so that's 128 characters x 2 bytes = 256 bytes. They do not mean the same thing by "length" that we usually do.

There is no answer to the reason "why" BOL says that - Books Online is just wrong, that's all. Proof:

SELECT x = USER_NAME() INTO #foo;
EXEC tempdb..sp_help '#foo';

Partial results:

Column_name  Type      Length
-----------  --------  ------
x            nvarchar  256

-----------------------^^^
---- again, this is 128 characters

And yes, you should be safe using NVARCHAR(128), but does it cost you anything extra to match the documentation, just in case? Also stop looking at deprecated system tables like sysusers for guidance on data type choices and corroborating the documentation. While sys.database_principals and sys.server_principals also use sysname in this case, they're a much more reliable place to check how SQL Server works today, unless you really are investigating how SQL Server worked 13+ years ago.