Schemas and tables

The Data Explorer builds CREATE and DROP statements for you from a form, shows them under SQL preview, and runs them the way it runs any schema change: a dry run first, then the commit — see Schema changes need a dry run. Objects are created in the instance's initial database.

Open the instance's Data Explorer tab, which needs sqlservercluster/dataRead; the schemas are listed on the left under Schemas, which needs sqlservercluster/schemaRead. Creating a schema or a table needs sqlservercluster/schemaWrite, and dropping one also sqlservercluster/schemaDrop, both held as ordinary actions: Owner, Contributor, Platform Owner and Platform Contributor can create and drop, the Database Explorer role can do neither — see Permissions.

Create a schema

1. Choose + next to Schemas (Create schema).
2. Enter the Name: up to 128 letters, digits and underscores, starting with a letter or an underscore. The SQL preview shows the statement, CREATE SCHEMA [<name>];.
3. Choose Dry run. When it passes, choose Confirm & create.

The statement has no AUTHORIZATION clause and the Data Explorer runs it as the sa login, so the schema is owned by dbo. It appears in the list with no tables.

The form also has Comment (optional) and IF NOT EXISTS. Neither is part of the statement that runs: T-SQL has no IF NOT EXISTS for a schema, and the comment is not stored. A schema with a name that already exists therefore fails in the dry run.

Create a table

1. Point at a schema in the list and choose its + (Create table). The form opens as Create a new table under that schema; the schema can be changed at the top.

2. Enter the table's Name, with the same rules as a schema name.

3. Define the Columns. The form starts with two:

   • id — bigint, primary key, Auto;
   • created_at — datetime2, nullable, default SYSUTCDATETIME().

   For each column set the Name, the Type — pick one from the list or choose Custom… for another T-SQL type, such as hierarchyid, optionally with a size: (50), (10,2) or (max) — and a Default expression, if any; the list next to it suggests common ones. Then tick Primary to make the column part of the primary key, Null to allow NULL, and Auto to make it an identity column that numbers itself. Add column adds a row; drag a row by its handle to reorder, or remove it.

4. Check the SQL preview, then choose Review & save. When the dry run passes, choose Confirm & create.

Rules the form enforces:

• column names are unique in the table;
• Auto needs an integer type — tinyint, smallint, int or bigint — and no default; an identity column is always NOT NULL;
• several columns ticked Primary make one primary key over all of them.

The form has no foreign keys, indexes or other constraints. Add them afterwards with an ALTER TABLE or CREATE INDEX statement on the Query tab. As for schemas, IF NOT EXISTS and Comment (optional) are not part of the statement that runs.

Drop a table or a schema

1. Point at the table or schema in the list and choose its bin icon.
2. Check the SQL preview and choose Dry run.
3. When the dry run passes, type the name to confirm and choose Drop table — or Drop view, Drop schema.

This needs sqlservercluster/schemaDrop as well as sqlservercluster/schemaWrite.

A schema can only be dropped when it is empty: drop its tables and other objects first. The form's CASCADE switch is not part of the statement, because T-SQL has none.