Getting-Started/getting-started.ipynb

{
    "cells": [
        {
            "attachments": {},
            "cell_type": "markdown",
            "metadata": {
                "azdata_cell_guid": "6681f7bf-3e96-4176-8351-4772aa825cee"
            },
            "source": [
                "# Getting Started\n",
                "\n",
                "You can create vectors by casting a numeric JSON array to a VECTOR type and then you can calculate distance between to vectors using `vector_distance`:"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 3,
            "metadata": {
                "azdata_cell_guid": "d555e182-ff9a-4316-af19-830be41bda5e",
                "language": "sql",
                "tags": []
            },
            "outputs": [
                {
                    "data": {
                        "text/html": [
                            "(1 row affected)"
                        ]
                    },
                    "metadata": {},
                    "output_type": "display_data"
                },
                {
                    "data": {
                        "text/html": [
                            "Total execution time: 00:00:00.084"
                        ]
                    },
                    "metadata": {},
                    "output_type": "display_data"
                },
                {
                    "data": {
                        "application/vnd.dataresource+json": {
                            "data": [
                                {
                                    "cosine_distance": "0.8893433329655024",
                                    "dotproduct_distance": "-3",
                                    "euclidean_distance": "7.0710678118654755"
                                }
                            ],
                            "schema": {
                                "fields": [
                                    {
                                        "name": "euclidean_distance"
                                    },
                                    {
                                        "name": "cosine_distance"
                                    },
                                    {
                                        "name": "dotproduct_distance"
                                    }
                                ]
                            }
                        },
                        "text/html": [
                            "<table>\n",
                            "<tr><th>euclidean_distance</th><th>cosine_distance</th><th>dotproduct_distance</th></tr>\n",
                            "<tr><td>7.0710678118654755</td><td>0.8893433329655024</td><td>-3</td></tr>\n",
                            "</table>"
                        ]
                    },
                    "execution_count": 3,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "declare @v1 vector(3) = cast('[1,3,-5]' as vector(3));\n",
                "declare @v2 vector(3) = cast('[4,-2,-1]' as vector(3));\n",
                "\n",
                "select \n",
                "    vector_distance('euclidean', @v1, @v2) AS euclidean_distance,\n",
                "    vector_distance('cosine', @v1, @v2) AS cosine_distance,\n",
                "    vector_distance('dot', @v1, @v2) AS dotproduct_distance;"
            ]
        },
        {
            "attachments": {},
            "cell_type": "markdown",
            "metadata": {
                "azdata_cell_guid": "bf0f3215-322f-497b-8c40-5262f76f1128",
                "language": "sql"
            },
            "source": [
                "## Store vectors in tables\n",
                "\n",
                "Vectors can be stored in a table as a single column of type VECTOR. Here is an example of how to create a table with vectors:"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 7,
            "metadata": {
                "azdata_cell_guid": "e19e918c-b4e3-482c-a439-177a90d16226",
                "language": "sql"
            },
            "outputs": [
                {
                    "data": {
                        "text/html": [
                            "(7 rows affected)"
                        ]
                    },
                    "metadata": {},
                    "output_type": "display_data"
                },
                {
                    "data": {
                        "text/html": [
                            "(7 rows affected)"
                        ]
                    },
                    "metadata": {},
                    "output_type": "display_data"
                },
                {
                    "data": {
                        "text/html": [
                            "Total execution time: 00:00:00.066"
                        ]
                    },
                    "metadata": {},
                    "output_type": "display_data"
                },
                {
                    "data": {
                        "application/vnd.dataresource+json": {
                            "data": [
                                {
                                    "id": "1",
                                    "v": "[4.0000000e+000,-2.0000000e+000,-1.0000000e+000]"
                                },
                                {
                                    "id": "2",
                                    "v": "[1.0000000e+000,3.0000000e+000,-5.0000000e+000]"
                                },
                                {
                                    "id": "3",
                                    "v": "[7.0000000e+000,-8.0000000e+000,-1.0000000e+001]"
                                },
                                {
                                    "id": "4",
                                    "v": "[4.0000000e+000,2.0000000e-001,-1.1000000e+000]"
                                },
                                {
                                    "id": "5",
                                    "v": "[0.0000000e+000,0.0000000e+000,0.0000000e+000]"
                                },
                                {
                                    "id": "6",
                                    "v": "[1.0000000e+001,1.0000000e+001,1.0000000e+001]"
                                },
                                {
                                    "id": "7",
                                    "v": "[-1.0000000e-001,-2.0000000e-001,-3.0000001e-001]"
                                }
                            ],
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "v"
                                    }
                                ]
                            }
                        },
                        "text/html": [
                            "<table>\n",
                            "<tr><th>id</th><th>v</th></tr>\n",
                            "<tr><td>1</td><td>[4.0000000e+000,-2.0000000e+000,-1.0000000e+000]</td></tr>\n",
                            "<tr><td>2</td><td>[1.0000000e+000,3.0000000e+000,-5.0000000e+000]</td></tr>\n",
                            "<tr><td>3</td><td>[7.0000000e+000,-8.0000000e+000,-1.0000000e+001]</td></tr>\n",
                            "<tr><td>4</td><td>[4.0000000e+000,2.0000000e-001,-1.1000000e+000]</td></tr>\n",
                            "<tr><td>5</td><td>[0.0000000e+000,0.0000000e+000,0.0000000e+000]</td></tr>\n",
                            "<tr><td>6</td><td>[1.0000000e+001,1.0000000e+001,1.0000000e+001]</td></tr>\n",
                            "<tr><td>7</td><td>[-1.0000000e-001,-2.0000000e-001,-3.0000001e-001]</td></tr>\n",
                            "</table>"
                        ]
                    },
                    "execution_count": 7,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "drop table if exists #vectors\n",
                "create table #vectors\n",
                "(\n",
                "    id int not null identity primary key,\n",
                "    vector vector(3) not null\n",
                ");\n",
                "\n",
                "insert into #vectors (vector)\n",
                "select \n",
                "    cast(a as vector(3))\n",
                "from\n",
                "    ( values \n",
                "        ('[4, -2, -1]'),\n",
                "        ('[1, 3, -5]'),\n",
                "        ('[7, -8, -10]'),\n",
                "        ('[4.0, 0.2, -1.1]'),\n",
                "        ('[0, 0, 0]'),\n",
                "        ('[10, 10, 10]'),\n",
                "        ('[-0.1, -0.2, -0.3]')\n",
                "    ) V(a)\n",
                ";\n",
                "\n",
                "select id, cast(vector as varchar(100)) as v from #vectors"
            ]
        },
        {
            "attachments": {},
            "cell_type": "markdown",
            "metadata": {
                "azdata_cell_guid": "5df3d4a5-cd9c-4013-aeed-ab4e33a1cb77",
                "language": "sql"
            },
            "source": [
                "# Query vectors in tables\n",
                "\n",
                "Vector distance can be used with any valid expression that evaluate to a vector. For example it can be used to calcualte distance between vectors in a table"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 8,
            "metadata": {
                "azdata_cell_guid": "058d2104-7e32-4f7a-975f-440c753f69d3",
                "language": "sql",
                "tags": []
            },
            "outputs": [
                {
                    "data": {
                        "text/html": [
                            "(3 rows affected)"
                        ]
                    },
                    "metadata": {},
                    "output_type": "display_data"
                },
                {
                    "data": {
                        "text/html": [
                            "Total execution time: 00:00:00.058"
                        ]
                    },
                    "metadata": {},
                    "output_type": "display_data"
                },
                {
                    "data": {
                        "application/vnd.dataresource+json": {
                            "data": [
                                {
                                    "distance": "0.08566878877110973",
                                    "id": "1"
                                },
                                {
                                    "distance": "0.18501562892002543",
                                    "id": "4"
                                },
                                {
                                    "distance": "0.4924421685441406",
                                    "id": "3"
                                }
                            ],
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "distance"
                                    }
                                ]
                            }
                        },
                        "text/html": [
                            "<table>\n",
                            "<tr><th>id</th><th>distance</th></tr>\n",
                            "<tr><td>1</td><td>0.08566878877110973</td></tr>\n",
                            "<tr><td>4</td><td>0.18501562892002543</td></tr>\n",
                            "<tr><td>3</td><td>0.4924421685441406</td></tr>\n",
                            "</table>"
                        ]
                    },
                    "execution_count": 8,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "declare @v vector(3) = cast('[6.4,-2.5,1.3]' as vector(3));\n",
                " \n",
                "select \n",
                "    v.id,    \n",
                "    vector_distance('cosine', @v, v.vector) as distance\n",
                "from\n",
                "    #vectors v\n",
                "where\n",
                "    vector_distance('cosine', @v, v.vector) < 0.5\n",
                "order by\n",
                "    distance\n"
            ]
        },
        {
            "attachments": {},
            "cell_type": "markdown",
            "metadata": {
                "azdata_cell_guid": "3107fd57-4427-49c6-8276-3de90a090f6b",
                "language": "sql"
            },
            "source": [
                "# Vector Dimensions\n",
                "\n",
                "In order to calculate distance, vectors must have the same number of dimensions. The following query will fail as the query vector `@v` has 6 dimensions while the stored vectors have only 3."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 9,
            "metadata": {
                "azdata_cell_guid": "27baad79-e8e3-44a8-bfe2-e0c91f08c8dd",
                "language": "sql"
            },
            "outputs": [
                {
                    "data": {
                        "text/html": [
                            "Total execution time: 00:00:00.050"
                        ]
                    },
                    "metadata": {},
                    "output_type": "display_data"
                },
                {
                    "data": {
                        "application/vnd.dataresource+json": {
                            "data": [],
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "distance"
                                    }
                                ]
                            }
                        },
                        "text/html": [
                            "<table>\n",
                            "<tr><th>id</th><th>distance</th></tr>\n",
                            "</table>"
                        ]
                    },
                    "execution_count": 9,
                    "metadata": {},
                    "output_type": "execute_result"
                },
                {
                    "ename": "",
                    "evalue": "Msg 42204, Level 16, State 3, Line 3\r\nVECTOR dimensions 6 and 3, do not match.",
                    "output_type": "error",
                    "traceback": []
                }
            ],
            "source": [
                "declare @v vector(6) = cast('[1, 2, 3, 4, 5, 6]' as vector(6));\n",
                " \n",
                "select \n",
                "    v.id,    \n",
                "    vector_distance('cosine', @v, v.vector) as distance\n",
                "from\n",
                "    #vectors v\n",
                "where\n",
                "    vector_distance('cosine', @v, v.vector) < 0.5\n",
                "order by\n",
                "    distance"
            ]
        }
    ],
    "metadata": {
        "kernelspec": {
            "display_name": "SQL",
            "language": "sql",
            "name": "SQL"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat": 4,
    "nbformat_minor": 2
}