{"id":"8786e57f-d1e4-4a27-88ff-8a6cfd2eb882","shortId":"ZtjjbR","kind":"skill","title":"sqlserver","tagline":"Use when writing T-SQL, editing SQL Server .sql files, using sqlcmd, SQL Server connection strings, stored procedures, execution plans, indexes, Always On, JSON, security, or connector code.","description":"# SQL Server\n\nMicrosoft SQL Server is a relational database engine spanning on-premises, containers, and Azure SQL. This skill covers T-SQL development, performance tuning, high availability, security, and connectivity across all major languages.\n\n## Quick Reference\n\n### Connection Setup (Python pyodbc)\n\n```python\nimport pyodbc\n\nconn_str = (\n    \"DRIVER={ODBC Driver 18 for SQL Server};\"\n    \"SERVER=myserver.database.windows.net,1433;\"\n    \"DATABASE=mydb;\"\n    \"UID=myuser;PWD=mypassword;\"\n    \"Encrypt=yes;TrustServerCertificate=no;\"\n)\nconn = pyodbc.connect(conn_str)\ncursor = conn.cursor()\n\n# Always use parameterized queries\ncursor.execute(\"SELECT OrderID, Total FROM Orders WHERE CustomerID = ?\", (42,))\nrows = cursor.fetchall()\n```\n\n### Stored Procedure Template\n\n```sql\nCREATE OR ALTER PROCEDURE dbo.usp_GetCustomerOrders\n    @CustomerID  INT,\n    @StartDate   DATE = NULL,           -- optional with default\n    @TotalCount  INT OUTPUT             -- output parameter\nAS\nBEGIN\n    SET NOCOUNT ON;\n\n    BEGIN TRY\n        SELECT OrderID, OrderDate, Total\n        FROM Orders\n        WHERE CustomerID = @CustomerID\n          AND (@StartDate IS NULL OR OrderDate >= @StartDate)\n        ORDER BY OrderDate DESC;\n\n        SET @TotalCount = @@ROWCOUNT;\n    END TRY\n    BEGIN CATCH\n        THROW;\n    END CATCH\nEND;\nGO\n```\n\n### Index Patterns\n\n```sql\n-- Clustered index (one per table, defines physical order)\nCREATE CLUSTERED INDEX IX_Orders_OrderDate ON Orders(OrderDate);\n\n-- Non-clustered covering index (INCLUDE avoids key lookups)\nCREATE NONCLUSTERED INDEX IX_Orders_CustomerID\n    ON Orders(CustomerID)\n    INCLUDE (OrderDate, Total);\n\n-- Filtered index (partial index for common queries)\nCREATE NONCLUSTERED INDEX IX_Orders_Active\n    ON Orders(Status)\n    WHERE Status = 'Active';\n```\n\n### Key T-SQL Patterns\n\n```sql\n-- CTE with window function\nWITH RankedOrders AS (\n    SELECT\n        CustomerID, OrderID, Total,\n        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Total DESC) AS RowNum\n    FROM Orders\n)\nSELECT CustomerID, OrderID, Total\nFROM RankedOrders\nWHERE RowNum = 1;\n\n-- MERGE upsert\nMERGE INTO Inventory AS tgt\nUSING @Updates AS src ON tgt.ProductID = src.ProductID\nWHEN MATCHED THEN UPDATE SET tgt.Qty = src.Qty\nWHEN NOT MATCHED THEN INSERT (ProductID, Qty) VALUES (src.ProductID, src.Qty);\n\n-- Offset pagination (2012+)\nSELECT OrderID, OrderDate, Total\nFROM Orders\nORDER BY OrderDate DESC\nOFFSET @PageSize * (@PageNum - 1) ROWS\nFETCH NEXT @PageSize ROWS ONLY;\n```\n\n<workflow>\n\n## Workflow\n\n### Step 1: Identify the Pattern\n\n| Need | Go to | Key Concept |\n| --- | --- | --- |\n| Write a complex query | tsql_patterns.md | CTEs, window functions, APPLY |\n| Build a stored procedure | stored_procedures.md | SET NOCOUNT ON, TRY/CATCH |\n| Query is slow | performance.md | Execution plans, Query Store |\n| Connect from app code | connections.md | Parameterized queries, drivers |\n| Work with JSON data | json.md | JSON_VALUE, OPENJSON, FOR JSON |\n| Lock down access | security.md | RLS, Dynamic Data Masking |\n| Backup, maintain, monitor | admin.md | DBCC, DMVs, SQL Agent |\n| HA / DR architecture | availability.md | Always On AG, FCI |\n\n### Step 2: Implement\n\n1. Use parameterized queries for all external input -- never concatenate strings\n2. Start stored procedures with `SET NOCOUNT ON` to suppress row count messages\n3. Wrap DML in explicit transactions with `TRY/CATCH` and `THROW`\n4. Add covering indexes (`INCLUDE` columns) to eliminate key lookups\n5. Test with actual execution plans (`SET STATISTICS XML ON` or SSMS Ctrl+M)\n\n### Step 3: Validate\n\nRun through the validation checkpoint below before considering the work complete.\n\n</workflow>\n\n<guardrails>\n\n## Guardrails\n\n- **Always use parameterized queries**: `?` placeholders in pyodbc, `@Param` in T-SQL -- never string concatenation\n- **Always SET NOCOUNT ON** in stored procedures -- reduces network traffic and prevents DONE_IN_PROC interference\n- **Always use TRY/CATCH** for error handling in procedures -- use `THROW` (not `RAISERROR`) for re-throwing\n- **Always use `CREATE OR ALTER`** (2016 SP1+) instead of DROP/CREATE to preserve permissions\n- **Prefer inline TVFs** over multi-statement TVFs and scalar UDFs -- multi-statement TVFs prevent parallelism\n- **Prefer `OFFSET/FETCH`** over `ROW_NUMBER()` for simple pagination (2012+)\n- **Never use `SELECT *`** in production queries -- always specify columns explicitly\n- **Never use cursors** unless no set-based alternative exists -- set-based operations are orders of magnitude faster\n\n</guardrails>\n\n<validation>\n\n### Validation Checkpoint\n\nBefore delivering SQL Server code, verify:\n\n- [ ] All user input passes through parameterized queries (no string concatenation)\n- [ ] Stored procedures have `SET NOCOUNT ON` as the first statement\n- [ ] DML operations are wrapped in `BEGIN TRY / BEGIN CATCH` with `THROW`\n- [ ] Indexes include `INCLUDE` columns to cover common query patterns\n- [ ] No implicit conversions in WHERE clauses (match data types exactly)\n- [ ] `CREATE OR ALTER` used instead of `DROP`/`CREATE` where supported\n\n</validation>\n\n<example>\n\n## Example\n\n**Task:** \"Create a stored procedure to search orders by customer and date range, with a Python caller using parameterized queries.\"\n\n```sql\n-- T-SQL: Stored procedure with proper error handling\nCREATE OR ALTER PROCEDURE dbo.usp_SearchOrders\n    @CustomerID  INT,\n    @StartDate   DATE = NULL,\n    @EndDate     DATE = NULL,\n    @PageNum     INT = 1,\n    @PageSize    INT = 50\nAS\nBEGIN\n    SET NOCOUNT ON;\n\n    BEGIN TRY\n        SELECT\n            o.OrderID,\n            o.OrderDate,\n            o.Total,\n            c.CustomerName\n        FROM Orders o\n        INNER JOIN Customers c ON c.CustomerID = o.CustomerID\n        WHERE o.CustomerID = @CustomerID\n          AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)\n          AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)\n        ORDER BY o.OrderDate DESC\n        OFFSET @PageSize * (@PageNum - 1) ROWS\n        FETCH NEXT @PageSize ROWS ONLY;\n    END TRY\n    BEGIN CATCH\n        THROW;\n    END CATCH\nEND;\nGO\n```\n\n```python\n# Python: Parameterized call via pyodbc\nimport pyodbc\n\nconn = pyodbc.connect(\n    \"DRIVER={ODBC Driver 18 for SQL Server};\"\n    \"SERVER=myserver,1433;DATABASE=mydb;\"\n    \"UID=myuser;PWD=mypassword;\"\n    \"Encrypt=yes;TrustServerCertificate=no;\"\n)\n\ncursor = conn.cursor()\ncursor.execute(\n    \"EXEC dbo.usp_SearchOrders @CustomerID=?, @StartDate=?, @PageNum=?, @PageSize=?\",\n    (42, \"2025-01-01\", 1, 25),\n)\n\nfor row in cursor.fetchall():\n    print(row.OrderID, row.OrderDate, row.Total, row.CustomerName)\n\ncursor.close()\nconn.close()\n```\n\n</example>\n\n## References Index\n\nFor detailed guides and code examples, refer to the following documents in `references/`:\n\n- **[T-SQL Patterns](references/tsql_patterns.md)** -- Window functions, CTEs, MERGE, APPLY, PIVOT, temporal tables, pagination.\n- **[Stored Procedures & T-SQL Programming](references/stored_procedures.md)** -- Procedures, functions, error handling, transactions, cursors, dynamic SQL, triggers.\n- **[Performance Tuning](references/performance.md)** -- Execution plans, Query Store, indexing strategy, wait stats, parameter sniffing, deadlocks.\n- **[Connection Patterns](references/connections.md)** -- Python, Node.js, .NET, Java, Go drivers; connection strings; Azure AD / Managed Identity.\n- **[JSON in SQL Server](references/json.md)** -- JSON_VALUE, OPENJSON, FOR JSON, computed-column indexing, JSON type (2022+/2025).\n- **[Security](references/security.md)** -- RLS, Dynamic Data Masking, Always Encrypted, TDE, auditing, roles and permissions.\n- **[Administration](references/admin.md)** -- Backup/restore, DBCC, maintenance, SQL Agent, DMV monitoring, server configuration.\n- **[High Availability & DR](references/availability.md)** -- Always On AG, FCI, log shipping, Azure SQL geo-replication, contained AGs.\n- **[Columnstore & Analytics](references/columnstore.md)** -- Columnstore indexes, batch mode, HTAP patterns, In-Memory OLTP.\n- **[CLI & Tools](references/sqlcmd.md)** -- sqlcmd, SSMS, Azure Data Studio, dbatools, sp_whoisactive, Ola Hallengren.\n\n## Official References\n\n- <https://learn.microsoft.com/en-us/sql/sql-server/>\n- <https://learn.microsoft.com/en-us/sql/t-sql/language-reference>\n- <https://learn.microsoft.com/en-us/azure-data-studio/>\n\n## Shared Styleguide Baseline\n\n- Use shared styleguides for generic language/framework rules to reduce duplication in this skill.\n- [General Principles](https://github.com/cofin/flow/blob/main/templates/styleguides/general.md)\n- Keep this skill focused on tool-specific workflows, edge cases, and integration details.","tags":["sqlserver","flow","cofin","agent-skills","ai-agents","beads","claude-code","codex","cursor","developer-tools","gemini-cli","opencode"],"capabilities":["skill","source-cofin","skill-sqlserver","topic-agent-skills","topic-ai-agents","topic-beads","topic-claude-code","topic-codex","topic-cursor","topic-developer-tools","topic-gemini-cli","topic-opencode","topic-plugin","topic-slash-commands","topic-spec-driven-development"],"categories":["flow"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/cofin/flow/sqlserver","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add cofin/flow","source_repo":"https://github.com/cofin/flow","install_from":"skills.sh"}},"qualityScore":"0.455","qualityRationale":"deterministic score 0.46 from registry signals: · indexed on github topic:agent-skills · 11 github stars · SKILL.md body (8,650 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-18T19:07:39.710Z","embedding":null,"createdAt":"2026-04-23T13:04:01.926Z","updatedAt":"2026-05-18T19:07:39.710Z","lastSeenAt":"2026-05-18T19:07:39.710Z","tsv":"'-01':828,829 '/2025':934 '/cofin/flow/blob/main/templates/styleguides/general.md)':1033 '/en-us/azure-data-studio/':1012 '/en-us/sql/sql-server/':1006 '/en-us/sql/t-sql/language-reference':1009 '1':280,328,337,417,720,770,830 '1433':87,805 '18':81,799 '2':415,428 '2012':314,575 '2016':542 '2022':933 '2025':827 '25':831 '3':441,476 '4':451 '42':116,826 '5':461 '50':723 'access':392 'across':63 'activ':234,240 'actual':464 'ad':914 'add':452 'admin.md':401 'administr':948 'ag':412,965,975 'agent':405,954 'alter':125,541,665,706 'altern':594 'alway':24,104,410,490,505,521,537,582,941,963 'analyt':977 'app':374 'appli':354,867 'architectur':408 'audit':944 'avail':59,960 'availability.md':409 'avoid':207 'azur':47,913,969,994 'backup':398 'backup/restore':950 'base':593,598 'baselin':1015 'batch':981 'begin':143,147,174,638,640,725,729,779 'build':355 'c':742 'c.customerid':744 'c.customername':735 'call':789 'caller':690 'case':1044 'catch':175,178,641,780,783 'checkpoint':482,606 'claus':658 'cli':989 'cluster':184,193,203 'code':30,375,611,849 'column':456,584,647,929 'columnstor':976,979 'common':227,650 'complet':488 'complex':348 'comput':928 'computed-column':927 'concaten':426,504,622 'concept':345 'configur':958 'conn':76,98,100,794 'conn.close':842 'conn.cursor':103,817 'connect':17,62,69,372,902,911 'connections.md':376 'connector':29 'consid':485 'contain':45,974 'convers':655 'count':439 'cover':51,204,453,649 'creat':123,192,210,229,539,663,670,675,704 'cte':247 'ctes':351,865 'ctrl':473 'cursor':102,588,816,884 'cursor.close':841 'cursor.execute':108,818 'cursor.fetchall':118,835 'custom':683,741 'customerid':115,129,156,157,215,218,255,263,273,710,748,822 'data':383,396,660,939,995 'databas':39,88,806 'date':132,685,713,716 'dbatool':997 'dbcc':402,951 'dbo.usp':127,708,820 'deadlock':901 'default':136 'defin':189 'deliv':608 'desc':168,267,324,766 'detail':846,1047 'develop':55 'dml':443,633 'dmv':955 'dmvs':403 'document':855 'done':517 'dr':407,961 'driver':78,80,379,796,798,910 'drop':669 'drop/create':546 'duplic':1025 'dynam':395,885,938 'edg':1043 'edit':8 'elimin':458 'encrypt':94,812,942 'end':172,177,179,777,782,784 'enddat':715,757,762 'engin':40 'error':525,702,881 'exact':662 'exampl':673,850 'exec':819 'execut':21,368,465,891 'exist':595 'explicit':445,585 'extern':423 'faster':604 'fci':413,966 'fetch':330,772 'file':12 'filter':222 'first':631 'focus':1037 'follow':854 'function':250,353,864,880 'general':1029 'generic':1020 'geo':972 'geo-repl':971 'getcustomerord':128 'github.com':1032 'github.com/cofin/flow/blob/main/templates/styleguides/general.md)':1031 'go':180,342,785,909 'guardrail':489 'guid':847 'ha':406 'hallengren':1001 'handl':526,703,882 'high':58,959 'htap':983 'ident':916 'identifi':338 'implement':416 'implicit':654 'import':74,792 'in-memori':985 'includ':206,219,455,645,646 'index':23,181,185,194,205,212,223,225,231,454,644,844,895,930,980 'inlin':551 'inner':739 'input':424,615 'insert':306 'instead':544,667 'int':130,138,711,719,722 'integr':1046 'interfer':520 'inventori':285 'ix':195,213,232 'java':908 'join':740 'json':26,382,385,389,917,922,926,931 'json.md':384 'keep':1034 'key':208,241,344,459 'languag':66 'language/framework':1021 'learn.microsoft.com':1005,1008,1011 'learn.microsoft.com/en-us/azure-data-studio/':1010 'learn.microsoft.com/en-us/sql/sql-server/':1004 'learn.microsoft.com/en-us/sql/t-sql/language-reference':1007 'lock':390 'log':967 'lookup':209,460 'm':474 'magnitud':603 'maintain':399 'mainten':952 'major':65 'manag':915 'mask':397,940 'match':296,304,659 'memori':987 'merg':281,283,866 'messag':440 'microsoft':33 'mode':982 'monitor':400,956 'multi':555,562 'multi-stat':554,561 'mydb':89,807 'mypassword':93,811 'myserv':804 'myserver.database.windows.net':86 'myuser':91,809 'need':341 'net':907 'network':513 'never':425,502,576,586 'next':331,773 'nocount':145,361,434,507,627,727 'node.js':906 'non':202 'non-clust':201 'nonclust':211,230 'null':133,161,714,717,752,759 'number':259,571 'o':738 'o.customerid':745,747 'o.orderdate':733,754,761,765 'o.orderid':732 'o.total':734 'odbc':79,797 'offici':1002 'offset':312,325,767 'offset/fetch':568 'ola':1000 'oltp':988 'on-premis':42 'one':186 'openjson':387,924 'oper':599,634 'option':134 'order':113,154,165,191,196,199,214,217,233,236,264,271,320,321,601,681,737,763 'orderd':151,163,167,197,200,220,317,323 'orderid':110,150,256,274,316 'output':139,140 'pagenum':327,718,769,824 'pages':326,332,721,768,774,825 'pagin':313,574,871 'parallel':566 'param':497 'paramet':141,899 'parameter':106,377,419,492,618,692,788 'partial':224 'partit':261 'pass':616 'pattern':182,245,340,652,861,903,984 'per':187 'perform':56,888 'performance.md':367 'permiss':549,947 'physic':190 'pivot':868 'placehold':494 'plan':22,369,466,892 'prefer':550,567 'premis':44 'preserv':548 'prevent':516,565 'principl':1030 'print':836 'proc':519 'procedur':20,120,126,358,431,511,528,624,678,699,707,873,879 'product':580 'productid':307 'program':877 'proper':701 'pwd':92,810 'pyodbc':72,75,496,791,793 'pyodbc.connect':99,795 'python':71,73,689,786,787,905 'qti':308 'queri':107,228,349,364,370,378,420,493,581,619,651,693,893 'quick':67 'raiserror':532 'rang':686 'rankedord':252,277 're':535 're-throw':534 'reduc':512,1024 'refer':68,843,851,857,1003 'references/admin.md':949 'references/availability.md':962 'references/columnstore.md':978 'references/connections.md':904 'references/json.md':921 'references/performance.md':890 'references/security.md':936 'references/sqlcmd.md':991 'references/stored_procedures.md':878 'references/tsql_patterns.md':862 'relat':38 'replic':973 'rls':394,937 'role':945 'row':117,258,329,333,438,570,771,775,833 'row.customername':840 'row.orderdate':838 'row.orderid':837 'row.total':839 'rowcount':171 'rownum':269,279 'rule':1022 'run':478 'scalar':559 'search':680 'searchord':709,821 'secur':27,60,935 'security.md':393 'select':109,149,254,272,315,578,731 'server':10,16,32,35,84,85,610,802,803,920,957 'set':144,169,299,360,433,467,506,592,597,626,726 'set-bas':591,596 'setup':70 'share':1013,1017 'ship':968 'simpl':573 'skill':50,1028,1036 'skill-sqlserver' 'slow':366 'snif':900 'source-cofin' 'sp':998 'sp1':543 'span':41 'specif':1041 'specifi':583 'sql':7,9,11,15,31,34,48,54,83,122,183,244,246,404,501,609,694,697,801,860,876,886,919,953,970 'sqlcmd':14,992 'sqlserver':1 'src':291 'src.productid':294,310 'src.qty':301,311 'ssms':472,993 'start':429 'startdat':131,159,164,712,750,755,823 'stat':898 'statement':556,563,632 'statist':468 'status':237,239 'step':336,414,475 'store':19,119,357,371,430,510,623,677,698,872,894 'stored_procedures.md':359 'str':77,101 'strategi':896 'string':18,427,503,621,912 'studio':996 'styleguid':1014,1018 'support':672 'suppress':437 't-sql':5,52,242,499,695,858,874 'tabl':188,870 'task':674 'tde':943 'templat':121 'tempor':869 'test':462 'tgt':287 'tgt.productid':293 'tgt.qty':300 'throw':176,450,530,536,643,781 'tool':990,1040 'tool-specif':1039 'topic-agent-skills' 'topic-ai-agents' 'topic-beads' 'topic-claude-code' 'topic-codex' 'topic-cursor' 'topic-developer-tools' 'topic-gemini-cli' 'topic-opencode' 'topic-plugin' 'topic-slash-commands' 'topic-spec-driven-development' 'total':111,152,221,257,266,275,318 'totalcount':137,170 'traffic':514 'transact':446,883 'tri':148,173,639,730,778 'trigger':887 'trustservercertif':96,814 'try/catch':363,448,523 'tsql_patterns.md':350 'tune':57,889 'tvfs':552,557,564 'type':661,932 'udf':560 'uid':90,808 'unless':589 'updat':289,298 'upsert':282 'use':2,13,105,288,418,491,522,529,538,577,587,666,691,1016 'user':614 'valid':477,481,605 'valu':309,386,923 'verifi':612 'via':790 'wait':897 'whoisact':999 'window':249,352,863 'work':380,487 'workflow':335,1042 'wrap':442,636 'write':4,346 'xml':469 'yes':95,813","prices":[{"id":"95874e6d-ced1-4a95-8e12-1b88aa47e906","listingId":"8786e57f-d1e4-4a27-88ff-8a6cfd2eb882","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"cofin","category":"flow","install_from":"skills.sh"},"createdAt":"2026-04-23T13:04:01.926Z"}],"sources":[{"listingId":"8786e57f-d1e4-4a27-88ff-8a6cfd2eb882","source":"github","sourceId":"cofin/flow/sqlserver","sourceUrl":"https://github.com/cofin/flow/tree/main/skills/sqlserver","isPrimary":false,"firstSeenAt":"2026-04-23T13:04:01.926Z","lastSeenAt":"2026-05-18T19:07:39.710Z"}],"details":{"listingId":"8786e57f-d1e4-4a27-88ff-8a6cfd2eb882","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"cofin","slug":"sqlserver","github":{"repo":"cofin/flow","stars":11,"topics":["agent-skills","ai-agents","beads","claude-code","codex","context-driven-development","cursor","developer-tools","gemini-cli","opencode","plugin","slash-commands","spec-driven-development","subagents","tdd","workflow"],"license":"apache-2.0","html_url":"https://github.com/cofin/flow","pushed_at":"2026-04-27T19:07:26Z","description":"Context-Driven Development toolkit for AI agents — spec-first planning, TDD workflow, and Beads integration.","skill_md_sha":"56441b3e328a7fac9df7d9008b6371faabafb65c","skill_md_path":"skills/sqlserver/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/cofin/flow/tree/main/skills/sqlserver"},"layout":"multi","source":"github","category":"flow","frontmatter":{"name":"sqlserver","description":"Use when writing T-SQL, editing SQL Server .sql files, using sqlcmd, SQL Server connection strings, stored procedures, execution plans, indexes, Always On, JSON, security, or connector code."},"skills_sh_url":"https://skills.sh/cofin/flow/sqlserver"},"updatedAt":"2026-05-18T19:07:39.710Z"}}