SQL Development
Optimized for current PostgreSQL, MySQL, and SQL Server releases plus migration-first database workflows.
Comprehensive SQL development guidelines combining SQL coding standards, stored procedure generation, and MS SQL Server DBA best practices.
- Leverage native parallel subagent dispatch and 200k+ context windows where available.
Cross-Client Portability
This skill is written to stay usable across GitHub Copilot, Claude Code, and Codex.
- GitHub Copilot: keep the folder in a Copilot-visible skill path or wrap the
workflow in project instructions when folder discovery is unavailable.
- Claude Code: keep the folder in a local skills directory or a compatible plugin source.
- Codex: install or sync the folder into
$CODEX_HOME/skills/sql-development and restart Codex after major changes.
MCP Availability And Fallback
Preferred MCP Server: None required
- Fallback prompt: "Use the SQL Development skill without MCP. Rely on its local instructions, bundled resources, standard shell or editor tools, and direct verification. Show the evidence used before concluding."
- Do not claim an MCP operation was used when the active host does not expose it.
- Treat local files, tests, rendered outputs, logs, or screenshots as the fallback evidence path.
Anti-Patterns
- Using
SELECT * in production queries: It hides contract drift and pulls more data than the caller needs.
- Writing non-SARGable predicates: Functions on indexed columns turn otherwise cheap queries into table scans.
- Ignoring transaction and lock behavior: Correct SQL needs both logical correctness and concurrency safety.
Verification Protocol
Before claiming "skill applied successfully":
- Pass/fail: The SQL Development implementation names the target runtime, framework version, and affected files.
- Pass/fail: Build, lint, test, or equivalent local validation is run for the changed surface.
- Pass/fail: Edge cases for errors, dependency drift, and environment differences are addressed or explicitly out of scope.
- Pressure-test scenario: Apply the workflow to a change that passes happy-path tests but fails one boundary condition.
- Success metric: Zero untested success claims; every implementation claim maps to a command or artifact.
Before and After Example
-- Before
SELECT *
FROM Orders
WHERE YEAR(created_at) = 2026;
-- After
SELECT order_id, customer_id, created_at, total_amount
FROM Orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';
Uses explicit columns and a SARGable date range so indexes can do their work.
Activation Conditions
Use symptom -> action triggers: when one matches, apply this skill and verify with the protocol below.
- Writing SQL queries and stored procedures
- Designing database schemas and table structures
- Working with MS SQL Server as a DBA
- Performance tuning and query optimization
- Database backup, restore, and security configuration
- SQL Server 2025+ feature adoption and migration
Part 1: Database Schema Design
Table Naming
- All table names in singular form
- All column names in singular form
Required Columns
- All tables must have a primary key column named
id
- All tables must have
created_at for creation timestamp
- All tables must have
updated_at for last update timestamp
Constraints
- All tables must have a primary key constraint
- All foreign key constraints must have a name
- All foreign key constraints defined inline
- All foreign keys must have
ON DELETE CASCADE
- All foreign keys must have
ON UPDATE CASCADE
- All foreign keys must reference the primary key of the parent table
Part 2: SQL Coding Style
Formatting
- Uppercase for SQL keywords (
SELECT, FROM, WHERE)
- Consistent indentation for nested queries
- Comments to explain complex logic
- Break long queries into multiple lines
- Organize clauses:
SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY
Query Structure
- Use explicit column names, never
SELECT *
- Qualify column names with table alias when using multiple tables
- Prefer JOINs over subqueries when possible
- Include
LIMIT/TOP clauses to restrict result sets
- Use appropriate indexing for frequently queried columns
- Avoid functions on indexed columns in
WHERE clauses
Part 3: Stored Procedure Standards
Naming Conventions
- Prefix with
usp_
- Use PascalCase:
usp_GetCustomerOrders
- Include plural noun for multiple records:
usp_GetProducts
- Include singular noun for single record:
usp_GetProduct
Parameter Handling
- Prefix parameters with
@
- Use camelCase:
@customerId
- Provide default values for optional parameters
- Validate parameter values before use
- Document parameters with comments
- Required parameters first, optional later
Structure
- Include header comment block with description, parameters, return values
- Return standardized error codes/messages
- Return result sets with consistent column order
- Use
OUTPUT parameters for returning status information
- Prefix temporary tables with
tmp_
- Include
SET NOCOUNT ON for data-modifying procedures
Part 4: Security Best Practices
Query Security
- Parameterize all queries to prevent SQL injection
- Use prepared statements for dynamic SQL
- Avoid embedding credentials in SQL scripts
- Proper error handling without exposing system details
- Avoid dynamic SQL in stored procedures
Transaction Management
- Explicitly begin and commit transactions
- Use appropriate isolation levels
- Avoid long-running transactions that lock tables
- Use batch processing for large data operations
Part 5: MS SQL Server DBA
Tooling
- Install and enable
ms-mssql.mssql VS Code extension for full database management
- Use official Microsoft documentation for reference and troubleshooting
DBA Responsibilities
- Database creation and configuration
- Backup and restore strategies
- Performance tuning and index optimization
- Security management and auditing
- Upgrades and compatibility planning (SQL Server 2025+)
Best Practices
- Focus on tool-based database inspection over codebase analysis
- Highlight deprecated/discontinued features in SQL Server 2025+
- Encourage secure, auditable, performance-oriented solutions
- Reference official docs for troubleshooting
- Warn about deprecated features and suggest alternatives
Troubleshooting
| Issue |
Solution |
| Slow queries |
Check execution plan, add indexes, optimize JOINs |
| Deadlocks |
Reduce transaction scope, consistent lock ordering |
| Missing data |
Verify CASCADE rules, check transaction isolation |
| Permission errors |
Review GRANT/REVOKE statements, check role membership |
| Connection issues |
Verify firewall rules, connection strings, SQL auth settings |
Common Pitfalls
- Using
SELECT * in production queries: It hides contract drift and pulls more data than the caller actually needs.
- Writing non-SARGable predicates: Functions on indexed columns turn otherwise cheap queries into table scans.
- Skipping transaction and lock analysis: Correct SQL needs both logical correctness and concurrency safety.
References & Resources
Documentation
- T-SQL Patterns — MERGE, CTEs, PIVOT, JSON operations, window functions, and error handling
- Performance Tuning — Execution plans, index tuning, Query Store, and anti-patterns
Scripts
Examples
Related Skills
- php-development: Use it when the workflow also needs modern PHP backend implementation.
- powerbi-modeling: Use it when the workflow also needs Power BI semantic model design and DAX work.
- code-quality: Use it when the workflow also needs two-stage review (spec compliance first, then code quality), maintainability, and refactoring guidance.
- systematic-debugging: Use it when the workflow also needs root-cause debugging before proposing fixes.
1---
2name: sql-development
3version: "2.0"
4last_updated: 2026-09-08
5tags: [sql, development, testing, quality, automation]
6description: "T-SQL, stored procedures, and MS SQL Server DBA practices. Use when writing SQL queries, designing schemas, tuning SQL Server performance, managing backups, configuring security, or using SQL Server 2025+ features."
7metadata:
8 source: https://skillmd.com/skills/practicalswan/sql-development
9---
10
11# SQL Development
12
13> Optimized for current PostgreSQL, MySQL, and SQL Server releases plus migration-first database workflows.
14
15Comprehensive SQL development guidelines combining SQL coding standards, stored procedure generation, and MS SQL Server DBA best practices.
16
17- Leverage native parallel subagent dispatch and 200k+ context windows where available.
18
19<!-- MCP:START -->
20
21<!-- PORTABILITY:START -->
22## Cross-Client Portability
23
24This skill is written to stay usable across GitHub Copilot, Claude Code, and Codex.
25
26- GitHub Copilot: keep the folder in a Copilot-visible skill path or wrap the
27 workflow in project instructions when folder discovery is unavailable.
28- Claude Code: keep the folder in a local skills directory or a compatible plugin source.
29- Codex: install or sync the folder into
30 `$CODEX_HOME/skills/sql-development` and restart Codex after major changes.
31
32<!-- PORTABILITY:END -->
33
34## MCP Availability And Fallback
35
36Preferred MCP Server: None required
37
38- Fallback prompt: "Use the SQL Development skill without MCP. Rely on its local instructions, bundled resources, standard shell or editor tools, and direct verification. Show the evidence used before concluding."
39- Do not claim an MCP operation was used when the active host does not expose it.
40- Treat local files, tests, rendered outputs, logs, or screenshots as the fallback evidence path.
41
42<!-- MCP:END -->
43
44## Anti-Patterns
45
46- Using `SELECT *` in production queries: It hides contract drift and pulls more data than the caller needs.
47- Writing non-SARGable predicates: Functions on indexed columns turn otherwise cheap queries into table scans.
48- Ignoring transaction and lock behavior: Correct SQL needs both logical correctness and concurrency safety.
49
50## Verification Protocol
51
52Before claiming "skill applied successfully":
53
541. Pass/fail: The SQL Development implementation names the target runtime, framework version, and affected files.
552. Pass/fail: Build, lint, test, or equivalent local validation is run for the changed surface.
563. Pass/fail: Edge cases for errors, dependency drift, and environment differences are addressed or explicitly out of scope.
574. Pressure-test scenario: Apply the workflow to a change that passes happy-path tests but fails one boundary condition.
585. Success metric: Zero untested success claims; every implementation claim maps to a command or artifact.
59
60## Before and After Example
61
62```sql
63-- Before
64SELECT *
65FROM Orders
66WHERE YEAR(created_at) = 2026;
67
68-- After
69SELECT order_id, customer_id, created_at, total_amount
70FROM Orders
71WHERE created_at >= '2026-01-01'
72 AND created_at < '2027-01-01';
73```
74
75Uses explicit columns and a SARGable date range so indexes can do their work.
76
77## Activation Conditions
78
79Use symptom -> action triggers: when one matches, apply this skill and verify with the protocol below.
80
81- Writing SQL queries and stored procedures
82- Designing database schemas and table structures
83- Working with MS SQL Server as a DBA
84- Performance tuning and query optimization
85- Database backup, restore, and security configuration
86- SQL Server 2025+ feature adoption and migration
87
88---
89
90## Part 1: Database Schema Design
91
92### Table Naming
93- All table names in singular form
94- All column names in singular form
95
96### Required Columns
97- All tables must have a primary key column named `id`
98- All tables must have `created_at` for creation timestamp
99- All tables must have `updated_at` for last update timestamp
100
101### Constraints
102- All tables must have a primary key constraint
103- All foreign key constraints must have a name
104- All foreign key constraints defined inline
105- All foreign keys must have `ON DELETE CASCADE`
106- All foreign keys must have `ON UPDATE CASCADE`
107- All foreign keys must reference the primary key of the parent table
108
109---
110
111## Part 2: SQL Coding Style
112
113### Formatting
114- Uppercase for SQL keywords (`SELECT`, `FROM`, `WHERE`)
115- Consistent indentation for nested queries
116- Comments to explain complex logic
117- Break long queries into multiple lines
118- Organize clauses: `SELECT`, `FROM`, `JOIN`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`
119
120### Query Structure
121- Use explicit column names, never `SELECT *`
122- Qualify column names with table alias when using multiple tables
123- Prefer JOINs over subqueries when possible
124- Include `LIMIT`/`TOP` clauses to restrict result sets
125- Use appropriate indexing for frequently queried columns
126- Avoid functions on indexed columns in `WHERE` clauses
127
128---
129
130## Part 3: Stored Procedure Standards
131
132### Naming Conventions
133- Prefix with `usp_`
134- Use PascalCase: `usp_GetCustomerOrders`
135- Include plural noun for multiple records: `usp_GetProducts`
136- Include singular noun for single record: `usp_GetProduct`
137
138### Parameter Handling
139- Prefix parameters with `@`
140- Use camelCase: `@customerId`
141- Provide default values for optional parameters
142- Validate parameter values before use
143- Document parameters with comments
144- Required parameters first, optional later
145
146### Structure
147- Include header comment block with description, parameters, return values
148- Return standardized error codes/messages
149- Return result sets with consistent column order
150- Use `OUTPUT` parameters for returning status information
151- Prefix temporary tables with `tmp_`
152- Include `SET NOCOUNT ON` for data-modifying procedures
153
154---
155
156## Part 4: Security Best Practices
157
158### Query Security
159- Parameterize all queries to prevent SQL injection
160- Use prepared statements for dynamic SQL
161- Avoid embedding credentials in SQL scripts
162- Proper error handling without exposing system details
163- Avoid dynamic SQL in stored procedures
164
165### Transaction Management
166- Explicitly begin and commit transactions
167- Use appropriate isolation levels
168- Avoid long-running transactions that lock tables
169- Use batch processing for large data operations
170
171---
172
173## Part 5: MS SQL Server DBA
174
175### Tooling
176- Install and enable `ms-mssql.mssql` VS Code extension for full database management
177- Use official Microsoft documentation for reference and troubleshooting
178
179### DBA Responsibilities
180- Database creation and configuration
181- Backup and restore strategies
182- Performance tuning and index optimization
183- Security management and auditing
184- Upgrades and compatibility planning (SQL Server 2025+)
185
186### Best Practices
187- Focus on tool-based database inspection over codebase analysis
188- Highlight deprecated/discontinued features in SQL Server 2025+
189- Encourage secure, auditable, performance-oriented solutions
190- Reference official docs for troubleshooting
191- Warn about deprecated features and suggest alternatives
192
193---
194
195## Troubleshooting
196
197| Issue | Solution |
198|-------|----------|
199| Slow queries | Check execution plan, add indexes, optimize JOINs |
200| Deadlocks | Reduce transaction scope, consistent lock ordering |
201| Missing data | Verify CASCADE rules, check transaction isolation |
202| Permission errors | Review GRANT/REVOKE statements, check role membership |
203| Connection issues | Verify firewall rules, connection strings, SQL auth settings |
204
205---
206
207## Common Pitfalls
208
209- Using `SELECT *` in production queries: It hides contract drift and pulls more data than the caller actually needs.
210- Writing non-SARGable predicates: Functions on indexed columns turn otherwise cheap queries into table scans.
211- Skipping transaction and lock analysis: Correct SQL needs both logical correctness and concurrency safety.
212
213## References & Resources
214
215### Documentation
216- [T-SQL Patterns](./references/tsql-patterns.md) — MERGE, CTEs, PIVOT, JSON operations, window functions, and error handling
217- [Performance Tuning](./references/performance-tuning.md) — Execution plans, index tuning, Query Store, and anti-patterns
218
219### Scripts
220- [Stored Procedure Template](./scripts/stored-proc-template.sql) — Production-ready SP template with TRY/CATCH, pagination, and dynamic sorting
221
222### Examples
223- [Schema Design Example](./examples/schema-design-example.md) — Recipe Management System with 10 tables, stored procedures, and migrations
224
225---
226
227## Related Skills
228
229- [php-development](../php-development/SKILL.md): Use it when the workflow also needs modern PHP backend implementation.
230- [powerbi-modeling](../powerbi-modeling/SKILL.md): Use it when the workflow also needs Power BI semantic model design and DAX work.
231- [code-quality](../code-quality/SKILL.md): Use it when the workflow also needs two-stage review (spec compliance first, then code quality), maintainability, and refactoring guidance.
232- [systematic-debugging](../systematic-debugging/SKILL.md): Use it when the workflow also needs root-cause debugging before proposing fixes.