How is SQL Prompt helping your team? Share your experience.

SQL Prompt Formatter error - Inline Index with included column

The following valid SQL pops an (incorrect) parsing error when attempting to format the code block with ctrl+k+y

```sql
drop table if exists #foo;

create table #foo (
    a int,
    b int,
    index idx_foo (a) include(b)
        with (data_compression = page)
) with (data_compression = page);
```



As near as I can tell, the error only occurs in the following conditions
  1. inline syntax
  2. included column
  3. with compresson on index
  4. also compression on table
Can this bug please be squashed in a future release? Thank you.

Answers

  • Annnnnd... I've only just now learned that specifying compression at the table statement scope is invalid when specifying compression on an inline scoped PK statement. Notably however, neither SQL Prompt Formatter nor the SSMS `parse` function throw an error when evaluating the below statement.

    ```sql
    drop table if exists dbo.#foo;

    create table #foo (
        a int not null 
            primary key clustered
            with (data_compression = page)
    ) with (data_compression = page);
    ```

    ...although the following error is thrown when attempting to execute the batch: 

    Msg 7711, Level 16, State 3, Line 21
    The DATA_COMPRESSION option was specified more than once for the table, or for at least one of its partitions if the table is partitioned.
    Msg 1750, Level 16, State 0, Line 21
    Could not create constraint or index. See previous errors.

  • Hello,

    Thank you for bringing this to our attention, I have been able to reproduce this in my environment and have escalated this to the dev team to look into.

    Best,

    Dustin 
  • Hello,

    Thank you for your patience with us regarding this issue.

    Our development team has now reviewed the bug you have identified and highlighted it to us alongside our current workload and available resources. Unfortunately, on this occasion, this is not something they are able to provide a fix for.

    We do appreciate the effort our customers go to working with us to better our products, so please do continue to highlight anything you find that needs our attention.

    I am going to close this ticket, for now, however, please don’t hesitate in reaching out to us again if you have any questions regarding this.

    Best,

    Dustin

Sign In or Register to comment.