N prefix before string in Transact-SQL query

Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this

SELECT * FROM a_table WHERE a_field LIKE '%а_pattern%'

until I change pattern to N'%а_pattern%'. I never had to add this prefix in the past, so I am curious. a_field is defined as nvarchar(255), but I think the reason is something else.

The following articles have some good information on the question. The short answer is just that there's a type mismatch between the unicode column and non-unicode string literal you're using. From the KB article, it looks like omitting the N prefix might still work in some cases, but it would depend on the code page and collation settings of the database. That might explain the change in behavior, if you were previously having success with the no-prefix method.

https://support.microsoft.com/en-us/kb/239530

StackExchange Database Administrators Q#12475, answer score: 31