Add emoji / emoticon to SQL Server table

Question

I am trying to insert emoji / emoticons to a SQL Server database but it just stores ??? instead of the emoji / emoticons.

I am finding only help for SQL Server not MySQL.

I tried : link

but not finding answers even not able to set with :

ALTER TABLE mytable charset=utf8mb4, 
    MODIFY COLUMN textfield1 VARCHAR(255) CHARACTER SET utf8mb4,
    MODIFY COLUMN textfield2 VARCHAR(255) CHARACTER SET utf8mb4;

SQL Server does not recognize this command. This is only for Microsoft SQL Server not MySQL

Answer 1

Use NVARCHAR(size) datatype and prefix string literal with N:

CREATE TABLE #tab(col NVARCHAR(100));

INSERT INTO #tab(col) VALUES (N'👍 🖒 🖓 🖕 🗑 🛦 ⁉ 😎 😔 😇 😥 😴 😭');

SELECT *
FROM #tab;

db<>fiddle demo

Output:

╔═════════════════════════════════╗
║              col                ║
╠═════════════════════════════════╣
║ 👍 🖒 🖓 🖕 🗑 🛦 ⁉ 😎 😔 😇 😥 😴😭 ║
╚═════════════════════════════════╝

---

EDIT:

SQL Server 2019 and forward supports UTF-8 collation:

CREATE TABLE t(col VARCHAR(100) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
-- column's data type is VARCHAR!
-- collate could be set on column/database/instance level

INSERT INTO t(col) VALUES (N'☢️');

SELECT * FROM t;
-- col
-- ☢️

db<>fiddle demo - SQL Server 2019