NVarchar storage vs Varchar storage on SQL Server [duplicate]

Question

I have just been informed by a colleague that if you assign a length to a VARCHAR field in the database, eg VARCHAR(1000), then the database will force the database to hold 1000 bytes of space for each row, whether or not it is needed.

However he claims that an NVARCHAR(1000) field will simply hold the number of bytes required.

I have researched this as best I can, both here and around the web, but I cannot find anything to support this, nor does the SQL Server Help documentation mention this anywhere that I can find.

Answer 1

From MSDN:

varchar [ ( n | max ) ]

Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes.

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes.

And:

Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation which counts against the 8,060 byte row limit during a sort operation.

To check the size of stored data you can use DATALENGTH

Returns the number of bytes used to represent any expression.

You can do this simple test to check length and bites used to store varchar and nvarchar data:

CREATE TABLE #temp (
    id int IDENTITY(1,1) NOT NULL,
    vColumn varchar(1000) NULL,
    nvColumn nvarchar(1000) NULL
)


INSERT INTO #temp VALUES
('something',N'something'),
('more','more'),
('',''),
('Autem excepturi omnis neque doloribus dolore. Saepe deleniti optio non ratione nesciunt esse ducimus. Nulla quia voluptatem aliquid omnis ex deleniti. Rerum minima unde officia est voluptatum esse dolorem aut. Sed est voluptas laboriosam. Dolore sint necessitatibus architecto sit eius ut molestiae eum.Sit sunt in dolores nihil. Numquam et nihil quo vel iusto. Commodi rem sint magnam qui perspiciatis. Accusantium sit adipisci neque. Nihil itaque quam quia. Est sapiente ut perferendis quia rerum. Quibusdam non et perferendis vel maxime est voluptates. Dolor deserunt qui iusto est. Et deleniti quia hic dicta ut quia. Dolore ducimus aspernatur quam nostrum commodi. Sequi cupiditate ipsa tempore. Velit dolorem eaque aspernatur sed numquam placeat excepturi odit. Accusantium officia sequi voluptas facilis ut eum necessitatibus id. Libero qui rerum et amet veniam architecto. Voluptatibus ad labore expedita. Mollitia ut soluta accusantium qui nam sunt nostrum. Aliquid aut voluptas accusamus v.',
N'Autem excepturi omnis neque doloribus dolore. Saepe deleniti optio non ratione nesciunt esse ducimus. Nulla quia voluptatem aliquid omnis ex deleniti. Rerum minima unde officia est voluptatum esse dolorem aut. Sed est voluptas laboriosam. Dolore sint necessitatibus architecto sit eius ut molestiae eum.Sit sunt in dolores nihil. Numquam et nihil quo vel iusto. Commodi rem sint magnam qui perspiciatis. Accusantium sit adipisci neque. Nihil itaque quam quia. Est sapiente ut perferendis quia rerum. Quibusdam non et perferendis vel maxime est voluptates. Dolor deserunt qui iusto est. Et deleniti quia hic dicta ut quia. Dolore ducimus aspernatur quam nostrum commodi. Sequi cupiditate ipsa tempore. Velit dolorem eaque aspernatur sed numquam placeat excepturi odit. Accusantium officia sequi voluptas facilis ut eum necessitatibus id. Libero qui rerum et amet veniam architecto. Voluptatibus ad labore expedita. Mollitia ut soluta accusantium qui nam sunt nostrum. Aliquid aut voluptas accusamus v.')

SELECT  id,
        vColumn,
        LEN(vColumn) vLen,
        DATALENGTH( vColumn) as vLength,
        nvColumn,
        LEN(nvColumn) nvLen,
        DATALENGTH( nvColumn) as nvLength
FROM #temp

DROP TABLE #temp

Will output:

id  vColumn             vLen    vLength     nvColumn            nvLen   nvLength
1   something           9       9           something           9       18
2   more                4       4           more                4       8
3                       0       0                               0       0
4   Autem excepturis... 1000    1000        Autem excepturi...  1000    2000

Basically n in above statements determine LENGTH (number of characters) of the string.

DATALENGTH for nvarchar is twice much than varchar.

Answer 2

Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the choice. Varchar stores ASCII data and should be your data type of choice for normal use. Regarding memory usage, nvarchar uses 2 bytes per character, whereas varchar uses 1.

Source - https://dba.stackexchange.com/questions/36081/write-differences-between-varchar-and-nvarchar

Answer 3

It sounds like there is confusion between CHAR and VARCHAR.

CHAR/NCHAR are fixed length, so they will always hold the number of bytes as defined. e.g. if you create a table with a field of type CHAR(10), each row will hold 10 bytes regardless of whether you enter a value that contains fewer characters or not. The remainder of the length gets padded out with blank spaces.

VARCHAR/NVARCHAR are variable length, and the amount of bytes used will vary dependent on the value the column it contains. e.g. if you create a table with a field of type VARCHAR(10), each row could be a different size, dependent on the length of the value held in that column.