SQL Server size difference for a column

Question

I have table in SQL Server say "Temp" and it has Addr1, Addr2, Addr3, Addr4 columns and some additional columns also there.

These Addr1, Addr2, Addr3 and Addr4 are nvarchar type. when I check the size of this column by object explorer. it shows all of them in nvarchar(100).

But when I check them using Alt + F1. It shows the details in Result Pane with the length as 200. screenshot is below.

why there is different? If I enter more than 100 characters, I'm getting truncation errors? seems like it taking only 100 characters.

can you please let me know what is the length value specifies ?

Thanks, Prakash.

Answer 1

Because the size listed in Object Explorer is number of characters and the size listed in the result of your query to sp_help is number of bytes.

VARCHAR values in SQL use 1 byte per character, whereas NVARCHAR values use 2 bytes per character. Both also need a 2 byte overhead - see below. So because you are looking at NVARCHAR columns, these need 200 (well actually 202) bytes to store 100 characters, where a VARCHAR would only require 100 (really 102).

References:

MSDN: char and varchar

The storage size is the actual length of the data entered + 2 bytes.

MSDN: nchar and nvarchar:

The storage size, in bytes, is two times the actual length of data entered + 2 bytes.

(emphasis mine)

MSDN: sp_help:

Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type.

    /------------------------------------------------------------------------\
    | Column name | Data type | Description                                  |
    |-------------+-----------+----------------------------------------------|
    |   Length    | smallint  | Physical length of the data type (in bytes). |
    \------------------------------------------------------------------------/