SQL SERVER – Measuring the Length of VARCHAR and NVARCHAR Columns with COL_LENGTH

I love my job because I get to help people learn new technology as well as help them clear their confusion about the older technology. Recently during SQL Server Performance Tuning Practical Workshop, I was asked a very simple but interesting question by an attendee about NVARCHAR column.

The question is – Is it true that a column with NVARCHAR datatype occupies twice the space than VARCHAR datatype?

The answer is – Yes, it does.

Let us do a simple test with the help of the function COL_LENGTH. This function returns the actual size of the table column in bytes. It gives us a small integer value as output. This function returns the actual storage bytes for that column.

USE TempDB
GO
CREATE TABLE TestTable (Col1 VARCHAR(50),
Col2 NVARCHAR(50));
SELECT COL_LENGTH('TestTable','Col1') Col1_Length,
COL_LENGTH('TestTable','Col2') Col2_Length;
DROP TABLE TestTable;

In the script above I have two columns with the exact the same length, however, one column is VARCHAR and the other one is NVARCHAR.

Now let us see the result.

You can clearly see from the example above that actual column length of the NVARCHAR is double of a length of VARCHAR column. N stands for National Language Character Set in NVARCHAR and is used to specify a Unicode string. VARCHAR is stored as regular 8-bit data. Whereas NVARCHAR strings are stored in the database as UTF-16 (16 bits or two bytes per character).

Reference: Pinal Dave (https://blog.sqlauthority.com)