NVarchar storage vs Varchar storage on SQL Server

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.

No wrong, `VARCHAR` stores only required no. of bytes based on data — Pரதீப், Oct 14 '16 at 10:20
nvarchar is use to store unicode language or symbols while varchar is use to store text format — Saeed ur Rehman, Oct 14 '16 at 10:21
http://stackoverflow.com/questions/176514/what-is-the-difference-between-char-nchar-varchar-and-nvarchar-in-sql-server — Deep, Oct 14 '16 at 10:21
http://sqlhints.com/2011/12/23/difference-between-varchar-and-nvarchar/ — Pரதீப், Oct 14 '16 at 10:23
I guess your collegue has `VARCHAR` & `CHAR` mixed up. `CHAR(100)` will always create a 100-size string value, even if your string is only 5 characters large. `VARCHAR` will not do that. That's what the VAR stands for (variable). — Jens, Oct 14 '16 at 11:31

score 32 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

Thanks very much, I had a feeling that was what I was reading, but it didn't quite click until now — Ben W, Oct 14 '16 at 15:34
Also, I suggest you to take a look at this question [link](http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar). I can say the answers given there, had taught me much about this datatypes and lead to further investigations. — gofr1, Oct 14 '16 at 16:04

score 10 · Answer 2 · edited Apr 13 '17 at 12:42

10

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

edited Apr 13 '17 at 12:42

Community

1
1

answered Oct 14 '16 at 10:19

Alec.

5,371
5
34
69

Concise and accurate. Perfect answer. – pim Oct 27 '17 at 20:45

score 7 · Answer 3 · answered Oct 14 '16 at 10:23

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.

NVarchar storage vs Varchar storage on SQL Server

3 Answers3