# SQL Server

The SQL Server connector (type `sqlserver`; `mssql`, `sql_server`, and
`sql-server` are accepted aliases) translates GQL queries into T-SQL. It
requires a [mapping file](https://memgraph.com/docs/memgraph-zero/memgql/reference#mapping-schema) that
maps graph patterns to relational tables, the same format as every other SQL
backend.

The driver is [tiberius](https://crates.io/crates/tiberius), a **pure-Rust**
implementation of the TDS wire protocol. No ODBC driver or system packages are
required at build or runtime.

SQL Server is available in
[`multi` mode](https://memgraph.com/docs/memgraph-zero/memgql/multiple-graphs) only; there is no
standalone `CONNECTOR_TYPE=sqlserver` environment mode yet. You register it at
runtime with `ADD CONNECTOR … TYPE sqlserver`, as shown below.

## 1. Start SQL Server

```bash
docker network create memgql-net

docker run -d --rm \
    --name sqlserver-dev \
    --network memgql-net \
    -p 1433:1433 \
    --env ACCEPT_EULA=Y \
    --env MSSQL_SA_PASSWORD='Memgraph!2024' \
    --env MSSQL_PID=Developer \
    mcr.microsoft.com/mssql/server:2022-latest
```

SQL Server takes ~20 seconds to accept connections. Then create a working
database:

```bash
docker exec sqlserver-dev /opt/mssql-tools18/bin/sqlcmd \
    -S localhost -U sa -P 'Memgraph!2024' -C \
    -Q "IF DB_ID('test') IS NULL CREATE DATABASE [test]"
```

## 2. Seed data

```bash
docker exec -i sqlserver-dev /opt/mssql-tools18/bin/sqlcmd \
    -S localhost -U sa -P 'Memgraph!2024' -C -b -d test << 'SQL'
CREATE TABLE persons (
    id   INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(255) NOT NULL,
    age  INT
);
CREATE TABLE companies (
    id   INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(255) NOT NULL
);
CREATE TABLE knows (
    id      INT IDENTITY(1,1) PRIMARY KEY,
    from_id INT NOT NULL REFERENCES persons(id),
    to_id   INT NOT NULL REFERENCES persons(id)
);
CREATE TABLE works_at (
    id         INT IDENTITY(1,1) PRIMARY KEY,
    person_id  INT NOT NULL REFERENCES persons(id),
    company_id INT NOT NULL REFERENCES companies(id)
);

INSERT INTO persons (name, age) VALUES ('Alice', 30), ('Bob', 25);
INSERT INTO companies (name) VALUES ('Acme Corp');
INSERT INTO knows (from_id, to_id) VALUES (1, 2);
INSERT INTO works_at (person_id, company_id) VALUES (1, 1);
GO
SQL
```

## 3. Write the mapping

Save as `mapping.json`, the standard
[mapping format](https://memgraph.com/docs/memgraph-zero/memgql/reference#mapping-schema). Declare every
property you plan to query: in `multi` mode the mapping is also the routing
schema, and a property that isn't declared is a routing error, not a
passthrough.

```json
{
  "vertices": [
    {
      "label": "Person",
      "mappedTableSource": {
        "connector": "ss",
        "table": "persons",
        "metaFields": {
          "id": "id"
        }
      },
      "attributes": [
        {
          "name": "name"
        },
        {
          "name": "age",
          "type": "Int"
        }
      ]
    },
    {
      "label": "Company",
      "mappedTableSource": {
        "connector": "ss",
        "table": "companies",
        "metaFields": {
          "id": "id"
        }
      },
      "attributes": [
        {
          "name": "name"
        }
      ]
    }
  ],
  "edges": [
    {
      "label": "KNOWS",
      "from": "Person",
      "to": "Person",
      "mappedTableSource": {
        "connector": "ss",
        "table": "knows",
        "metaFields": {
          "id": "id",
          "from": "from_id",
          "to": "to_id"
        }
      }
    },
    {
      "label": "WORKS_AT",
      "from": "Person",
      "to": "Company",
      "mappedTableSource": {
        "connector": "ss",
        "table": "works_at",
        "metaFields": {
          "id": "id",
          "from": "person_id",
          "to": "company_id"
        }
      }
    }
  ]
}
```

## 4. Start MemGQL in multi mode

```bash
docker run --rm \
    --name memgql \
    --network memgql-net \
    --stop-timeout 2 \
    -p 7688:7688 \
    --env CONNECTOR_TYPE=multi \
    --env BOLT_LISTEN_ADDR=0.0.0.0:7688 \
    -v ./mapping.json:/data/mapping.json \
    memgraph/memgql:latest
```

## 5. Connect and register the backend

```bash
mgconsole --port 7688
```

```gql
ADD CONNECTOR ss TYPE sqlserver
    URI 'Server=sqlserver-dev,1433;Database=test;User Id=sa;Password=Memgraph!2024;TrustServerCertificate=true';
CREATE GRAPH social FROM FILE '/data/mapping.json';
MATCH (n:Person) RETURN n.name LIMIT 5;
```

The `URI` is an ADO-style connection string
(`Server=<host>,<port>;Database=<db>;User Id=<user>;Password=<pass>;…`).
Encryption is on by default and the server certificate is validated against
the system trust store; `TrustServerCertificateCA=<pem>` adds a private CA and
`TrustServerCertificate=true` accepts a self-signed certificate, as above. Two
MemGQL-specific keys may be added for an instance that must never wait on
MemGQL:

| Key | Effect |
|-----|--------|
| `NoLock=true` | Every table MemGQL reads carries `WITH (NOLOCK)`, so federation traffic never waits on writers. |
| `IsolationLevel=READ UNCOMMITTED` | Runs `SET TRANSACTION ISOLATION LEVEL` once per session; any T-SQL level is accepted. |

Mappings may name a view as readily as a table. Add `"schema": "reporting"`
for a non-default schema and `"database": "warehouse"` to reach another
database through the same connection (rendered `warehouse.reporting.orders_v`),
and give `metaFields.id` / `from` / `to` a list of columns for a composite key —
see the [schema file reference](https://memgraph.com/docs/memgraph-zero/memgql/schema-file#vertices).

## 6. Query

```gql
MATCH (p:Person) RETURN p.name, p.age;
```

```gql
MATCH (p:Person)-[:WORKS_AT]->(c:Company) RETURN p.name, c.name;
```

```gql
MATCH (a:Person)-[:KNOWS]->(b:Person) RETURN a.name, b.name;
```

## Dialect notes

- Positional bind parameters use SQL Server's native `@P1`, `@P2`, … form.
- `LIMIT n` / `SKIP m` render as `ORDER BY … OFFSET m ROWS FETCH NEXT n ROWS
  ONLY`; when the query has no `ORDER BY`, a neutral `ORDER BY (SELECT NULL)`
  is synthesized because T-SQL requires one for `OFFSET`/`FETCH`.
- Booleans are emitted as `BIT` (`1`/`0`).
- `char_length()` / `length()` on a column translate to T-SQL `LEN()`.
- No `INSERT … RETURNING`; SQL Server's `OUTPUT` clause is used only when the
  caller needs the auto-generated key.

## Known limitations

- **Variable-length paths**: quantified path patterns (`(){1,3}`) and trail
  semantics are not available on SQL Server yet and return an actionable error.
  Use a bounded fixed-hop chain or a Cypher backend.
- **`collect()`** is not available; there is no portable array aggregate in
  the T-SQL surface MemGQL emits yet; the query fails with the backend's error.
- **Map projections** (`RETURN n {.a, .b}`) currently return `NULL` instead of
  a map (no error is raised); don't rely on them on this backend.
- **`INSERT … RETURN`** executes the insert but returns the affected-row count
  instead of the projected values.

For connector configuration, see
[Reference](https://memgraph.com/docs/memgraph-zero/memgql/reference#sql-server-sqlserver).