{"id":"8786e57f-d1e4-4a27-88ff-8a6cfd2eb882","shortId":"ZtjjbR","kind":"skill","title":"sqlserver","tagline":"Auto-activate for T-SQL patterns, sqlcmd, SQL Server connection strings. Produces T-SQL queries, stored procedures, indexing strategies, and SQL Server connection patterns. Use when: writing T-SQL queries, optimizing execution plans, configuring SQL Server, setting up Always On A","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-04-24T07:03:20.255Z","embedding":null,"createdAt":"2026-04-23T13:04:01.926Z","updatedAt":"2026-04-24T07:03:20.255Z","lastSeenAt":"2026-04-24T07:03:20.255Z","tsv":"'-01':844,845 '/2025':950 '/cofin/flow/blob/main/templates/styleguides/general.md)':1049 '/en-us/azure-data-studio/':1028 '/en-us/sql/sql-server/':1022 '/en-us/sql/t-sql/language-reference':1025 '1':296,344,353,433,736,786,846 '1433':103,821 '18':97,815 '2':431,444 '2012':330,591 '2016':558 '2022':949 '2025':843 '25':847 '3':457,492 '4':467 '42':132,842 '5':477 '50':739 'access':408 'across':79 'activ':4,250,256 'actual':480 'ad':930 'add':468 'admin.md':417 'administr':964 'ag':428,981,991 'agent':421,970 'alter':141,557,681,722 'altern':610 'alway':44,120,426,506,521,537,553,598,957,979 'analyt':993 'app':390 'appli':370,883 'architectur':424 'audit':960 'auto':3 'auto-activ':2 'avail':75,976 'availability.md':425 'avoid':223 'azur':63,929,985,1010 'backup':414 'backup/restore':966 'base':609,614 'baselin':1031 'batch':997 'begin':159,163,190,654,656,741,745,795 'build':371 'c':758 'c.customerid':760 'c.customername':751 'call':805 'caller':706 'case':1060 'catch':191,194,657,796,799 'checkpoint':498,622 'claus':674 'cli':1005 'cluster':200,209,219 'code':391,627,865 'column':472,600,663,945 'columnstor':992,995 'common':243,666 'complet':504 'complex':364 'comput':944 'computed-column':943 'concaten':442,520,638 'concept':361 'configur':39,974 'conn':92,114,116,810 'conn.close':858 'conn.cursor':119,833 'connect':13,27,78,85,388,918,927 'connections.md':392 'consid':501 'contain':61,990 'convers':671 'count':455 'cover':67,220,469,665 'creat':139,208,226,245,555,679,686,691,720 'cte':263 'ctes':367,881 'ctrl':489 'cursor':118,604,832,900 'cursor.close':857 'cursor.execute':124,834 'cursor.fetchall':134,851 'custom':699,757 'customerid':131,145,172,173,231,234,271,279,289,726,764,838 'data':399,412,676,955,1011 'databas':55,104,822 'date':148,701,729,732 'dbatool':1013 'dbcc':418,967 'dbo.usp':143,724,836 'deadlock':917 'default':152 'defin':205 'deliv':624 'desc':184,283,340,782 'detail':862,1063 'develop':71 'dml':459,649 'dmv':971 'dmvs':419 'document':871 'done':533 'dr':423,977 'driver':94,96,395,812,814,926 'drop':685 'drop/create':562 'duplic':1041 'dynam':411,901,954 'edg':1059 'elimin':474 'encrypt':110,828,958 'end':188,193,195,793,798,800 'enddat':731,773,778 'engin':56 'error':541,718,897 'exact':678 'exampl':689,866 'exec':835 'execut':37,384,481,907 'exist':611 'explicit':461,601 'extern':439 'faster':620 'fci':429,982 'fetch':346,788 'filter':238 'first':647 'focus':1053 'follow':870 'function':266,369,880,896 'general':1045 'generic':1036 'geo':988 'geo-repl':987 'getcustomerord':144 'github.com':1048 'github.com/cofin/flow/blob/main/templates/styleguides/general.md)':1047 'go':196,358,801,925 'guardrail':505 'guid':863 'ha':422 'hallengren':1017 'handl':542,719,898 'high':74,975 'htap':999 'ident':932 'identifi':354 'implement':432 'implicit':670 'import':90,808 'in-memori':1001 'includ':222,235,471,661,662 'index':22,197,201,210,221,228,239,241,247,470,660,860,911,946,996 'inlin':567 'inner':755 'input':440,631 'insert':322 'instead':560,683 'int':146,154,727,735,738 'integr':1062 'interfer':536 'inventori':301 'ix':211,229,248 'java':924 'join':756 'json':398,401,405,933,938,942,947 'json.md':400 'keep':1050 'key':224,257,360,475 'languag':82 'language/framework':1037 'learn.microsoft.com':1021,1024,1027 'learn.microsoft.com/en-us/azure-data-studio/':1026 'learn.microsoft.com/en-us/sql/sql-server/':1020 'learn.microsoft.com/en-us/sql/t-sql/language-reference':1023 'lock':406 'log':983 'lookup':225,476 'm':490 'magnitud':619 'maintain':415 'mainten':968 'major':81 'manag':931 'mask':413,956 'match':312,320,675 'memori':1003 'merg':297,299,882 'messag':456 'microsoft':49 'mode':998 'monitor':416,972 'multi':571,578 'multi-stat':570,577 'mydb':105,823 'mypassword':109,827 'myserv':820 'myserver.database.windows.net':102 'myuser':107,825 'need':357 'net':923 'network':529 'never':441,518,592,602 'next':347,789 'nocount':161,377,450,523,643,743 'node.js':922 'non':218 'non-clust':217 'nonclust':227,246 'null':149,177,730,733,768,775 'number':275,587 'o':754 'o.customerid':761,763 'o.orderdate':749,770,777,781 'o.orderid':748 'o.total':750 'odbc':95,813 'offici':1018 'offset':328,341,783 'offset/fetch':584 'ola':1016 'oltp':1004 'on-premis':58 'one':202 'openjson':403,940 'oper':615,650 'optim':36 'option':150 'order':129,170,181,207,212,215,230,233,249,252,280,287,336,337,617,697,753,779 'orderd':167,179,183,213,216,236,333,339 'orderid':126,166,272,290,332 'output':155,156 'pagenum':343,734,785,840 'pages':342,348,737,784,790,841 'pagin':329,590,887 'parallel':582 'param':513 'paramet':157,915 'parameter':122,393,435,508,634,708,804 'partial':240 'partit':277 'pass':632 'pattern':9,28,198,261,356,668,877,919,1000 'per':203 'perform':72,904 'performance.md':383 'permiss':565,963 'physic':206 'pivot':884 'placehold':510 'plan':38,385,482,908 'prefer':566,583 'premis':60 'preserv':564 'prevent':532,581 'principl':1046 'print':852 'proc':535 'procedur':21,136,142,374,447,527,544,640,694,715,723,889,895 'produc':15 'product':596 'productid':323 'program':893 'proper':717 'pwd':108,826 'pyodbc':88,91,512,807,809 'pyodbc.connect':115,811 'python':87,89,705,802,803,921 'qti':324 'queri':19,35,123,244,365,380,386,394,436,509,597,635,667,709,909 'quick':83 'raiserror':548 'rang':702 'rankedord':268,293 're':551 're-throw':550 'reduc':528,1040 'refer':84,859,867,873,1019 'references/admin.md':965 'references/availability.md':978 'references/columnstore.md':994 'references/connections.md':920 'references/json.md':937 'references/performance.md':906 'references/security.md':952 'references/sqlcmd.md':1007 'references/stored_procedures.md':894 'references/tsql_patterns.md':878 'relat':54 'replic':989 'rls':410,953 'role':961 'row':133,274,345,349,454,586,787,791,849 'row.customername':856 'row.orderdate':854 'row.orderid':853 'row.total':855 'rowcount':187 'rownum':285,295 'rule':1038 'run':494 'scalar':575 'search':696 'searchord':725,837 'secur':76,951 'security.md':409 'select':125,165,270,288,331,594,747 'server':12,26,41,48,51,100,101,626,818,819,936,973 'set':42,160,185,315,376,449,483,522,608,613,642,742 'set-bas':607,612 'setup':86 'share':1029,1033 'ship':984 'simpl':589 'skill':66,1044,1052 'skill-sqlserver' 'slow':382 'snif':916 'source-cofin' 'sp':1014 'sp1':559 'span':57 'specif':1057 'specifi':599 'sql':8,11,18,25,34,40,47,50,64,70,99,138,199,260,262,420,517,625,710,713,817,876,892,902,935,969,986 'sqlcmd':10,1008 'sqlserver':1 'src':307 'src.productid':310,326 'src.qty':317,327 'ssms':488,1009 'start':445 'startdat':147,175,180,728,766,771,839 'stat':914 'statement':572,579,648 'statist':484 'status':253,255 'step':352,430,491 'store':20,135,373,387,446,526,639,693,714,888,910 'stored_procedures.md':375 'str':93,117 'strategi':23,912 'string':14,443,519,637,928 'studio':1012 'styleguid':1030,1034 'support':688 'suppress':453 't-sql':6,16,32,68,258,515,711,874,890 'tabl':204,886 'task':690 'tde':959 'templat':137 'tempor':885 'test':478 'tgt':303 'tgt.productid':309 'tgt.qty':316 'throw':192,466,546,552,659,797 'tool':1006,1056 'tool-specif':1055 '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':127,168,237,273,282,291,334 'totalcount':153,186 'traffic':530 'transact':462,899 'tri':164,189,655,746,794 'trigger':903 'trustservercertif':112,830 'try/catch':379,464,539 'tsql_patterns.md':366 'tune':73,905 'tvfs':568,573,580 'type':677,948 'udf':576 'uid':106,824 'unless':605 'updat':305,314 'upsert':298 'use':29,121,304,434,507,538,545,554,593,603,682,707,1032 'user':630 'valid':493,497,621 'valu':325,402,939 'verifi':628 'via':806 'wait':913 'whoisact':1015 'window':265,368,879 'work':396,503 'workflow':351,1058 'wrap':458,652 'write':31,362 'xml':485 'yes':111,829","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-04-24T07:03:20.255Z"}],"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-19T23:22:27Z","description":"Context-Driven Development toolkit for AI agents — spec-first planning, TDD workflow, and Beads integration.","skill_md_sha":"e556498676ca571b728e5346ce8e74c6c8815a07","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":"Auto-activate for T-SQL patterns, sqlcmd, SQL Server connection strings. Produces T-SQL queries, stored procedures, indexing strategies, and SQL Server connection patterns. Use when: writing T-SQL queries, optimizing execution plans, configuring SQL Server, setting up Always On AG, using sqlcmd/SSMS, or working with SQL Server connectors (Python, Node, .NET, JDBC). Not for PostgreSQL (see postgres), MySQL (see mysql), or Azure-specific managed services."},"skills_sh_url":"https://skills.sh/cofin/flow/sqlserver"},"updatedAt":"2026-04-24T07:03:20.255Z"}}