If SQL Server USER_NAME () is sysname, why does it return NVARCHAR (256)?

Question

I am working with a column, I want to save the value of the USER_NAME() function in SQL Server 2008 R2. In BOL, this function is documented as a return type of NVARCHAR(256) .

However, in one example, they do this:

 SELECT name FROM sysusers WHERE name = USER_NAME(1) 

and the sysusers.name column is of type sysname , which (as I understand it) is effectively NVARCHAR(128) NOT NULL . This inconsistency is further hardened in the documentation for CURRENT_USER , which says: "This function is equivalent to USER_NAME() ", but indicates that it is of type sysname (of course, in this case we will give an example of using CURRENT_USER by default for the VARCHAR(30) column VARCHAR(30) ...)

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

Answer 1

Ok, let's see:

 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 specific number of characters out of 128 (not 256). The reason sys.types says 256 is because nvarchar has 2 bytes per character - so 128 characters x 2 bytes = 256 bytes. They do not mean the same length that we usually do.

There is no answer to the reason why BOL says that Books Online is simply 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 is it worth something extra to match the documentation, just in case? Also stop browsing obsolete system tables, such as sysusers , for guidance on selecting data types and confirming documentation. Although sys.database_principals and sys.server_principals also use sysname , in this case they are much more reliable in checking how SQL Server works today, unless you really learn how SQL Server worked 13 years ago.