Why does SQL Server give me a column twice the size I requested?

Question

After executing a CREATE TABLE for a temporary table I was verifying that the size of the field fits what I need to use.

To my surprise, SQL Server (Azure SQL) is reporting that the table now has double the size. Why is this?

This is what I executed, in order:

CREATE TABLE #A ( Name NVARCHAR(500) not null ) 
EXEC tempdb..sp_help '#A'

Answer 1

An NVARCHAR column in SQL Server always stores every character with 2 bytes.

So if you're asking for 500 characters (at 2 bytes each), obviously this results in column size of 1000 bytes.

That's been like this in SQL Server forever - this isn't new or Azure specific.