{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# UTF-8 Functional Demo"
            ],
            "metadata": {
                "azdata_cell_guid": "142ed68f-c3b7-4930-b1d7-061da601ef9b"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Make sure databases don't exist"
            ],
            "metadata": {
                "azdata_cell_guid": "a0992b06-0c54-4560-84ed-3a1e84886333"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE master;\r\n",
                "GO\r\n",
                "\r\n",
                "DROP DATABASE IF EXISTS MyNonUtf8Database;\r\n",
                "\r\n",
                "DROP DATABASE IF EXISTS MyUtf8Database;\r\n",
                "\r\n",
                "DROP DATABASE IF EXISTS MyFormerlyUnicodeOnlyDatabase;\r\n",
                "\r\n",
                "DROP DATABASE IF EXISTS MaskingDatabase;"
            ],
            "metadata": {
                "azdata_cell_guid": "3e4cbdbd-e034-4f78-91cb-275803ecf042"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.129"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Create a database that is NOT collated with UTF-8.\r\n",
                "This demonstrates that you can insert Unicode data into VARCHAR columns collated with UTF-8.\r\n",
                "This demo used the N' syntax, as string literals are always collated in the collation of the currently active database.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f5f711f5-8de3-49cd-bcb4-65d5db761c1c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE DATABASE MyNonUtf8Database COLLATE SQL_Latin1_General_CP1_CI_AI;\r\n",
                "GO\r\n",
                "\r\n",
                "USE MyNonUtf8Database;\r\n",
                "GO\r\n",
                "\r\n",
                "CREATE TABLE MyUtf8Table (datakind VARCHAR(100), data VARCHAR(8000) COLLATE Latin1_General_100_CI_AS_SC_UTF8);\r\n",
                "GO\r\n",
                "\r\n",
                "INSERT INTO MyUtf8Table\r\n",
                "VALUES ('ASCII - 1 byte per character', N'Thequickbrownfoxjumpsoverthelazydog'), \r\n",
                "\t('Cyrillic - 2 bytes per character', N'Быстраякоричневаялисапрыгаетчерезленивуюсобаку'), \r\n",
                "\t('Far East - 3 bytes per character', N'敏捷的棕色狐狸跳过了懒狗'), \r\n",
                "\t('Emojis - 4 bytes per character', N'👶👦👧👨👩👴👵👨👩👨👩👨👩👨👩'), \r\n",
                "\t('Emojis with Variation Selector - 6 bytes per glyph', N'⚕️⚖️↔︎↕︎↖︎↗︎↘︎↙︎↩︎↪︎↔️↕️↖️↗️↘️↙️↩️↪️'), \r\n",
                "\t('Ashi with Supplementary Variation Selector - 7 bytes per glyph', N'芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄅芦󠄆芦󠄇芦󠄈芦󠄉芦󠄃芦󠄂芦󠄁芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄈芦󠄉');\r\n",
                "GO\r\n",
                "\r\n",
                "SELECT datakind, data\r\n",
                "FROM MyUtf8Table;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "6af5063f-c763-4fbd-8895-4d31985604f1"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(6 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(6 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.597"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 2,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "datakind"
                                    },
                                    {
                                        "name": "data"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "ASCII - 1 byte per character",
                                    "1": "Thequickbrownfoxjumpsoverthelazydog"
                                },
                                {
                                    "0": "Cyrillic - 2 bytes per character",
                                    "1": "Быстраякоричневаялисапрыгаетчерезленивуюсобаку"
                                },
                                {
                                    "0": "Far East - 3 bytes per character",
                                    "1": "敏捷的棕色狐狸跳过了懒狗"
                                },
                                {
                                    "0": "Emojis - 4 bytes per character",
                                    "1": "👶👦👧👨👩👴👵👨👩👨👩👨👩👨👩"
                                },
                                {
                                    "0": "Emojis with Variation Selector - 6 bytes per glyph",
                                    "1": "⚕️⚖️↔︎↕︎↖︎↗︎↘︎↙︎↩︎↪︎↔️↕️↖️↗️↘️↙️↩️↪️"
                                },
                                {
                                    "0": "Ashi with Supplementary Variation Selector - 7 bytes per glyph",
                                    "1": "芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄅芦󠄆芦󠄇芦󠄈芦󠄉芦󠄃芦󠄂芦󠄁芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄈芦󠄉"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>datakind</th><th>data</th></tr><tr><td>ASCII - 1 byte per character</td><td>Thequickbrownfoxjumpsoverthelazydog</td></tr><tr><td>Cyrillic - 2 bytes per character</td><td>Быстраякоричневаялисапрыгаетчерезленивуюсобаку</td></tr><tr><td>Far East - 3 bytes per character</td><td>敏捷的棕色狐狸跳过了懒狗</td></tr><tr><td>Emojis - 4 bytes per character</td><td>👶👦👧👨👩👴👵👨👩👨👩👨👩👨👩</td></tr><tr><td>Emojis with Variation Selector - 6 bytes per glyph</td><td>⚕️⚖️↔︎↕︎↖︎↗︎↘︎↙︎↩︎↪︎↔️↕️↖️↗️↘️↙️↩️↪️</td></tr><tr><td>Ashi with Supplementary Variation Selector - 7 bytes per glyph</td><td>芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄅芦󠄆芦󠄇芦󠄈芦󠄉芦󠄃芦󠄂芦󠄁芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄈芦󠄉</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Create a database collated with UTF-8.\r\n",
                "This is to demonstrate that now string literals can be used without N' as string literals are collated with the database collation, and can hold any characters."
            ],
            "metadata": {
                "azdata_cell_guid": "d31b7957-5b5c-44dc-b4fb-ec7da338add2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE DATABASE MyUtf8Database COLLATE Lithuanian_100_CS_AI_WS_SC_UTF8;\r\n",
                "GO\r\n",
                "\r\n",
                "USE MyUtf8Database;\r\n",
                "GO\r\n",
                "\r\n",
                "CREATE TABLE MyTableWithInheritedCollation (datakind VARCHAR(100), data VARCHAR(8000));\r\n",
                "GO\r\n",
                "\r\n",
                "INSERT INTO MyTableWithInheritedCollation\r\n",
                "VALUES ('ASCII - 1 byte per character', 'Thequickbrownfoxjumpsoverthelazydog'), \r\n",
                "\t('Cyrillic - 2 bytes per character', 'Быстраякоричневаялисапрыгаетчерезленивуюсобаку'), \r\n",
                "\t('Far East - 3 bytes per character', '敏捷的棕色狐狸跳过了懒狗'), \r\n",
                "\t('Emojis - 4 bytes per character', '👶👦👧👨👩👴👵👨👩👨👩👨👩👨👩'), \r\n",
                "\t('Emojis with Variation Selector - 6 bytes per glyph', '⚕️⚖️↔︎↕︎↖︎↗︎↘︎↙︎↩︎↪︎↔️↕️↖️↗️↘️↙️↩️↪️'), \r\n",
                "\t('Ashi with Supplementary Variation Selector - 7 bytes per glyph', '芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄅芦󠄆芦󠄇芦󠄈芦󠄉芦󠄃芦󠄂芦󠄁芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄈芦󠄉');\r\n",
                "GO\r\n",
                "\r\n",
                "SELECT datakind, data\r\n",
                "FROM MyTableWithInheritedCollation;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "88220dd6-fa2d-416f-91be-7db703f71e13"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(6 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(6 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.424"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 1,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "datakind"
                                    },
                                    {
                                        "name": "data"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "ASCII - 1 byte per character",
                                    "1": "Thequickbrownfoxjumpsoverthelazydog"
                                },
                                {
                                    "0": "Cyrillic - 2 bytes per character",
                                    "1": "Быстраякоричневаялисапрыгаетчерезленивуюсобаку"
                                },
                                {
                                    "0": "Far East - 3 bytes per character",
                                    "1": "敏捷的棕色狐狸跳过了懒狗"
                                },
                                {
                                    "0": "Emojis - 4 bytes per character",
                                    "1": "👶👦👧👨👩👴👵👨👩👨👩👨👩👨👩"
                                },
                                {
                                    "0": "Emojis with Variation Selector - 6 bytes per glyph",
                                    "1": "⚕️⚖️↔︎↕︎↖︎↗︎↘︎↙︎↩︎↪︎↔️↕️↖️↗️↘️↙️↩️↪️"
                                },
                                {
                                    "0": "Ashi with Supplementary Variation Selector - 7 bytes per glyph",
                                    "1": "芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄅芦󠄆芦󠄇芦󠄈芦󠄉芦󠄃芦󠄂芦󠄁芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄈芦󠄉"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>datakind</th><th>data</th></tr><tr><td>ASCII - 1 byte per character</td><td>Thequickbrownfoxjumpsoverthelazydog</td></tr><tr><td>Cyrillic - 2 bytes per character</td><td>Быстраякоричневаялисапрыгаетчерезленивуюсобаку</td></tr><tr><td>Far East - 3 bytes per character</td><td>敏捷的棕色狐狸跳过了懒狗</td></tr><tr><td>Emojis - 4 bytes per character</td><td>👶👦👧👨👩👴👵👨👩👨👩👨👩👨👩</td></tr><tr><td>Emojis with Variation Selector - 6 bytes per glyph</td><td>⚕️⚖️↔︎↕︎↖︎↗︎↘︎↙︎↩︎↪︎↔️↕️↖️↗️↘️↙️↩️↪️</td></tr><tr><td>Ashi with Supplementary Variation Selector - 7 bytes per glyph</td><td>芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄅芦󠄆芦󠄇芦󠄈芦󠄉芦󠄃芦󠄂芦󠄁芦󠄀芦󠄁芦󠄂芦󠄃芦󠄄芦󠄈芦󠄉</td></tr></table>"
                    }
                }
            ],
            "execution_count": 1
        }
    ]
}