sysname is not just an alias for nvarchar(128)

I had noticed the datatype sysname in sys.types before but never thought much about it.  It seemed obvious that it would be used for the internals of SQL Server to put a limit on object lengths.  A default behaviour that I had not noticed from sys.tables is the is_nullable column has a value of 0 for this type.  Fast forward to today, I had built some table variables to capture roles and permissions and used sysname for all of my fields, even the nullable ones.  I'm lazy and didn't want my fingers to stray into the number row.  I ran my script and it exploded everywhere

Cannot insert the value NULL into column 'server_role', table '@PEOPLE'; column does not allow nulls. INSERT fails.

That didn't make sense, my table declaration approximated "DECLARE @PEOPLE(server_role sysname) TABLE"  By default, a column is nullable unless you explicitly state it's not nullable.  

CREATE TABLE really (blah sysname)
EXECUTE sp_help really

Column_name	Type	Computed	Length	Prec	Scale	Nullable
blah	sysname	no	256			no

Huh, by golly it's automatically not null because of the way the datatype is defined in sys.types. That's kind of cool.  Next logical thought of course is, what happens if I explicitly declare it as nullable, who wins?

CREATE TABLE really_really (blah sysname NULL)
EXECUTE sp_help really_really

Column_name	Type	Computed	Length	Prec	Scale	Nullable
blah	sysname	no	256			yes

Key take away:  Don't be lazy.  Explicitly declare your sizes and nullability lest it bite you in the britches.

References

http://technet.microsoft.com/en-us/library/ms191240.aspx