TSQL: Any benefits for explicitly specifying NVARCHAR in a string?

Question

When you add pass a new job_type to sys.sp_cdc_add_job @job_type,
(which is of type nvarchar(20))

You can pass the argument as either

• N'cleanup'
• cleanup

Are there any reasons or benefits to use the former syntax using N' to pass the argument to stored procedures?

Answer 1

Only if the string contains unicode characters

The string is implictly converted to nvarchar when the passed into the stored proc.

However, before SP execution, it's a literal varchar ("character string constant") without N prefix. So, if you Japanese names, you'll need "N" to make it a "unicode constant". See "constants" in BOL which explains more about, er, constants...

Edit: A test of this inspired by Andomar...

CREATE PROCEDURE dbo.ZanziBar
   @KungFoo nvarchar(1)
AS
SET NOCOUNT ON
SELECT @KungFoo
GO

EXEC dbo.ZanziBar '₠'

EXEC dbo.ZanziBar N'₠'

Answer 2

Most strings in Windows are unicode UCS-16. Now I didn't write SSMS, but I know SSMS talks to SQL Server using the TDS protocol. So the logical thing to expect is that SSMS converts '' strings to an 8-bit TDS string, while it can send N'' strings as a UCS-16 TDS string without conversion. Let's see what happens in a test:

select '₠', N'₠'
---- ----
?    ₠

(1 row(s) affected)

The ? is what's left of the Unicode-only character, it got lost in the UCS-16 to 8-bit translation.

Since SSMS does not know what type of string a stored procedure expects, I expect it to convert a '' string in a stored procedure call as well.

The performance penalty should be negligible, UCS-16 to UCS-8 and back is very fast.