CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduresnvarchar
dance2die
dance2die

Reputation: 36955

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

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

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

Upvotes: 1

Views: 1817

Answers (2)

gbn
gbn

Reputation: 432551

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'₠'

Upvotes: 5

Andomar
Andomar

Reputation: 238256

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.

Upvotes: 5

Related Questions