1. Log in to the Azure portal at https://portal.azure.com ➢ navigate to the Azure Synapse Analytics workspace you created in Exercise 3.3 ➢ on the Overview blade, click the Open link in the Open Synapse Studio tile ➢ navigate to the Manage hub ➢ start your dedicated SQL pool ➢ navigate to the Develop hub ➢ create a new SQL script ➢ and then execute the following SQL statement:
   SELECT DATABASEPROPERTYEX(DB_NAME(), ‘Collation’) AS Collation

The output is SQL_Latin1_General_CP1_CI_AS, which is the default (refer to Figure 3.28).

2. Execute the following SQL syntax:
   CREATE TABLE [dbo].[ENCODE] (
   [ENCODE_ID] INT NOT NULL,
   [ENCODE] VARCHAR (1) NOT NULL)

GO

INSERT INTO [dbo].[ENCODE] ([ENCODE_ID], [ENCODE]) VALUES (1, ‘殽’)
INSERT INTO [dbo].[ENCODE] ([ENCODE_ID], [ENCODE]) VALUES (2, ‘Ž’)
INSERT INTO [dbo].[ENCODE] ([ENCODE_ID], [ENCODE]) VALUES (3, ‘ß’)
INSERT INTO [dbo].[ENCODE] ([ENCODE_ID], [ENCODE]) VALUES (4, ‘€’)
INSERT INTO [dbo].[ENCODE] ([ENCODE_ID], [ENCODE]) VALUES (5, ‘陽’)

3. Execute the following SQL statement. The output is similar to Figure 5.25.
   SELECT * FROM [dbo].[ENCODE] ORDER BY [ENCODE_ID]

FIGURE 5.25 Encoding and decoding data—VARCHAR

4. Execute the following SQL statement:
   CREATE TABLE [dbo].[ENCODEN] (
   [ENCODE_ID INT NOT NULL,
   [ENCODE] NVARCHAR (1) NOT NULL)

INSERT INTO [dbo].[ENCODEN] ([ENCODE_ID], [ENCODE]) VALUES (1, N’殽’)
INSERT INTO [dbo].[ENCODEN] ([ENCODE_ID], [ENCODE]) VALUES (2, ‘Ž’)
INSERT INTO [dbo].[ENCODEN] ([ENCODE_ID], [ENCODE]) VALUES (3, ‘ß’)
INSERT INTO [dbo].[ENCODEN] ([ENCODE_ID], [ENCODE]) VALUES (4, ‘€’)
INSERT INTO [dbo].[ENCODEN] ([ENCODE_ID], [ENCODE]) VALUES (5, N’陽’)

5. Execute the following SQL statement. The output is similar to Figure 5.26.
   SELECT * FROM [dbo].[ENCODEN] ORDER BY [ENCODE_ID]

FIGURE 5.26 Encoding and decoding data—NVARCHAR

6. Rename (for example, Ch05Ex09) and commit the SQL script to your integrated source code repository, as configured in Exercise 3.6, and then pause (shut down) the dedicated SQL pool. You can find the SQL script for this exercise in the Chapter05/Ch05Ex09 directory on GitHub.

The first action in Exercise 5.9 was to create, populate, and select the [dbo].[ENCODE] table. Notice that the data type for [ENCODE] was VARCHAR. When you store a character that does not fit into a single byte, then it cannot be correctly decoded. You can see this in Figure 5.25, when running the SELECT statement that results in a question mark (?) being rendered instead of the expected value. Using NVARCHAR as the data type for column [ENCODE] on the [dbo].[ENCODEN] table means the data stored in the column can be two bytes. Using NVARCHAR in combination with the N prefix on the data contained in the INSERT statement results in the data being stored correctly. Therefore, when the SELECT is performed on that table, as shown in Figure 5.26, the characters are decoded as expected and desired.