Data Explorer

The Data Explorer tab on the instance page works on the instance's initial database. It opens for anyone who has sqlservercluster/dataRead on the instance; each thing you do in it then needs its own permission, checked against you, not against the server's sa login, which the Data Explorer connects as. The Database Explorer role can browse and edit rows and run SELECT, INSERT, UPDATE, DELETE and MERGE, but not create, change or drop objects; Owner, Contributor, Platform Owner and Platform Contributor can do all of it — see Permissions.

A stopped instance has no Data Explorer: the tab shows Cluster unavailable until the instance is running again.

Find a table

The panel on the left lists the database's schemas under Schemas. Expand a schema to see its tables and views, and a table to see its columns, their types, which of them are NOT NULL, and a key icon on the primary key. Listing them needs sqlservercluster/schemaRead.

Browse and edit rows

Choose a table, and the Browse tab shows its rows, 100 at a time in primary-key order, with Prev, Next and Refresh. Reading rows needs sqlservercluster/dataRead.

A table whose primary key is a single column can be edited in place, with sqlservercluster/dataWrite:

• change a cell's value;
• Row adds a new row to fill in;
• Mark for delete marks a row for deletion.

The changes wait until you choose Submit — which names how many there are — or Discard all. A table with a primary key of several columns, or none, can only be read here; change its rows with a statement on the Query tab.

Run statements

The Query tab has an editor for a T-SQL batch and two buttons:

• Dry run runs the batch inside a transaction and rolls it back, so you see what it would do — rows returned, rows affected, errors — without changing anything.
• Run runs the batch and commits it.

The whole batch runs as one transaction: if a statement fails, nothing in the batch is kept. Before anything runs, every statement is classified and checked against your permissions; the result lists each statement's class and, when you lack one, the permission that is Missing, which you can then ask for as temporary access — see Request access.

A batch that runs for more than 30 seconds is stopped, and its results stop at 5,000 rows in total, marked as truncated.

Caution

Every batch submitted here is stored with its full text, refused and denied ones included. Anyone with sqlservercluster/read on the instance — the Reader and Databases Reader roles among them — can list the recent ones through the API. Do not put passwords or other secrets in a statement.

Schema changes need a dry run

A batch that creates, changes or drops objects — including TRUNCATE TABLE — is only committed after a clean dry run of exactly the same batch:

1. Choose Dry run.
2. When the dry run succeeds, Commit DDL changes appears. Choose it within two minutes.

Editing the batch, letting the two minutes pass, or a dry run by someone else does not count: the commit is refused and you run a fresh dry run.

What runs

Every other statement is refused, whatever your permissions, and the whole batch with it:

• transaction control — BEGIN, COMMIT, ROLLBACK and SAVE TRANSACTION — because the Data Explorer owns the transaction;
• EXEC and sp_executesql, which could run statements the check never saw;
• anything else, such as USE, DECLARE, SET, GRANT, CREATE LOGIN, CREATE USER or BACKUP;
• a batch that does not parse.

For those, connect with a SQL Server client instead — see Connect to an instance.

Important

The check looks at the statements you submit, not at what they do once they run, and everything runs as sa. SELECT … INTO creates a table with sqlservercluster/queryExecute alone. The body of a procedure, function or trigger is not checked statement by statement when it is created, so it can hold statements the Data Explorer refuses on their own — and a trigger runs them as sa when a row change made through the Data Explorer fires it.

With the CLI

stsh sqlserver schemas my-sql -g my-resource-group
stsh sqlserver rows my-sql -g my-resource-group dbo orders
stsh sqlserver query my-sql -g my-resource-group "SELECT COUNT(*) FROM dbo.orders"
stsh sqlserver query my-sql -g my-resource-group "DELETE FROM dbo.orders WHERE id = 42" --dry-run

stsh sqlserver rows shows the first 100 rows. stsh sqlserver query runs a dry run first and commits only when it passes, so schema changes work from the CLI too; with --dry-run it stops after the dry run.