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.

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

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)

SQL Column, SQL Datatype, SQL Function, SQL Scripts, SQL Server
Previous Post
SQL SERVER – How to Count a Particular Value Across All Columns?
Next Post
SQL SERVER – Cannot Shrink Log File 2 (SQLAuthorityDB_log) Because the Logical Log File Located at the End of the File is in Use

Related Posts

Leave a Reply