sqldb-authoring-cli

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

CRITICAL NOTES

1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering
2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

SQL Database Authoring — CLI Skill

Create and manage SQL database in Fabric items, author T-SQL DDL/DML, deploy schema via SqlPackage, configure source control, CI/CD, and GraphQL APIs using sqlcmd and az rest.

Table of Contents

For Fabric topology, capacity, OneLake, auth, control-plane REST, jobs see COMMON-CORE.md and COMMON-CLI.md.

Tool Stack

Agent check — before first operation, confirm the required CLI tools and authentication are already configured by following the shared setup guidance in COMMON-CLI.md. Do not duplicate install or az login steps in this skill.

Connection

Discover the SQL Database TDS Endpoint

For workspace/item resolution, SQL Database endpoint discovery, and CLI connection patterns, use the shared guidance in common/COMMON-CLI.md and common/COMMON-CORE.md instead of embedding az rest or sqlcmd recipes here.

Follow the SQL Database endpoint discovery steps in COMMON-CLI.md, then bring the discovered server FQDN and database name back into this skill for SQLDB-specific authoring and deployment tasks.

• Use the SQL Database endpoint values returned by Fabric for the target item; do not hardcode endpoint domains or database names.
• When generating deployment or authoring steps, treat connectivity details as inputs discovered via the shared common guidance.
• Focus this skill on schema authoring, DDL/DML design, constraints, indexing, source control, SqlPackage deployment, and GraphQL/API behaviors rather than connection bootstrapping.

Connection guidance

If the user needs help connecting interactively or from CI/CD, refer them to common/COMMON-CLI.md for the shared sqlcmd setup, authentication, and invocation patterns.

For SQLDB-specific authoring in this skill, assume the connection inputs are already known:

• serverFqdn
• databaseName

Agentic Workflows

Schema Discovery Before Authoring

Before any write operation, discover the target schema:

# 1. List tables
$SQLCMD -Q "SELECT table_schema, table_name FROM information_schema.tables WHERE table_type='BASE TABLE' ORDER BY 1,2" -W

# 2. Check columns
$SQLCMD -Q "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name='Orders' ORDER BY ordinal_position" -W

# 3. Sample data
$SQLCMD -Q "SELECT TOP 5 * FROM dbo.Orders" -W

# 4. Check constraints
$SQLCMD -Q "SELECT tc.constraint_name, tc.constraint_type, kcu.column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name=kcu.constraint_name WHERE tc.table_name='Orders' ORDER BY tc.constraint_type" -W

# 5. Check indexes
$SQLCMD -Q "SELECT i.name, i.type_desc, STRING_AGG(c.name,', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS cols FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id=ic.object_id AND i.index_id=ic.index_id JOIN sys.columns c ON ic.object_id=c.object_id AND ic.column_id=c.column_id WHERE i.object_id=OBJECT_ID('dbo.Orders') GROUP BY i.name, i.type_desc" -W

# 6. Row counts
$SQLCMD -Q "SELECT s.name AS [schema], t.name AS [table], SUM(p.rows) AS row_count FROM sys.tables t JOIN sys.schemas s ON t.schema_id=s.schema_id JOIN sys.partitions p ON t.object_id=p.object_id AND p.index_id IN (0,1) GROUP BY s.name, t.name ORDER BY row_count DESC" -W

# 7. Programmability objects
$SQLCMD -Q "SELECT name, type_desc FROM sys.objects WHERE type IN ('V','FN','IF','P','TF','TR') ORDER BY type_desc, name" -W

Workflow

1. Discover → Run steps 1–5 to understand available tables/columns.
2. Sample → SELECT TOP 5 on relevant tables.
3. Formulate → Select pattern from SQLDB-AUTHORING-CORE.md (Table DDL, Temporal Tables, Data Virtualization, Views, DML, etc.).
4. Execute → $SQLCMD -Q "..." or $SQLCMD -i file.sql for multi-statement.
5. Verify → Query affected table (SELECT COUNT(*), SELECT TOP 5).
6. Deploy (optional) → Extract with sqlpackage /Action:Extract for .dacpac; set up source control.

Gotchas, Rules, Troubleshooting

For full authoring gotchas: SQLDB-AUTHORING-CORE.md Authoring Gotchas and Troubleshooting. For CLI-specific issues: COMMON-CLI.md Gotchas & Troubleshooting (CLI-Specific).

MUST DO

• Verify workspace has capacity before creating database — call GET /v1/workspaces/{id} and check capacityId.
• Always -d <DatabaseName> — FQDN alone is insufficient.
• Always -G or --authentication-method — SQL auth not supported on Fabric.
• Follow COMMON-CLI.md authentication guidance first — ActiveDirectoryDefault depends on an authenticated CLI session.
• Specify collation at creation — cannot be changed after the database is created.
• Use SqlPackage for repeatable deployments — extract .dacpac → publish for incremental schema changes.
• Use -i file.sql for multi-statement batches (CREATE PROCEDURE, transactions with GO separators).
• SET NOCOUNT ON; in scripts — suppresses row-count messages that corrupt output.

AVOID

• ODBC sqlcmd (/opt/mssql-tools/bin/sqlcmd) — use the Go version.
• Omitting -W in scripts — trailing spaces corrupt CSV.
• Hardcoded FQDNs — discover via REST API.
• MARS — not supported. Remove MultipleActiveResultSets.
• CREATE LOGIN / SQL authentication — Entra users only.
• Modifying tables with CCI / ALTER INDEX ALL while mirroring is active — stop mirroring or alter individual indexes.
• vector / json columns on tables that must mirror — cannot be mirrored to OneLake.
• Special characters in DB / column names — see Limitations Reference.
• Modifying .sqlproj in source-controlled repo — reset on next sync.
• Unsupported features (CDC, Always Encrypted, in-memory, ledger, full-text GA, partition SWITCH, EXECUTE AS, hierarchyid/sql_variant/timestamp PKs, CTAS, COPY INTO) — see Limitations Reference for the full list.
• Exceeding resource limits (32 vCores, 4 TB, 1024 GB tempdb, 150 DBs/workspace).

PREFER

• az rest for database creation over portal — scriptable and repeatable.
• SqlPackage publish (.dacpac) over manual DDL for schema evolution — tracks incremental changes.
• Enforced constraints (PK, FK, CHECK, DEFAULT) — SQL Database supports them fully, unlike DW.
• Temporal tables for audit trails and slowly-changing dimensions — full system-versioning available. Note: history table is excluded from mirroring.
• Data virtualization (external tables / OPENROWSET) to query OneLake without ETL. Note: external tables themselves are not mirrored.
• INSERT ... SELECT for bulk data movement within the database.
• BCP for high-performance bulk import/export.
• Native AI functions / sp_invoke_external_rest_endpoint for embedding generation in T-SQL.
• sqlcmd (Go) -G over curl+token for SQL queries.
• -Q (non-interactive exit) for agentic use.
• -i file.sql over -Q "..." for anything beyond simple one-liners.
• -F vertical for exploration of wide tables.
• Env vars (FABRIC_SERVER, FABRIC_DB) for script reuse.
• Auto-tuning — let the engine manage indexes; check sys.dm_db_tuning_recommendations periodically.
• Optimized locking — enabled by default; reduces lock memory and eliminates lock escalation.

TROUBLESHOOTING