What does N’ stands for in a SQL script ? (the one used before characters in insert script)

I generated an sql script like this,

INSERT [dbo].[TableName] ([Sno], [Name], [EmployeeId], [ProjectId], [Experience]) 
VALUES (1, N'Dave', N'ESD157', N'FD080', 7)

I wonder whats that N’ exactly mean and whats its purpose here.

NOTE: By searching for the answer all i can get is that N’ is a prefix for National language standard and its for using unicode data. But honestly i am not able to get a clear idea about the exact operation of N’ here. I’d appreciate your help and please make it in more of an understandable way. Thanks in advance.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

N is used to specify a unicode string.

Here’s a good discussion: Why do some SQL strings have an ‘N’ prefix?

In your example N prefix is not required because ASCII characters (with value less than 128) map directly to unicode. However, if you wanted to insert a name that was not ASCII then the N prefix would be required.

INSERT [dbo].[TableName] ([Sno], [Name], [EmployeeId], [ProjectId], [Experience]) 
VALUES (1, N'Wāhi', 'ESD157', 'FD080', 7)

Method 2

The "N" prefix stands for National Language in the SQL-92 standard, and is used for representing unicode characters.

Any time you pass Unicode data to SQL Server you must prefix the Unicode string with N.

It is used when the type is from NVARCHAR, NCHAR or NTEXT.

For more info refer to this: Why do some SQL strings have an ‘N’ prefix?

Method 3

'abcd' is a literal for a [var]char string (or maybe text, but varchar(max) would be more common now) – occupying 4 bytes memory, and using whatever code-page the SQL server is configured for. N'abcd' is a literal for a n[var]char string (or maybe ntext, but nvarchar(max) would be preferable), occupying 8 bytes of memory using UTF-16. This allows for full international usage, and frankly n[var]char should probably be the default in most systems.

Method 4

This denotes that the subsequent string is in Unicode (the N actually stands for National language character set).

Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.

• SOURCE

Method 5

N is to specify that its a string type value.

[N]’tsql_string’

Is a constant string. tsql_string can be any nvarchar or varchar data
type. If the N is included, the string is interpreted as nvarchar
data type.

Method 6

each country has its own specific letters and symbols so a database set up for English US will not recognise the £ symbol which a English UK database would, the same goes for Spanish, French, German

Also other languages like Chinese, Japanese, Hebrew, Arabic don’t use any Latin characters.

so anyone trying to enter any data not contained in the local character set will fail or suffer data corruption, if you are using varchar, so if there is even the remotest possibility that your database will need to support more than one local character set then you have to use the nationalised language character set aka unicode aka NChar, which allows the character sets nationality to be recorded with the character. providing international text support

Likewise adding the N Prefix to a string instructs the database to include the Nation code as well as the character code

Method 7

DECLARE

   TYPE name_salary_rt IS RECORD (

      table_names     VARCHAR2 (1000),

      counts  NUMBER

   );

VSQL varchar2(2000);

   TYPE name_salary_aat IS TABLE OF name_salary_rt

      INDEX BY PLS_INTEGER;


   l_employees   name_salary_aat;

BEGIN

   EXECUTE IMMEDIATE

      q'[select table_name ,count(*) CountF

           from all_tab_columns  where rownum<100

         group by table_name]'

      BULK COLLECT INTO l_employees;


   FOR indx IN 1 .. l_employees.COUNT

   LOOP

VSQL:=VSQL||'  select '''||l_employees (indx).table_names||''','''|| l_employees (indx).counts ||''' from dual ';

if indx<l_employees.COUNT then

VSQL:=VSQL|| ' union all ';

else

VSQL:=VSQL||';';

end if;


  --    DBMS_OUTPUT.put_line (l_employees (indx).table_names||','|| l_employees (indx).counts);

    --    DBMS_OUTPUT.put_line (l_employees (indx).countf);

   END LOOP;

DBMS_OUTPUT.put_line (VSQL);

-- execute immediate  VSQL; 

END;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0