sql server 2012 express do not understand Russian letters

Question

I have DB which is working with Russian text however when i run queries it shows me this. Database will used by Russians and it has to show Russian text properly!

Any ideas how to fix it? In the future it will located in Russia and work with Russian version SQL Server but right now I am working on English version SQL 2012 Express.

Here is the table and insert statement:

Create table Employee
(
 EmpID int not null IDENTITY (10, 1), 
 StrName nvarchar (25) not null, 
 Phone1 nvarchar (25) not null,
 Phone2 nvarchar (25)
 Primary Key (EmpID),
);
insert into  Employee  (LastName , FirstName,Phone1,Phone2)
  values ('Иванов','111 111 11111','111 111 1111');

Answer 1

Are you sure the data has been stored in the database correctly? How do you know?

Make sure that the column has a proper collation, that it is defined as nvarchar and that inserts of string literals are prefixed with N. For example, these are not the same:

INSERT dbo.table(column) SELECT 'foo';
INSERT dbo.table(column) SELECT N'foo';

As an example:

USE tempdb;
GO

CREATE TABLE dbo.foo
(
  ID INT PRIMARY KEY,
  bar NVARCHAR(32) COLLATE SQL_Ukrainian_CP1251_CI_AS
);

INSERT dbo.foo SELECT 1,'АБВГДЕЖЅZЗИІКЛ';
INSERT dbo.foo SELECT 2,N'АБВГДЕЖЅZЗИІКЛ';

SELECT ID, bar FROM dbo.foo;
GO
DROP TABLE dbo.foo;

Results:

ID    bar
----  --------------
1     ????????Z?????
2     АБВГДЕЖЅZЗИІКЛ

And to show how this affects your insert statement, your string is missing the N prefix:

SELECT
  CONVERT(NVARCHAR(32), 'Иванов'),
  CONVERT(NVARCHAR(32), N'Иванов');

Results:

------    ------
??????    Иванов

So, prefix your Unicode strings with N'a prefix' or lose data.

Answer 2

While Aaron Bertrand gave a good explanation why do you getting such a results, I'd say there's a way not to prefix all you strings with russian letters with 'N'.
As far as I know, you have just set your server collation properly. So if you set your collation, for example, like Cyrillic_General_CI_AS, server could treat varchar with russian letters properly:

select
    'español', '平成年月日', 'иван',
    serverproperty('collation')

results:

espanol ?????   иван    Cyrillic_General_CI_AS

As you see, spanish and Chinese strings are not treated properly while russian strings are. So you can insert data into nvarchar columns without prefixing strings with 'N'

That said, I'm using nvarchar data type in our database as default strings, nvarchar parameters in stored procedures. I very rarely use russian strings in code (only when I want to test something), and I've never used N'string' syntax.

While having correct default collation could be handy, there's problem with this solution - it's not easy to change default collation on installed SQL Server, so you have to be careful when installing SQL Server instance and choose collation properly.