Using N before SQL staring

Introduction 

A lot of query that I find is why we use N before any SQL string. Here in this article I am trying to

answer this question with some example

What N Denotes

When we use N before any SQL string it denotes that the SQL sub string is an UNICODE. Here N means that the National Language character set.

If we use N with any substring that means that we are passing the value of a variable with data type like nVARCHAR,  nCHAR,  nTEXT.

We never pass the value of variable with N prefix that have data type CHAR, VARCHAR, TEXT.

Take a Simple Example

Try this sql

Exec sp_executesql 'SELECT ''Joyadeep'' As NAME'

It gives an error

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Here the sp_executesql expect an UNICODE staring.

Now try this

Exec sp_executesql N'SELECT ''Joydeep'' As NAME'

NAME

--------

Joydeep

(1 row(s) affected)

We can use variable also

DECLARE @SQLSTRING nVARCHAR(max);

SET @SQLSTRING = N'SELECT ''Joydeep'' AS NAME';

Exec sp_executesql @SQLSTRING;

NAME

-------

Joydeep

(1 row(s) affected)

Hope you like it.