What is the N prefix in MSSQL all about?
Tags: sql, beginners, mssql, sqlserver
Published: 2021-12-07 06:17:22

I used to learn about SQL, especially MySQL. Then now my job requires me to understand Microsoft SQL Server (mssql). So I make these notes to guide me and others who want to transition to mssql. I usually summarized what I found and understood so far from browsing the internet.

My takeaways:

- It's declaring the string as `NCHAR`, `NVARCHAR`, or `NTEXT` data type, rather than `CHAR`, `VARCHAR`, or `TEXT`.

- This __N prefix__ denotes that subsequent string is in __Unicode__ (the N actually stands for National language character set).

- To quote from Microsoft:
> Without the N prefix, the string is converted to __the default code page of the database__ and __may not recognize certain characters__.

- The `NVARCHAR` column can store any Unicode data. The `VARCHAR` column is restricted to an 8-bit.

- By using `NVARCHAR` rather than `VARCHAR`, we can __avoid doing encoding conversions every time we read from or write to the database__. Some people think that `VARCHAR` should be used because it takes up less space. Codepage incompatibilities are pain and Unicode is the cure for codepage problems.

- The `VARCHAR` data type represents the __non-Unicode__ variable-length string data type. We can store letters, numbers, and special characters in it.

> __N__ represents __string size in bytes__ (_not a value representing the number of characters in a string_).
It stores a __maximum of 8000 Non-Unicode characters__.
It takes __1 byte per character__. If we do not _explicitly specify the value for N_, it takes __1-byte storage__.
```SQL
-- DECLARE @var AS VARCHAR(N) = 'text';
DECLARE @text AS VARCHAR(100) = 'this VARCHAR yields 33 characters';
SELECT
    @text AS Output,
    DATALENGTH(@text) AS Length;
```
The query above defines `VARCHAR` data type with 100 bytes of data.
![VARCHAR DDL-TSQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/diq02o6a5hqfs9soxyhd.png)
It returns the _string length_ as 33 because of 1 byte per character, including a space character.
![VARCHAR query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hju0f98tz92y4t091zpw.png)

- The `NVARCHAR` data type is for the __Unicode__ variable-length character data type. We can store __both non-Unicode and Unicode characters (Japanese Kanji, Korean Hangul, etc.)__.

> __N__ represents __string size in bytes__ (_not a value representing the number of characters in a string_).
It stores a __maximum of 4000 Unicode and Non-Unicode characters__.
It takes __2 byte per character__. If we do not _explicitly specify the value for N_, it takes __2-byte storage__.
```SQL
-- DECLARE @var AS NVARCHAR(N) = N'text';
DECLARE @text AS NVARCHAR(100) = N'this NVARCHAR yields 34 characters';
SELECT
    @text AS Output,
    DATALENGTH(@text) AS Length;
```
The query above defines `NVARCHAR` data type with 100 bytes of data.
![NVARCHAR DDL-TSQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3e5oxljjr154r9u0ngaj.png)
It returns the _string length_ of 68 because `NVARCHAR` takes 2 bytes per character.
![NVARCHAR query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/99ylbikwm5on6l953hiu.png)
 
- Storing example:
```SQL
CREATE TABLE UserComments
(
    ID int IDENTITY (1,1),
    [Language] VARCHAR(50),
    [Comment] VARCHAR(200),
    [NewComment] NVARCHAR(200)
);

INSERT INTO UserComments ([Language],[Comment],[NewComment]) 
VALUES ('English','Hello World', N'Hello World');
INSERT INTO UserComments ([Language],[Comment],[NewComment]) 
VALUES ('Japanese','こんにちは世界', N'こんにちは世界');
INSERT INTO UserComments ([Language],[Comment],[NewComment]) 
VALUES ('Hindi','नमस्ते दुनिया', N'नमस्ते दुनिया');

SELECT * FROM UserComments;
```
![Storing Error](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qydvu07aidhn9o5lcywj.png)
 
References:

[1] [the-meaning-of-the-prefix-n](https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements-and-when-should-i-use-it#10025043)
[2] [what-is-the-difference-between-varchar-and-nvarchar](https://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar)
[3] [varchar-vs-nvarchar-data-types-in-sql-server](https://codingsight.com/varchar-vs-nvarchar-data-types-in-sql-server/)

[Let's be friend & support me👋](https://beacons.ai/ranggakd)