How to insert Unicode character in MSSQL (NVARCHAR Data Type)
For Inerting Unicode in ms sql you need to use NVARCHAR() datatype
Here we know more about NVARCHAR()
What is varchar in SQL?
As the name suggests, varchar means character data that is varying. Also known as Variable Character, it is an indeterminate length string data type. It can hold numbers, letters and special characters. Microsoft SQL Server 2008 (and above) can store up to 8000 characters as the maximum length of the string using varchar data type. SQL varchar usually holds 1 byte per character and 2 more bytes for the length information. It is recommended to use varchar as the data type when columns have variable length and the actual data is way less than the given capacity. Let’s switch to SSMS and see how varchar works.
The following example creates three variables (name, gender and age) with varchar as the data type and different values being assigned to them. As evident from the result sets shown below, by default, the string length of the SQL varchar columns is 1 and it returns only the first value of the variables(rest of the string being truncated) when no string length is passed for the varchar data type. Function len() is used to determine the number of characters stored in the varchar column.
When to use what?
If your column will store a fixed-length Unicode characters like French, Arabic and so on characters then go for NCHAR. If the data stored in a column is Unicode and can vary in length, then go for NVARCHAR. Querying to NCHAR or NVARCHAR is a bit slower then CHAR or VARCHAR. So don't go for NCHAR or NVARCHAR to store non-Unicode characters even though this data type supports that.
NVARCHAR Data Type
Use the SQL Server NVARCHAR data type to define columns, variables, and parameters variable length characters. NVARCHAR types are variable in length. They take up more memory than the characters stored. This differs from the CHAR type, which always occupies the full amount defined.
They store up to 4000 characters with each character taking two bytes and are is well suited for storing extended character set data, such as Kanji.
Generally speaking, If you’re writing an application supporting multiple languages, then use NVARCHAR over VARCHAR.
Defining NVARCHAR types
Here, we use NVARCHAR to define a Person table with FirstName as 20 in length, and LastName 40 in length:
About the NVARCHAR data type:
- It is a variable-length data type
- Used to store Unicode characters
- Occupies 2 bytes of space for each character
create database kodingnotes use kodingnotes create table langtest( fname NVARCHAR(200), lname NVARCHAR(200) ) insert into langtest (fname, lname) values('सूरज','विश्वकर्मा')-- inserting without profix N insert into langtest (fname, lname) values(N'सूरज',N'विश्वकर्मा')-- inserting with profix N select * from langtest