Greek characters into varchar column and with Cyrillic colation

I have SQL Server 2019. If I have a table with Cyrillic collation and a varchar column I can't find a way to insert Greek letters:

CREATE TABLE dbo.tmp (
    id int NOT NULL
    , name varchar(75) collate Cyrillic_General_CI_AS
)

INSERT INTO dbo.tmp (id, name)
VALUES (1, N'Εργ Μας');

SELECT *
FROM dbo.tmp;

Then the result is

??? ???

I need to know is there a solution to make the insert while keeping the varchar type and the Cyrillic collation. Or these parameters are incompatible with Greek letters?

I am affraid there's no other solution than change of the DB collation to Greek_CI_AS if you want to store Greek characters.

Also you can change it to one of UTF collations. Change it to LATIN1_GENERAL_100_CI_AS_SC to LATIN1_GENERAL_100_CI_AS_SC_UTF8. This one should support both of the character sets.

Here you can find a good article how change your collation: https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-ver16

You can use in-place conversion or copy.

In-place conversion.

-- NVARCHAR column is encoded in UTF-16 because a supplementary character enabled collation is used
CREATE TABLE dbo.MyTable (CharCol NVARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC);

-- VARCHAR column is encoded the Latin code page and therefore is not Unicode capable
CREATE TABLE dbo.MyTable (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI);

To convert the column in-place to use UTF-8, run an ALTER COLUMN statement that sets the required data type and a UTF-8 enabled collation:

ALTER TABLE dbo.MyTable 
ALTER COLUMN CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8

This method is easy to implement, however it's a possibly blocking operation which may become an issue for large tables and busy applications.

Copy and replace. Consider one of the existing tables defined below:

-- NVARCHAR column is encoded in UTF-16 because a supplementary character enabled collation is used
CREATE TABLE dbo.MyTable (CharCol NVARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC);
GO

-- VARCHAR column is encoded using the Latin code page and therefore is not Unicode capable
CREATE TABLE dbo.MyTable (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI);
GO

To convert the column to use UTF-8, copy the data to a new table where the target column is already the required data type and a UTF-8 enabled collation, and then replace the old table:

CREATE TABLE dbo.MyTableNew (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
GO
INSERT INTO dbo.MyTableNew 
SELECT * FROM dbo.MyTable;
GO
DROP TABLE dbo.MyTable;
GO
EXEC sp_rename 'dbo.MyTableNew', 'dbo.MyTable';
GO

This method is much faster than in-place conversion, but handling complex schemas with many dependencies (FKs, PKs, Triggers, DFs) and syncing the tail of the table (if the database is in use) requires more planning.