Comparing changes

Signed-off-by: Zi Chen <[email protected]>

* Merged PR 1696252: Changes to support Manual-cutover and Perform-cutover in ScriptDOM ## Description The changes are done to support new parameters in TSQL ALTER DATABASE command to add new parameters MANUAL_CUTOVER and PERFORM_CUTOVER. **Functional spec** of feature FMv2: https://microsoft-my.sharepoint-df.com/:w:/p/blakhani/ERKCMOUx59ZIjfSJ_eIyLfQB--YQbcRDPoSNiiUtWvS1bg?e=vWg0vP **Design doc** of feature FMv2: https://microsoft.sharepoint.com/:w:/t/sqlstormr/Eb0o3p3MNB1CmdS7NByqHkQB11bGrU9PNVg5GE98Z6yhhg?e=reyHi3 ## Code Change - [X] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [X] Code changes are accompanied by appropriate unit tests - [X] Identified and included SMEs needed to review code changes - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature Newly added tests are passing successfully: ![image.png](https://msdata.visualstudio.com/c6789c20-b819-4bfd-9917-11471655156e/_apis/git/repositories/2247f543-55d8-45df-a9fe-23820ae656af/pullRequests/1696252/attachments/image.png) Ran all tests which are passing successfully: ![image.png](https://msdata.visualstudio.com/c6789c20-b819-4bfd-9917-11471655156e/_apis/git/repositories/2247f543-55d8-45df-a9fe-23820ae656af/pullRequests/1696252/attachments/image.png) ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers ---- #### AI description (iteration 1) #### PR Classification New feature implementation to support manual and perform cutover options in ScriptDOM. #### PR Summary This pull request extends ScriptDOM to accommodate the MANUAL_CUTOVER and PERFORM_CUTOVER options as required for the Forward Migration v2 feature. The enhancements include grammar updates, AST modifications, option kind extensions, code generation adjustments, and new SQL tests. - **`TSql170.g`**: Added production rules for `alterDbManualCutover` and `alterDbPerformCutover` to parse the new options. - **`Ast.xml`**: Introduced new classes (`ManualCutoverDatabaseOption` and `PerformCutoverDatabaseOption`) to represent the cutover options. - **`DatabaseOptionKind.cs` & Helpers**: Extended the enum and updated mappings in `DatabaseOptionKindHelper.cs` and `OnOffSimpleDbOptionsHelper.cs` with the new option kinds. - **`CodeGenerationSupporter.cs` & `SqlVersionFlags.cs`**: Incorporate... * Merged PR 1704129: support ORDER clause for NonClustered ColumnStore Index ## Description Support Order clause on NONClustered ColumnStore Index. CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON T(A, B, C) ORDER(A, B); CREATE COLUMNSTORE INDEX idx_ntccitrim_prim2 ON dbo.nttrim_prim (i1, c1, nvc1) ORDER (i1, c1); We already support CLUSTERED COLUMNSTORE INDEX Order clause, we need add support for NonClustered right now. ## Code Change - [x] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [x] Code changes are accompanied by appropriate unit tests - [x] Identified and included SMEs needed to review code changes - [x] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [x] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ![image.png](https://msdata.visualstudio.com/c6789c20-b819-4bfd-9917-11471655156e/_apis/git/repositories/2247f543-55d8-45df-a9fe-23820ae656af/pullRequests/1704129/attachments/image.png) ## Documentation - [x] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers support ORDER clause for NonClustered ColumnStore Index ---- #### AI description (iteration 1) #### PR Classification New feature enabling the ORDER clause for both clustered and non-clustered columnstore indexes. #### PR Summary This pull request enhances the SQL script generator to support the ORDER clause for columnstore indexes and adds tests to verify the new syntax. - `SqlScriptGeneratorVisitor.CreateColumnStoreIndexStatement.cs`: Modified condition to generate ordered columns for both clustered and non-clustered indexes. - `Only170SyntaxTests.cs`: Updated test suite to include a new parser test for the CreateColumnStoreIndex syntax. - `Baselines170/CreateColumnStoreIndexTests170.sql` & `TestScripts/CreateColumnStoreIndexTests170.sql`: Added new test files to validate the ORDER clause support.  Related work items: #3876240 * Merged PR 1702087: Identity Column - Fabric DW In this PR we delete options for Identity Columns in TSqlFabricDW.g since they are not supported by the Fabric SQL engine. i.e. Fabric warehouse does not support seed and increment.. Hence the supported TSQL syntax would change from: `IDENTITY [ (seed , increment) ]` to the new syntax: `CREATE TABLE table1 ( ID BIGINT IDENTITY );` Related work items: #4314344 * [Security] Update .NET SDK to latest patch version * Fix test on Linux --------- Co-authored-by: Mohit Agarwal <[email protected]> Co-authored-by: May Wen <[email protected]> Co-authored-by: Dhruv Relwani <[email protected]> Co-authored-by: MerlinBot <MerlinBot>

…0) (microsoft#147) * Initial plan * Initial planning and exploration for JSON Index support Co-authored-by: llali <[email protected]> * Add JSON Index support - AST definitions, grammar rules, and script generation Co-authored-by: llali <[email protected]> * Complete JSON Index implementation - fix parser recognition and add correct baseline Co-authored-by: llali <[email protected]> * Final fix - correct error count expectations for older SQL parser versions Co-authored-by: llali <[email protected]> * Remove antlr-2.7.5.jar file from repository Co-authored-by: llali <[email protected]> * Remove tmp folder from repository and add to .gitignore Co-authored-by: llali <[email protected]> * Revert global.json SDK version back to 8.0.410 Co-authored-by: llali <[email protected]> * Remove NUL file - ANTLR build artifact Co-authored-by: llali <[email protected]> * Use CreateXmlIndexStatement pattern - inherit from IndexStatement instead of TSqlStatement Co-authored-by: llali <[email protected]> * Address reviewer feedback: rename JsonPaths to ForJsonPaths and add comprehensive error tests Co-authored-by: zijchen <[email protected]> * Fix error test position calculations for accurate error reporting Co-authored-by: zijchen <[email protected]> * Change JSON Index column from ColumnReferenceExpression to Identifier type Co-authored-by: llali <[email protected]> * Remove ErrorTest90/ErrorTest100 calls and fix missing comma in Only170SyntaxTests Co-authored-by: llali <[email protected]> * Fix parser error test offsets - correct positions for JSON keyword errors Co-authored-by: llali <[email protected]> * Remove ErrorTest80 call from ParserErrorsTests as requested Co-authored-by: llali <[email protected]> * Remove ErrorTest160 calls from ParserErrorsTests for JSON Index - only TSql170 tests needed Co-authored-by: zijchen <[email protected]> * fixing the test * Remove filegroup support from JSON Index syntax Co-authored-by: llali <[email protected]> * Fix JSON Index error count in Only170SyntaxTests - correct to 8 errors after filegroup removal Co-authored-by: llali <[email protected]> --------- Co-authored-by: copilot-swe-agent[bot] <[email protected]> Co-authored-by: llali <[email protected]> Co-authored-by: zijchen <[email protected]> Co-authored-by: Leila Lali <[email protected]>

…ex option classes (microsoft#150) * Initial plan * Implement vector index support - add grammar, AST classes, and script generators Co-authored-by: llali <[email protected]> * Consolidate vector index options and remove duplicate Vector constant Co-authored-by: llali <[email protected]> * Revert to original design with separate VectorMetricIndexOption and VectorTypeIndexOption classes Co-authored-by: llali <[email protected]> * fixing the parser * Update vector index option values to uppercase in baseline test file Co-authored-by: llali <[email protected]> * Add comprehensive vector index error test cases to ParserErrorsTests Co-authored-by: zijchen <[email protected]> * Remove invalid negative test case for vector index - CREATE INDEX syntax is valid for regular indexes Co-authored-by: zijchen <[email protected]> * fixing the test --------- Co-authored-by: copilot-swe-agent[bot] <[email protected]> Co-authored-by: llali <[email protected]> Co-authored-by: Leila Lali <[email protected]> Co-authored-by: zijchen <[email protected]>

* Merged PR 1735207: Adding regex functions tests # Pull Request Template for ScriptDom ## Description This PR adds tests for regex intrinsic functions. Functional Spec - https://microsoft.sharepoint.com/:w:/t/Ad-Astra/EZAk15L-vkJHqsaCybly0QIB0eBUdv02BBaHUAl48aY_Pw?e=NPRj0w ## Code Change - [x] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [x] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [x] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [x] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information None Adding regex functions tests Related work items: #4005966 * Merged PR 1718694: [ai_generate_chunks] Add syntax to ScriptDom # Pull Request Template for ScriptDom ## Description # Add syntax and script generation support for AI_GENERATE_CHUNKS table-valued function ## Description This PR introduces parser and script generator support for the new built-in table-valued function (TVF) `AI_GENERATE_CHUNKS`. `AI_GENERATE_CHUNKS` enables chunking of input text based on a specified strategy. It accepts required and optional parameters and supports aliasing for cross apply scenarios. ### Syntax supported ```sql SELECT * FROM AI_GENERATE_CHUNKS ( source = 'some text', chunk_type = fixed, chunk_size = 5, overlap = 2, enable_chunk_set_id = 1 ); ``` ### Parameters | Parameter | Required | Type | Description | |------------------------|----------|-------------------|--------------------------------------------------------------------| | `source` | Yes | `ScalarExpression`| Input text to be chunked | | `chunk_type` | Yes | `Identifier` | Type of chunking strategy. Currently only `fixed` is supported | | `chunk_size` | Yes | `ScalarExpression`| Number of tokens per chunk | | `overlap` | No | `ScalarExpression`| Optional token overlap between adjacent chunks | | `enable_chunk_set_id` | No | `ScalarExpression`| Optional flag. If set, adds a `chunk_set_id` column to the result | ### Summary of Changes - Added support for the `ai_generate_chunks` table-valued function to the SQL parser. - Introduced new AST class `AiGenerateChunksTableReference` with members: - `Source` - `ChunkType` - `ChunkSize` - `Overlap` - `EnableChunkSetId` - Added grammar rule for parsing `ai_generate_chunks`. - Implemented corresponding visitor logic for script generation (`SqlScriptGeneratorVisitor`). - Added unit tests for: - Positive cases covering all combinations of optional parameters. - Negative cases for missing/invalid parameter order, unexpected tokens, or malformed expressions. ### Example Use Cases This function is designed to support downstream AI workflows where long documents need to be split into manageable token chunks for embedding generation or other ML tasks. ## Code Change - [X] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [X] Code changes are accompanied by appropriate unit tests - [X] Identified and included SMEs needed to review code changes - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [X] Follow the [steps](https://msdata.vi... * Update .NET SDK to latest patch version * Merged PR 1724944: [MSJSON] added support for json_objectagg and Returning JSON option for json_object, json_array # Pull Request Template for ScriptDom ## Description adds support to scriptdom for new functions and options in SQLServer. Specifically support for - Json_objectagg with options NULL ON NULL/ABSENT ON NULL and RETURNING JSON. - Support for RETURNING JSON option for json_object, json_array. Added relevant test for all options. ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [X] Code changes are accompanied by appropriate unit tests - [X] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature Added Test. * Merged PR 1724727: [ai_generate_embeddings] Add syntax to ScriptDom # Pull Request Template for ScriptDom ## Description This PR adds support for the new `AI_GENERATE_EMBEDDINGS` T-SQL built-in function syntax: ```sql AI_GENERATE_EMBEDDINGS('input text' USE MODEL model_name [PARAMETERS (...optional JSON...)]) ``` Input expression: required string or expression representing the text to embed. USE MODEL model_name: required keyword clause specifying the embedding model. PARAMETERS ( ... ): optional block allowing additional JSON parameters to customize embedding behavior. ### Examples ```sql -- Basic usage with required input and model SELECT AI_GENERATE_EMBEDDINGS('Hello world' USE MODEL MyEmbeddingModel); -- Usage with optional PARAMETERS block SELECT AI_GENERATE_EMBEDDINGS('Hello world' USE MODEL MyEmbeddingModel PARAMETERS (TRY_CONVERT(JSON, N'{"param":"value"}'))); ``` ### Features - Adds `AiGenerateEmbeddingsFunctionCall` AST node. - Grammar recognizes required `USE MODEL` clause and optional `PARAMETERS` block. - Script generator visitor outputs syntax matching expected SQL Server formatting, with or without optional PARAMETERS. - Comprehensive unit tests cover: - Missing required keywords or values (e.g., missing USE, missing MODEL). - Misplaced keywords (e.g., PARAMETERS before input). - Invalid syntax like missing parentheses or incomplete expressions. - Extra commas or unknown parameters. - Negative tests aligned with expected SQL46010 and SQL46029 parser errors. - Supports round-tripping: parsed AST can be pretty-printed back to valid SQL matching the original input. ### Compatibility - Valid only in SQL Server with compatibility level 170 or higher. ## Code Change - [X] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [X] Code changes are accompanied by appropriate unit tests - [X] Identified and included SMEs needed to review code changes - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ---- #### AI description (iteration 1) #### PR Classification New feature implementation to add syntax support for the AI_GENERATE_EMBEDDINGS function in ScriptDom. #### PR Summary This PR introduces a new SQL function, AI_GENERATE_EMBEDDINGS, which allows users to send input text to external AI models and retrieve an em... * Merged PR 1752033: [modle_management]ScriptDom changes for external model support # Pull Request Template for ScriptDom ## Description Introduces syntax support for Create, Alter and Drop operation for External Model DDL CREATE CREATE EXTERNAL MODEL <model_name> AUTHORIZATION owner WITH ( LOCATION = 'URL or FILE PATH', API_FORMAT = ‘OpenAI, Azure OpenAI, etc’, MODEL_TYPE = <EMBEDDINGS,CHAT, etc>, MODEL = ‘text-embedding-ada-002,etc’, CREDENTIAL = <credential_name>, PARAMETERS = ‘{ “valid”:”JSON”}’ } | **Column Name** | **Data Type** | **Notes** | |-----------------------|---------------------|-----------| | `model_name` | `SYSNAME` | Name of the external model object | | `owner_name` | `SYSNAME` | Database principal that owns the external model object. Defaults to connected user if not specified. | | `location` | `NVARCHAR(4000)` | Location of the endpoint. Can be a URL (`https://...`) or file path (`mountpoint:\\...`). | | `api_format` | `NVARCHAR(100)` | Format of the API the endpoint accepts/produces. P0: Azure OpenAI, OpenAI, Ollama; P1: Local, Nvidia Triton, Databricks; P2: TBD | | `model_type` | `SYSNAME` | Model type for the data plane. P0: EMBEDDINGS; Post GA: CHAT, MODERATE, TRANSLATE, SPEECH. Only supported types are accepted. | | `model` | `NVARCHAR(100)` | Name of the embedding model used by the endpoint. | | `local_runtime_path` | `NVARCHAR(4000)` | Path to local model runtime (e.g., ONNX runtime). Required for local models. | | `credential` | `SYSNAME` | Name of the database scoped credential. Not used for ONNX Runtime. | | `parameters` | `JSON` | Default JSON parameters to be appended to the embeddings payload. | ## Code Change - [X] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [X] Code changes are accompanied by appropriate unit tests - [X] Identified and included SMEs needed to review code changes - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [X] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers ScriptDom channges ---- #### AI description (iteration 1) #### PR Classific... * Merged PR 1758352: Add ScriptDom support for VECTOR_SEARCH function # Pull Request Template for ScriptDom ## Description Added parser and script generator support for VECTOR_SEARCH Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers Add ScriptDom support for VECTOR_SEARCH function ---- #### AI description (iteration 1) #### PR Classification This pull request introduces a new feature by adding native ScriptDom support for the VECTOR_SEARCH function. #### PR Summary The changes implement parsing, AST definition, and script generation for VECTOR_SEARCH, along with a comprehensive suite of tests to validate error handling and syntax compliance. - **`SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/SqlScriptGeneratorVisitor.VectorSearchTableReference.cs`**: New file added to generate script output for the VECTOR_SEARCH function. - **`SqlScriptDom/Parser/TSql/TSql170.g` & `SqlDom/Parser/TSql/Ast.xml`**: Extended grammar rules and AST definitions to support VECTOR_SEARCH. - **`SqlScriptDom/Parser/TSql/CodeGenerationSupporter.cs`**: Added new constants (e.g., COSINE, DOT, EUCLIDEAN, METRIC, TOP_N, VECTOR_SEARCH) required for vector search processing. - **`Test/SqlDom/ParserErrorsTests.cs` and related vector test scripts**: Introduced extensive tests covering syntax errors and parameter validations for VECTOR_SEARCH.  Related work items: #4440435 * Merged PR 1757817: Added EM Permission support ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers Added EM Permission support ---- #### AI description (iteration 1) #### PR Classification This PR implements a new feature by adding support for External Model permissions in ScriptDOM, enabling the parsing and generation of new permission-related T-SQL statements. #### PR Summary The pull request enhances ScriptDOM to correctly handle External Model permissions—covering GRANT, DENY, REVOKE, and ALTER AUTHORIZATION operations—and integrates comprehensive tests and token generation support to improve IntelliSense and syntax-highlighting as outlined in the linked work items. - **`Test/SqlDom/ExternalModelPermissionUnitTests.cs`**: Adds unit tests for External Model permission statements, including backward compatibility checks. - **`Test/SqlDom/Baselines170/SecurityStatementExternalModelTests170.sql` & `Test/SqlDom/TestScripts/SecurityStatementExternalModelTests170.sql`**: Introduce baseline and test scripts with various External Model permission scenarios. - **`SqlScriptDom/Parser/TSql/TSql170ParserBaseInternal.cs` & `SqlScriptDom/Parser/TSql/SecurityObjectKind.cs`**: Update parsing logic and the security object enumeration to recognize and handle External Model keywords. - **`SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/SqlScriptGeneratorVisitor.SecurityTargetObject.cs`**: Enhances token generation to support the External Model permission syntax.  Related work items: #4088625, #4088633 * Update .NET SDK to latest patch version (8.0.413) * Merged PR 1764425: Adding release notes for 170.82.0 # Pull Request Template for ScriptDom ## Description Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers Adding release notes for 170.82.0 * remove extras * fixing the build --------- Co-authored-by: C Shanmukha Reddy <[email protected]> Co-authored-by: Sicong Liu <[email protected]> Co-authored-by: MerlinBot <MerlinBot> Co-authored-by: Shiv Prashant Sood <[email protected]> Co-authored-by: Urvashi Raj <[email protected]> Co-authored-by: Zi Chen <[email protected]>

* Merged PR 1756293: [MSJSON] json_arrayagg support for scriptdom ## Description This pull request introduces full support for the JSON_ARRAYAGG function in ScriptDom by extending the T-SQL grammar and code generation, along with updated test cases to validate the new functionality. Json_ArrayAgg with options NULL ON NULL/ABSENT ON NULL and RETURNING JSON are supported. * Merged PR 1777076: Adding missing event CREATE_JSON_INDEX # Pull Request Template for ScriptDom ## Description Adding missing JSON index event to the event type enum. Adding missing event CREATE_JSON_INDEX * Merged PR 1781082: Adding regexp_like syntax Regexp_like function works as a boolean expression similar to LIKE, MATCH starting from compat level 170. Adding regexp_like syntax Related work items: #4005966 * Merged PR 1762947: Nested CTE Implementation - Fabric DW In this PR we modify the Fabric DW Grammar to support Nested Common Table Expressions with the following syntax: ``` WITH <NESTED_CTE_NAME_LEVEL1> [ (column_name , ...) ] AS (WITH <NESTED_CTE_NAME_LEVEL2> [ (column_name , ...) ] AS ( ... WITH <NESTED_CTE_NAME_LEVELn-1> [ ( column_name , ...) ] AS ( WITH <NESTED_CTE_NAME_LEVELn> [ ( column_name , ...) ] AS ( Standard_CTE_query_definition ) <SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn ) <SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn-1 ... ) <SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVEL2 ) ``` This is derived from public [Nested CTE Documentation](https://learn.microsoft.com/en-us/sql/t-sql/queries/nested-common-table-expression?view=sql-server-ver16). Work Item: https://dev.azure.com/powerbi/AI/_workitems/edit/1672555/ * Merged PR 1785976: [AI_GENERATE_CHUNKS] ENABLE_CHUNK_SET_ID to Integer # Pull Request Template for ScriptDom ## Description This PR changes the optional parameter ENABLE_CHUNK_SET_ID of AI_GENERATE_CHUNKS from expression to integer/null only. * Merged PR 1780003: Fixed a bug when parsing json keyvalue parameter Some of json functions accept json key value and the key value can be with or without single quote. The problem comes when the parser assumes any name and ':' should be parsed as a label. and label are not supported as function parameters. since it's not easy to differentiate between a label and json value, had to change the parser to accept label as function parameter and then parse it as column reference. Fixed a bug when parsing json keyvalue parameter * Merged PR 1787157: Adding release notes for 170.100 Adding release notes for 170.0.96

Tidy up links and add link to release notes Signed-off-by: Erik Ejlskov Jensen <[email protected]>

…ft#164)

…cate (microsoft#158)

* Fixing a bug with regex_Like function as boolean exp * Adding more test

…uter references (microsoft#167)

* Merged PR 1790439: Remove unused variable in Antlr grammar file TSql170 # Pull Request Template for ScriptDom ## Description Remove unused local variable from _alterDbModifyAzureOptions_ grammar rule and ensure accurate token position tracking for WITH MANUAL_CUTOVER clause. Details: • Eliminated redundant _hasManualCutover_ variable. • Improved maintainability and clarity. • Ensured UpdateTokenInfo is called for position tracking. ## Code Change - [x] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [x] Code changes are accompanied by appropriate unit tests - [x] Identified and included SMEs needed to review code changes - [x] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [x] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature Re-ran all existing tests to ensure the code changes are not impacting anything. All tests are passing successfully: ![image.png](https://msdata.visualstudio.com/c6789c20-b819-4bfd-9917-11471655156e/_apis/git/repositories/2247f543-55d8-45df-a9fe-23820ae656af/pullRequests/1790439/attachments/image.png) ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers N/A. ---- #### AI description (iteration 1) #### PR Classification This pull request is a code cleanup that removes an unused variable and refactors manual cutover token handling in the TSql170 Antlr grammar file. #### PR Summary The changes remove the unused boolean variable in `SqlScriptDom/Parser/TSql/TSql170.g` and replace its assignment with a call to `UpdateTokenInfo` when processing the manual cutover identifier. - `SqlScriptDom/Parser/TSql/TSql170.g`: Removed the unnecessary declaration and assignment of the variable and added `UpdateTokenInfo` to update token information for manual cutover.  Related work items: #4005325 * Merged PR 1790607: Remove Unused Parameter form External Model # Pull Request Template for ScriptDom ## Description #### AI description (iteration 1) #### PR Classification Code cleanup: Removing an unused parameter from external model parser definitions. #### PR Summary This PR streamlines the T-SQL grammar by removing the unused parameter "encounteredOptions" from external model statement rules. - `SqlScriptDom/Parser/TSql/TSql170.g`: Removed `long encounteredOptions = 0;` from the `createExternalModelStatement` rule. - `SqlScriptDom/Parser/TSql/TSql170.g`: Removed `long encounteredOptions = 0;` from the `alterExternalModelStatement` rule. * Merged PR 1789319: Resend PR for vector type 2nd parameter Resending PR !1780528 which was reverted to unblock release Before submitting your pull request, please ensure you have completed the following: - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file Please provide any additional information that might be helpful for the reviewers Reverting commit cd69b7806dfbd6a581dbe50779a07ac70047cee8 to unblock release Before submitting your pull request, please ensure you have completed the following: - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file Please provide any additional information that might be helpful for the reviewers Revert cd69b7806dfbd6a581dbe50779a07ac70047cee8 ---- This PR temporarily reverts a previous commit to remove vector data type support. The pull request reverses vector-related changes by deleting files and code modification... * Merged PR 1792393: Add support for Optimized Locking database option As a result of the new [Optimized Locking](https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-locking?view=sql-server-ver17) feature, new DDL has been added: `ALTER DATABASE MyDb SET OPTIMIZED_LOCKING = ON/OFF`. This PR adds this DDL to the grammar. - [X] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [X] Code changes are accompanied by appropriate unit tests - [X] Identified and included SMEs needed to review code changes - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature - [X] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file Please provide any additional information that might be helpful for the reviewers ---- This pull request introduces a new feature by adding support for the Optimized Locking database option. The pull request implements the support for the Optimized Locking option in ALTER DATABASE statements by extending both the parser and code generation components. - `SqlScriptGeneratorVisitor.OptimizedLockingAlterDatabaseOption.cs`: Implements the visitor method to generate script text for the new option based on its state (ON/OFF). - `TSql170.g` and `TSqlFabricDW.g`: Add new grammar rules to parse the Optimized Locking option and integrate it into the database option parsing logic. - `Ast.xml`: Introduces a new AST class for the Optimized Locking database option. - `DatabaseOptionKind.cs` and `CodeGenerationSupporter.cs`: Update the enumeration and constant definitions to include the Optimized Locking option.  * Update .NET SDK to latest patch version (8.0.414) * Merged PR 1792484: Updating .md files Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue Before submitting your pull request, please ensure you have completed the following: - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file Please provide any additional information that might be helpful for the reviewers Updating .md files ---- Documentation update. This pull request revises and expands the project’s documentation to clarify developer workflows and update build instructions for SqlScriptDOM. - `/github/copilot-instructions.md` now features detailed sections on key points, build/test workflows, and common pitfalls. - A new file, `/github/BUG_FIXING_GUIDE.md`, has been added to provide a step-by-step guide for bug fixing in SqlScriptDOM. - `/README.md` and `/CONTRIBUTING.md` have been updated to use repository-relative paths and improve instructions for modifying the DOM, parser, and script generator.  * Merged PR 1804708: [ai_generate_embeddings] PARAMETERS without parentheses This PR fixes a bug for AI_GENERATE_EMBEDDINGS when PARAMETERS comes without parentheses "SELECT AI_GENERATE_EMBEDDINGS(large_text USE MODEL MyAzureOpenAIModel PARAMETERS '{"dimensions" : 768 }')" Current behavior only allows PARAMETERS with parentheses. This PR adds syntax change along with tests. - [X] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [X] Code changes are accompanied by appropriate unit tests - [X] Identified and included SMEs needed to review code changes - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file Please provide any additional information that might be helpful for the reviewers First commit ---- Bug fix to correctly handle the ScriptDom PARAMETERS clause without parentheses. This pull request fixes the handling of the PARAMETERS clause in the AI_GENERATE_EMBEDDINGS function by conditionally emitting parentheses only when needed. - `SqlScriptGeneratorVisitor.AiGenerateEmbeddingsFunction.cs`: Modified the code to check if OptionalParameters is a StringLiteral and, if so, emit it without surrounding parentheses. - `TSql170.g`: Updated the grammar rules for aiGenerateEmbeddingsFunctionCall to accept either a parenthesized expression or a direct string literal. - Test files: Adjusted baseline and test scripts, including error expectations, to validate the new syntax support.  Related work items: #4692491 * Merged PR 1808102: [AI_GENERATE_EMBEDDINGS] Additional Syntax Change # Pull Request Template for ScriptDom ## Description Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue This PR updates the `aiGenerateEmbeddingsFunctionCall` grammar and the script generator to: - **Constrain model names** after `USE MODEL` to a **single token** (either an `Identifier` or one `QuotedIdentifier` like `[dbo.MyDefaultModel]`). Multi-part names (e.g., `dbo.MyDefaultModel`, `[dbo].[MyDefaultModel]`, `MyDb.dbo.Model`) now **fail at parse time**. - **Refine `PARAMETERS`** parsing to accept an **expression** with or without parentheses, but **reject** a **bare JSON string literal** (e.g., `PARAMETERS '{"a":1}'`). This is because the optional PARAMETERS can only be a MSJSON not varchar/nvarchar. ## What changed ### Grammar (`TSql170.g`) - **Model name**: - Replaced `schemaObjectThreePartName` with a **single-part** alternative: - `Identifier` **or** `QuotedIdentifier` (one token). - This allows `[dbo.MyDefaultModel]` (single token), but rejects dotted multi-part forms. - **PARAMETERS**: - Accept as **keyword** *or* **identifier** matched to `"Parameters"`. - Value can be either: - `PARAMETERS ( <expr> )` → wraps `<expr>` in a `ParenthesisExpression` to preserve parens. - `PARAMETERS <expr>` → **guarded** to **forbid** a leading ASCII/Unicode **string literal** (blocks `PARAMETERS '{...}'`). - No support added for `=` (kept “same as before” by design). ### Script generator (`SqlScriptGeneratorVisitor.AIGenerateEmbeddingsFunction.cs`) - **Do not force parentheses** for `OptionalParameters`. Emit: - `PARAMETERS {space} <fragment>` - If the AST contains a `ParenthesisExpression`, it prints its own `(`…`)`. Bare expressions print without parens. Before submitting your pull request, please ensure you have completed the following: ## Code Change - [X] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [X] Code changes are accompanied by appropriate unit tests - [X] Identified and included SMEs needed to review code changes - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please ... * Merged PR 1814233: add OPTIMIZE_FOR_ARRAY_SEARCH option for Json index # Pull Request Template for ScriptDom ## Description Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers This PR added OPTIMIZE_FOR_ARRAY_SEARCH option for Json index. A test case is added. ---- #### AI description (iteration 1) #### PR Classification This PR introduces a new feature by adding support for the OPTIMIZE_FOR_ARRAY_SEARCH option in JSON indexes. #### PR Summary The changes enable the OPTIMIZE_FOR_ARRAY_SEARCH option in ScriptDom by updating parser mappings, constants, and test cases to validate the new JSON index syntax. - `SqlScriptDom/Parser/TSql/IndexOptionKind.cs`: Added a new enum value for OptimizeForArraySearch. - `SqlScriptDom/Parser/TSql/CodeGenerationSupporter.cs`: Introduced a new constant for OPTIMIZE_FOR_ARRAY_SEARCH. - `SqlScriptDom/Parser/TSql/IndexOptionHelper.cs`: Mapped the new index option to support the updated feature. - Test files (`Test/SqlDom/TestScripts/JsonIndexTests170.sql`, `Test/SqlDom/Baselines170/JsonIndexTests170.sql`, `Test/SqlDom/Only170SyntaxTests.cs`): Updated to include and validate the new JSON index option.  Related work items: #4709838 * Merged PR 1815158: Add test case for PAD_INDEX and DROP_EXISTING options for Json index # Pull Request Template for ScriptDom ## Description Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information This PR added a test case for PAD_INDEX and DROP_EXISTING options for Json index. These options are shared options with other indexes, so only a test case is needed. ---- #### AI description (iteration 1) #### PR Classification This PR adds new test cases to validate the PAD_INDEX and DROP_EXISTING options for JSON indexes. #### PR Summary The changes extend the JSON index tests by including scenarios for the PAD_INDEX and DROP_EXISTING options. - `Test/SqlDom/TestScripts/JsonIndexTests170.sql`: Adds a JSON index creation statement with PAD_INDEX and DROP_EXISTING options. - `Test/SqlDom/Baselines170/JsonIndexTests170.sql`: Updates the baseline to include the new JSON index command with PAD_INDEX and DROP_EXISTING options. - `Test/SqlDom/Only170SyntaxTests.cs`: Adjusts the expected error counts for JSON index tests to reflect the new options.  Related work items: #4711081 * fixing merge issues * Merged PR 1815666: Release notes for 170.121.2 # Pull Request Template for ScriptDom ## Description Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers * replace smart apostrophe --------- Co-authored-by: Mohit Agarwal <[email protected]> Co-authored-by: Urvashi Raj <[email protected]> Co-authored-by: Zi Chen <[email protected]> Co-authored-by: Mateo Marenco <[email protected]> Co-authored-by: GitOps (Git LowPriv) <GitOps (Git LowPriv)> Co-authored-by: Sicong Liu <[email protected]> Co-authored-by: Lijun Ji <[email protected]> Co-authored-by: Drew Skwiers-Koballa <[email protected]>

* Merged PR 1818918: Adding for Order Clause to JSON_ARRAYAGG # Pull Request Template for ScriptDom ## Description Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers Adding for Order Clause to JSON_ARRAYAGG ---- #### AI description (iteration 1) #### PR Classification New feature addition to support an ORDER BY clause in JSON_ARRAYAGG. #### PR Summary This pull request adds comprehensive support for an ORDER BY clause in JSON_ARRAYAGG function calls by updating the parser grammar, AST definitions, and script generation logic, accompanied by new tests ensuring correct implementation. - In `SqlScriptDom/Parser/TSql/TSql170.g`, updated grammar rules to include an optional `OrderByClause` for JSON_ARRAYAGG. - In `SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/SqlScriptGeneratorVisitor.FunctionCall.cs`, added logic to generate the ORDER BY clause when present. - In `SqlScriptDom/Parser/TSql/Ast.xml`, appended a new member `JsonOrderByClause` to support the syntax. - Added new test scripts and baselines in `Test/SqlDom/TestScripts/JsonArrayAggOrderBy170.sql` and `Test/SqlDom/Baselines170/JsonArrayAggOrderBy170.sql` to validate the functionality.  * Merged PR 1819059: Fix parsing 'WITH ARRAY WRAPPER' in json_query function # Pull Request Template for ScriptDom ## Description Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers Fix parsing 'WITH ARRAY WRAPPER' in json_query function ---- #### AI description (iteration 1) #### PR Classification Bug fix addressing incorrect parsing of the JSON_QUERY function when using the WITH ARRAY WRAPPER clause. #### PR Summary This pull request corrects the token generation for the JSON_QUERY function so that the WITH ARRAY WRAPPER clause is correctly parsed inside the function's parameter list. The changes adjust the grammar, script generator, and test expectations to match the proper syntax. - `Test/SqlDom/ParserErrorsTests.cs`: Updated error tests to reflect new token positions for the WITH ARRAY WRAPPER clause. - `SqlScriptDom/Parser/TSql/TSql170.g`: Modified grammar rules to move the closing parenthesis generation after handling the WITH clause. - `SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/SqlScriptGeneratorVisitor.FunctionCall.cs`: Reordered token generation to include the WITH ARRAY WRAPPER clause within the function call parameters. - `Test/SqlDom/Baselines170/JsonFunctionTests170.sql` and `Test/SqlDom/TestScripts/JsonFunctionTests170.sql`: Updated baseline and test scripts to match the corrected function syntax.  Related work items: #4716932 * Merged PR 1810300: Updated syntax for EM permissions tests # Pull Request Template for ScriptDom ## Description External Model Object are not schema - scoped and only database scoped. hence, tests scripts are updated accordingly. Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers Updated syntax for EM permissions ---- #### AI description (iteration 1) #### PR Classification Syntax update for external model permissions tests. #### PR Summary This pull request revises the SQL syntax in EM permissions tests by removing explicit schema prefixes from external model names, ensuring consistent and simplified permission statements. - Changes in `Test/SqlDom/Baselines170/SecurityStatementExternalModelTests170.sql`: Removed redundant schema qualifiers (e.g., `prod.`, `dbo.`, `schema1.`, `test.`) from external model object names. - Changes in `Test/SqlDom/TestScripts/SecurityStatementExternalModelTests170.sql`: Updated SQL statements to strip schema qualifiers, standardizing external model naming across the test scripts.  * Merged PR 1818887: [JSON] ScriptDom support for json_value Returning syntax Adds support to scriptdom for RETURNING option for json_value. Specifically support for json_value('a', 'b' RETURNING <SQL Type>) Code Change - [ ] The Common checklist has been reviewed and followed - [x] Code changes are accompanied by appropriate unit tests - [x] Identified and included SMEs needed to review code changes - [x] Follow the steps here to make changes in the code Testing Follow the steps here to add new tests for your feature Test are added to respective json test file. Documentation Update relevant documentation in the wiki and the README.md file Additional Information Support by updating grammar rules, AST definitions, code generation, and tests to handle a returning clause for JSON_VALUE with typed data. - `SqlScriptDom/Parser/TSql/TSql170.g`: Revised grammar rules to replace the JSON returning clause with one that uses a DataTypeReference, added a new jsonValueReturningClause and jsonDataType rule to enforce JSON type restrictions, and integrated JSON_VALUE into the built-in function call logic. - `SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/SqlScriptGeneratorVisitor.FunctionCall.cs`: Modified the return type generation logic to work with DataTypeReference and introduced specific handling for JSON_VALUE, ensuring proper generation of the returning clause. - `SqlScriptDom/Parser/TSql/Ast.xml`: Updated the AST definition for the ReturnType member from Identifier to DataTypeReference to support the new syntax. - `Test/SqlDom`: Expanded test cases in multiple SQL test scripts to include JSON_VALUE with various returning types, validating the new syntax. * Merged PR 1823138: Add test cases for Json_contains and Json_modify # Pull Request Template for ScriptDom ## Description This PR added several test cases for Json_contains and Json_modify. These 2 Json functions don't have special syntax that needs special handling, so just added a few test cases. Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers add test cases for Json_contains and Json_modify Related work items: #4724160 * Merged PR 1824015: Adding the release notes for 170.128.0 Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue Before submitting your pull request, please ensure you have completed the following: - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file Please provide any additional information that might be helpful for the reviewers Adding the release notes for 170.128.0 * fixing the test --------- Co-authored-by: Urvashi Raj <[email protected]> Co-authored-by: Shiv Prashant Sood <[email protected]> Co-authored-by: Lijun Ji <[email protected]>

* [Security] Update .NET SDK to latest patch version (8.0.415) * Merged PR 1847463: Adding support for option RESUMABLE in ALTER TABLE ADD CONSTRAINT GitHub bug: microsoft#125 RESUMABLE is valid for 160+ reference: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver17#resumable---on--off Max_Duration is valid for 120+ Reference: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver17#max_duration--time--minutes- This PR introduces version-gated logic to allow RESUMABLE (and MAX_DURATION) only when the SQL Server version supports it, and updates related tests and documentation to clearly guide such validation fixes. - Updated `/SqlScriptDom/Parser/TSql/TSql80ParserBaseInternal.cs` to replace unconditional option rejections with checks using version flags (TSql160AndAbove and TSql120AndAbove). - Added new test scripts and baselines in `/Test/SqlDom/TestScripts/AlterTableResumableTests160.sql` and `/Test/SqlDom/Baselines160/AlterTableResumableTests160.sql`, and updated `/Test/SqlDom/Only160SyntaxTests.cs` to verify version-specific error counts. - Added comprehensive documentation in new files (e.g., `/.github/VALIDATION_FIX_GUIDE.md`, `/.github/DEBUGGING_WORKFLOW.md`, and `/.github/README.md`) to assist users in handling validation issues like this. - Updated copilot and bug fixing guides (in `/.github/copilot-instructions.md` and `/.github/BUG_FIXING_GUIDE.md`) with decision trees and pract... * Merged PR 1870990: Fixing the bug JSON_OBJECT does not parse inside RETURN statement # Pull Request Template for ScriptDom ## Description Fixing bug microsoft#181 Fixing the bug JSON_OBJECT does not parse inside RETURN statement Bug fix and documentation update to ensure JSON_OBJECT (and related JSON functions) parse correctly inside RETURN statements. #### PR Summary This pull request fixes the issue where JSON_OBJECT was failing in RETURN statements by introducing token-based syntactic predicates across multiple grammar files, and it also adds supporting test cases and comprehensive documentation updates for testing and function extension in SqlScriptDOM. - `SqlScriptDom/Parser/TSql/TSql160.g`, `TSql170.g`, and `TSqlFabricDW.g`: Added new `jsonObjectCall` and `jsonArrayCall` rules with proper syntactic predicates. - `Test/SqlDom/TestScripts` and `Test/SqlDom/Baselines160`: Introduced new tests and baselines for JSON_OBJECT, JSON_ARRAY, and TRIM usage in RETURN statements. - `/.github/instructions` and `/.github/prompts`: Added detailed guidelines for testing, new system function implementation, and grammar extension patterns. - `Test/SqlDom/Only160SyntaxTests.cs` and `/.github/copilot-instructions.md`: Updated test configurations and documentation references to include the new JSON function tests.  * Merged PR 1874197: Adding prompts to help developer using github copilot # Pull Request Template for ScriptDom ## Description - Adding prompt files to run in VScode github copilot and it help developers with using the copilot to generate the code. - Adding more instructions files for new features - Fixing the test library to write enough info for copilot to know better how to fix the tests. #### PR Summary This PR adds detailed guidelines for adding new index and data types, a new feature implementation prompt to assist GitHub Copilot users, and updates testing and documentation instructions throughout the repository. - Added new guideline files: `/.github/instructions/new_index_types.guidelines.instructions.md` and `/.github/instructions/new_data_types.guidelines.instructions.md` with step‑by‑step instructions. - Introduced a new prompt file `/.github/prompts/new-feature-implementation.prompt.md` to guide feature type identification for developers. - Updated testing guidance in `/.github/instructions/testing.guidelines.instructions.md` and refactored ParserTest constructors to use nullable error counts. - Modified copilot instructions and README links to reflect renamed and relocated guidelines. - Made minor changes in test utilities and baseline test files to support these updates.  * Merged PR 1870962: Add test case for JSON_OBJECTAGG with qualified column references (GitHub issue microsoft#175) ## Issue Users reported that `JSON_OBJECTAGG` was not parsing correctly with TSql170Parser, receiving the error "Incorrect syntax near &microsoft#39;:&microsoft#39;". Example that was failing: ```sql SELECT JSON_OBJECTAGG( t.c1 : t.c2 ) FROM ( VALUES(&microsoft#39;key1&microsoft#39;, &microsoft#39;c&microsoft#39;), (&microsoft#39;key2&microsoft#39;, &microsoft#39;b&microsoft#39;), (&microsoft#39;key3&microsoft#39;,&microsoft#39;a&microsoft#39;) ) AS t(c1, c2); ``` ## Investigation Results After thorough analysis, I found that **the grammar for JSON_OBJECTAGG is already complete and correct** in `SqlScriptDom/Parser/TSql/TSql170.g`. All supporting code exists: - ✅ Grammar rules in TSql170.g (lines 32584, 33029-30, 33135) - ✅ `jsonKeyValueExpression` rule handles both shorthand and explicit colon syntax - ✅ `CodeGenerationSupporter.JsonObjectAgg` constant defined - ✅ `JsonKeyValue` AST class with proper properties - ✅ Script generator visitor for JsonKeyValue - ✅ Comprehensive positive tests in `JsonFunctionTests170.sql` - ✅ Comprehensive negative tests in `ParserErrorsTests.cs` ## Root Cause The issue is that the **parser needs to be regenerated** from the grammar files via the build process. The grammar has the correct implementation, but the generated parser code needs to be updated. ## Changes Made Added a test case to `JsonFunctionTests170.sql` that exactly matches the reported issue scenario: - **Test script**: Added test with qualified column names `t.c1 : t.c2` - **Baseline**: Added expected normalized output This ensures future builds will include this specific test case and the parser will handle qualified column references in JSON_OBJECTAGG correctly. ## Next Steps When the build runs, it will: 1. Regenerate the parser from TSql170.g using ANTLR 2. Compile the updated parser 3. Run all tests (which should pass) 4. Publish a new NuGet package with the working parser ## Verification The fix can be verified with: ```csharp var script = @"SELECT JSON_OBJECTAGG( t.c1 : t.c2 ) FROM ( VALUES(&microsoft#39;key1&microsoft#39;, &microsoft#39;c&microsoft#39;), (&microsoft#39;key2&microsoft#39;, &microsoft#39;b&microsoft#39;), (&microsoft#39;key3&microsoft#39;,&microsoft#39;a&microsoft#39;) ) AS t(c1, c2);"; var parser = new TSql170Parser(true); var fragment = parser.Parse(new StringReader(script), out var parseErrors); Assert.AreEqual(0, parseErrors.Count); // Should pass after build ``` Fixes: [scriptdom is missing json_objectagg](https://msdata.visualstudio.com/web/wi.aspx?pcguid=8b119ea1-2e2a-4839-8db7-8c9e8d50f6fa&id=4792111) <p><small class=\"secondary-text\">AI-generated content may be incorrect</small></p>  Co-authored-by: Azure Pipelines Test Service <> Co-authored-by: GitHub Copilot <> Co-authored-by: Leila Lali <[email protected]> Related work items: #4792111 * Merged PR 1882663: Adding a prompt to analyze dsmain ADO commit # Pull Request Template for ScriptDom This PR introduces new automation prompts and demo instructions for ADO commit analysis and T-SQL verification, along with updates to testing guidelines and configuration adjustments. #### PR Summary The changes add detailed prompts and demonstration files to automate the analysis of ADO commits (including VECTOR_SEARCH validation and bug reporting), update testing instructions and guidelines to enforce unit test best practices, and adjust ADO configuration settings. - **`.github/prompts/analyze-ado-commit-for-tsql-verification.prompt.md`**: New prompt outlining the complete workflow for analyzing ADO commits for T-SQL verification and creating bug reports. - **`.github/demo/analyze_ado_commit_demo.md` & `.github/demo/new-feature-implementation_demo.md`**: Demo files showcasing the automated analysis process and the implementation fix for the VECTOR_SEARCH function (supporting 3-part identifiers). - **`.github/prompts/verify-and-test-tsql-syntax.prompt.md`**: Updated prompt with enhanced debugging and unit test instructions for verifying T-SQL syntax. - **Guideline Instruction Updates (`.github/instructions/*`)**: Revised testing, bug fixing, and function guidelines to mandate unit test usage and align testing procedures. - **`.vscode/mcp.json`**: Modified ADO configuration with an updated area path for proper integration.  * remove some ADO guidlines * Merged PR 1898573: Adding Release notes for 170.147.0 Adding Release notes for 170.147.0

* Merged PR 1821782: Fixing the parse issue with vector type Parser would timeout or crash when parsing queries with VECTOR data types in deeply nested JOIN structures due to exponential complexity (O(2^n)) in ANTLR's syntactic predicate lookahead. The `selectTableReferenceElement` grammar rule uses recursive syntactic predicates that cause exponential backtracking when VECTOR types appear in complex join hierarchies. Each additional nesting level doubles the parsing attempts. Implemented conditional optimization using `ContainsVectorInLookahead()` helper method: - Scans ahead up to 200 tokens to detect VECTOR keyword - When VECTOR detected: applies `SaveGuessing` optimization to reduce complexity from O(2^n) to O(n²) - When no VECTOR: uses standard grammar path with minimal overhead (~1-2ms) - **TSql170.g**: Modified `selectTableReferenceElement` rule with conditional SaveGuessing - **TSql170ParserBaseInternal.cs**: Added `ContainsVectorInLookahead()` helper method - **ComplexQueryTests170.sql**: Added comprehensive test coverage (6 queries) - 5 VECTOR scenarios: CAST, CONVERT, deeply nested JOINs with VECTOR types - 1 non-VECTOR scenario: Complex 6-way nested JOIN to validate no regression - All 1,122 tests passing (0 failures) - VECTOR queries: Previously timeout/crash → now parse successfully - Non-VECTOR queries: No measurable performance impact - Cross-version validation: Appropriate error counts for TSql80-160 - **Overhead**: O(1) per table reference (200-token scan) - **Typical queries**: <2ms additional overhead - **VECTOR queries**: Massive improvement (timeout → completes) - **Verdict**: Acceptable tradeoff for critical fix Related work items: #4819213 * Merged PR 1921093: [Fabric DW] Add support for Fabric DW specific AI functions This PR adds support for Fabric DW specific AI functions: - `ai_analyze_sentiment(<input>)` - `ai_classify(<input>, <class1>, <class2>[, <class 3>, ...])` - `ai_extract(<input>, <label1>[, <label2>, ...])` - `ai_fix_grammar(<input>)` - `ai_generate_response(<promptPart1>[, <promptPart2>])` - `ai_summarize(<input>)` - `ai_translate(<input>, <lang>)` Detailed design document is here: https://microsoft.sharepoint.com/:w:/r/teams/AzurePolaris/_layouts/15/doc2.aspx?sourcedoc=%7B75BAC667-A870-4482-8A37-F80E6EC8FCE0%7D&file=AI%20and%20Extensibiliy.docx Before submitting your pull request, please ensure you have completed the following: - [x] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [x] Code changes are accompanied by appropriate unit tests - [x] Identified and included SMEs needed to review code changes - [X] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code - [x] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ---- New feature: Adding support for Fabric DW–specific AI built-in functions. This pull request extends the SQL parser for Fabric DW by introducing new AI functions and their corresponding AST representations, script generators, and tests. Key changes include: - **`TSqlFabricDW.g`**: Added grammar rules for AI functions (e.g., AI_ANALYZE_SENTIMENT, AI_CLASSIFY, AI_EXTRACT, AI_GENERATE_RESPONSE, AI_SUMMARIZE, AI_TRANSLATE). - **`Ast.xml`**: Introduced new AST classes to represent the AI function calls. - **Script Generator Files**: Created new files in `SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/` to define visitors for the AI functions. - **`CodeGenerationSupporter.cs`**: Added new constants to support the AI function tokens. - **Test Files**: Included multiple test scripts and negative tests under `/Test/SqlDom/` to validate the syntax and error handling for the newly added functions.  Related work items: #4924682 * Merged PR 1921976: Fixing IIF parsing when it occurs inside a parenthesis. # Pull Request Template for ScriptDom ## Description Fixes: microsoft#28 The TSql160Parser.ParseExpression() was failing to parse valid T-SQL expressions where IIF with boolean operators was wrapped in parentheses: ```sql SELECT 1 WHERE (IIF(1 > 0 AND 2 > 1, 1, 0)) = 1 ``` Customers reported this issue when using IIF expressions with multiple boolean operators (AND, OR, >, <, etc.) wrapped in parentheses. The original code used a simple counter (insideIIf) to track when inside an IIF call. The counter was decremented on the first boolean operator inside the IIF (e.g., >). When a second operator like AND was encountered, insideIIf was already 0, causing it to be misidentified as a top-level boolean operator - which incorrectly signaled that the outer parentheses contained a boolean expression instead of a scalar expression. Replaced the simple counter with stack-based parenthesis depth tracking. This properly tracks when we're inside an IIF by remembering the parenthesis depth where each IIF started, rather than incorrectly decrementing on boolean operators. This also correctly handles: Nested IIF expressions: `(IIF(IIF(a > 1, b, c) > 2, 1, 0))` Deeply nested parentheses: `((((IIF(1 > 0 AND 2 > 1, 1, 0)))) = 1` Multiple IIF expressions: `(IIF(...)) = 1 AND (IIF(...)) = 1` ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers ---- #### AI description (iteration 1) #### PR Classification Bug fix to correct the parsing of IIF expressions when they occur inside parentheses. #### PR Summary This pull request fixes the incorrect handling of nested IIF expressions by replacing a simple counter with a stack-based approach and a pending flag, ensuring accurate tracking of IIF parentheses. The update enhances the SQL parser and adds regression tests to validate various scenarios. - `SqlScriptDom/Parser/TSql/TSql80ParserBaseInternal.cs`: Refactor... * Merged PR 1932484: Adding release notes for 170.157.0 # Pull Request Template for ScriptDom ## Description Adding release notes for 170.157.0 Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers Adding release notes for 170.157.0 ---- #### AI description (iteration 1) #### PR Classification Documentation update providing the release notes for version 170.157.0. #### PR Summary This pull request adds a new release notes file for Microsoft.SqlServer.TransactSql.ScriptDom 170.157.0, detailing supported platforms, dependency updates, new AI function support, and fixes for specific issues. - `release-notes/170/170.157.0.md`: New file containing the release information including target platform support, updated .NET SDK dependency (8.0.415), addition of Fabric DW-specific AI functions, and fixes for issues microsoft#161 and microsoft#28.  --------- Co-authored-by: Maksim Vlasov <[email protected]> Co-authored-by: Aasim Khan <[email protected]>

* Merged PR 1952906: JSON_ARRAYAGG support for OVER clause # Pull Request Template for ScriptDom ## Description `JSON_ARRAYAGG` supports windowed aggregate usage via the `OVER (PARTITION BY ...)` clause in SQL Server, but ScriptDOM was missing support for parsing and generating it. This PR adds OVER clause support for `JSON_ARRAYAGG` in the TSql170 parser and script generator. **SQL syntax now supported:** ```sql SELECT JSON_ARRAYAGG(name) OVER (PARTITION BY dept) FROM employees; SELECT JSON_ARRAYAGG(name ABSENT ON NULL) OVER (PARTITION BY dept) FROM employees; SELECT JSON_ARRAYAGG(name NULL ON NULL) OVER (PARTITION BY dept) FROM employees; SELECT JSON_ARRAYAGG(name ORDER BY name NULL ON NULL RETURNING JSON) OVER (PARTITION BY dept) FROM employees; ``` * Merged PR 1948594: Fix allocation issue in TSql80ParserBaseInternal.AddAndUpdateTokenInfo High memory allocations were detected in the `TSql80ParserBaseInternal.AddAndUpdateTokenInfo` method, specifically in the overload that processes collections. The method uses a `foreach` loop to iterate over an `IList<TFragmentType>` collection, which causes the allocation of a boxed `List.Enumerator` object on every invocation. Since this is a hot path in the SQL parser (called frequently during parsing operations), these repeated allocations contribute significantly to GC pressure. Performance impact: This issue appears in 0.28% of high allocation traces and allocates 3.0 MB/sec at both the 50th and 90th percentiles. ## Solution Replaced the `foreach` loop with an index-based `for` loop in the `AddAndUpdateTokenInfo` method. The new implementation iterates using an integer index from 0 to `otherCollection.Count`, accessing each element via the indexer. This eliminates the enumerator allocation while preserving identical functionality and behavior. The change is made in `/SqlScriptDom/Parser/TSql/TSql80ParserBaseInternal.cs` at lines 292-299. **Before:** ```csharp foreach (TFragmentType item in otherCollection) { AddAndUpdateTokenInfo(node, collection, item); } ``` **After:** ```csharp for (int i = 0; i < otherCollection.Count; i++) { TFragmentType item = otherCollection[i]; AddAndUpdateTokenInfo(node, collection, item); } ``` ## Benefits - Eliminates boxing allocation of `List.Enumerator` on hot path - Reduces GC pressure and improves parser performance - Maintains identical behavior and semantics - Minimal code change with no API modifications * Merged PR 1881483: Add support for 3-part and 4-part identifiers in VECTOR_SEARCH TOP_N parameter  ## Summary This PR enables the VECTOR_SEARCH table-valued function to accept 3-part and 4-part column identifiers in the TOP_N parameter, allowing queries like: ```sql SELECT qt.qid, src.id, ann.distance FROM QueryTable qt CROSS APPLY VECTOR_SEARCH( TABLE = graphnode AS src, COLUMN = embedding, SIMILAR_TO = qt.qembedding, METRIC = &microsoft#39;euclidean&microsoft#39;, TOP_N = dbo.qt.top_n -- 3-part identifier now supported ) AS ann ``` Previously, the parser only accepted up to 2-part identifiers (e.g., `qt.top_n`) in the TOP_N parameter, which prevented queries using schema-qualified column references in CROSS APPLY scenarios. ## Changes Made ### Grammar Rule Update Modified the `vectorSearchColumnReferenceExpression` rule in `SqlScriptDom/Parser/TSql/TSql170.g` to allow up to 4-part identifiers instead of the previous 2-part limit. This change enables the TOP_N parameter to accept: - Simple identifiers: `@variable` or `columnName` - 2-part identifiers: `table.column` - 3-part identifiers: `schema.table.column` (the primary use case from the issue) - 4-part identifiers: `database.schema.table.column` The change was minimal—updating a single parameter from `multiPartIdentifier[2]` to `multiPartIdentifier[4]` in line 33971 of TSql170.g. This aligns with standard SQL Server column reference behavior in other contexts. ### Test Coverage Added comprehensive test coverage in `Test/SqlDom/TestScripts/VectorSearchCrossApplyTests170.sql` with a test case that validates the exact query pattern from the issue. The test includes: - A DECLARE statement setting up a VECTOR variable - A SELECT query with CROSS APPLY using VECTOR_SEARCH - The TOP_N parameter specified as a 3-part identifier (`dbo.qt.top_n`) The corresponding baseline file `Test/SqlDom/Baselines170/VectorSearchCrossApplyTests170.sql` captures the expected formatted output from the parser. Updated `Test/SqlDom/Only170SyntaxTests.cs` to register the new test case with appropriate error counts for older parser versions (TSql80 through TSql160), which still use the 2-part identifier limit and correctly report parse errors for this syntax. ## Impact This change only affects the TSql170 (SQL Server 2025) parser and is backward compatible. The AST definition already used `ScalarExpression` for the TopN member, so no AST changes were required. Older parser versions continue to enforce the 2-part identifier limit and generate expected parse errors for 3-part identifiers. The fix enables real-world scenarios where VECTOR_SEARCH is used in CROSS APPLY with outer references that require schema qualification, which is common in production databases with explicit schema naming conventions. Fixes: #4843961 * Merged PR 1881565: ## Fix VECTOR_SEARCH SIMILAR_TO Parameter to Reject Subqueries  ### Summary The VECTOR_SEARCH function in the TSql170 parser was incorrectly allowing subqueries in the SIMILAR_TO parameter. This fix adds validation to properly reject subqueries and throw an appropriate parse error, aligning with SQL Server 2025 implementation requirements. ### Problem The SIMILAR_TO parameter was accepting any scalar expression, including subqueries wrapped in parentheses. This allowed invalid syntax like: ```sql SELECT * FROM VECTOR_SEARCH( TABLE = graphnode, COLUMN = embedding, SIMILAR_TO = (SELECT TOP 1 embedding FROM GTQuery), -- Should error but didn&microsoft#39;t METRIC = &microsoft#39;euclidean&microsoft#39;, TOP_N = 20 ) AS ann ``` This syntax should be rejected because subqueries are not supported in the SIMILAR_TO parameter, similar to how they are restricted in other contexts. ### Solution Added validation logic in the `vectorSearchTableReference` grammar rule (`SqlScriptDom/Parser/TSql/TSql170.g`) that checks if the SIMILAR_TO expression is a `ScalarSubquery` type. When detected, the parser now throws a SQL46098 error with the message "Subqueries are not allowed in this context. Only scalar expressions are allowed." The validation follows the same pattern used elsewhere in the grammar for similar restrictions and is placed immediately after matching the SIMILAR_TO identifier, before assigning the expression to the result object. ### Testing Added a comprehensive error test case in `Test/SqlDom/ParserErrorsTests.cs` within the existing `VectorSearchErrorTest170` method. The test verifies that: - Subqueries in the SIMILAR_TO parameter are properly rejected - The correct error code (SQL46098) is generated - The error position is accurately reported Valid syntax continues to work correctly, including: - Variables: `SIMILAR_TO = @qv` - Column references: `SIMILAR_TO = outerref.vector_col` - Other scalar expressions that are not subqueries ### Impact - **All 558 existing tests pass** with no regressions - **Minimal change**: Only 12 lines added across 2 files - **Consistent behavior**: Uses the same error code and message pattern as other subquery restrictions in the parser - **Backward compatible**: Only rejects previously invalid syntax that should not have been allowed Fixes: #4844065 Related work items: #4844065 * Merged PR 1959169: Adding release notes for 170.168.0 # Pull Request Template for ScriptDom ## Description Please provide a detailed description, include the link to the design specification or SQL feature document for the new TSQL syntaxes. Make sure to add links to the Github or DevDiv issue Before submitting your pull request, please ensure you have completed the following: ## Code Change - [ ] The [Common checklist](https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/Common?path=/Templates/PR%20Checklist%20for%20SQLToolsAndLibraries.md&version=GBmain&_a=preview) has been reviewed and followed - [ ] Code changes are accompanied by appropriate unit tests - [ ] Identified and included SMEs needed to review code changes - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=make-the-changes-in) here to make changes in the code ## Testing - [ ] Follow the [steps](https://msdata.visualstudio.com/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki/33838/Adding-or-Extending-TSql-support-in-Sql-Dom?anchor=to-extend-the-tests-do-the-following%3A) here to add new tests for your feature ## Documentation - [ ] Update relevant documentation in the [wiki](https://dev.azure.com/msdata/SQLToolsAndLibraries/_wiki/wikis/SQLToolsAndLibraries.wiki) and the README.md file ## Additional Information Please provide any additional information that might be helpful for the reviewers Adding release notes for 170.168.0 --------- Co-authored-by: Shiv Prashant Sood <[email protected]> Co-authored-by: GitHub Copilot <GitHub Copilot>

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Comparing changes

Open a pull request

Uh oh!

Commits on Jul 7, 2025

Commits on Jul 22, 2025

Commits on Jul 30, 2025

Commits on Aug 4, 2025

Commits on Aug 7, 2025

Commits on Sep 3, 2025

Commits on Sep 4, 2025

Commits on Sep 16, 2025

Commits on Sep 17, 2025

Commits on Sep 22, 2025

Commits on Sep 26, 2025

Commits on Sep 29, 2025

Commits on Oct 7, 2025

Commits on Dec 12, 2025

Commits on Jan 23, 2026

Commits on Feb 4, 2026

Commits on Feb 13, 2026

This comparison is taking too long to generate.

Uh oh!