locked
INLINE INDEX - INCLUDED columns syntax? RRS feed

  • Question

  • I have almost always created my indexes in separate statements from the CREATE TABLE statement. Now I am working in an environment that uses DAC package deployments. For whatever reason, the indexes seem to be out of out order with the table definitions, which I will track down separate from this thread.

    For here and now, I would like to the know inline index syntax for Included columns:

    the inline index example in MS documentation is 

    CREATE TABLE t2 ( c1 INT,  c2 INT,  INDEX ix_1 NONCLUSTERED (c1,c2) );

    My guess for INCLUDE columns below does not work

    CREATE TABLE t3 ( c1 INT, c2 INT, c3 INT, INDEX ix_1 NONCLUSTERED (c1,c2) INCLUDE(c3) );

    so what is the correct syntax?


    jchang

    Thursday, February 27, 2020 1:57 PM

Answers

  • My guess for INCLUDE columns below does not work

    CREATE TABLE t3 ( c1 INT, c2 INT, c3 INT, INDEX ix_1 NONCLUSTERED (c1,c2) INCLUDE(c3) );

    so what is the correct syntax?

    That syntax is indeed correct. But it only works on SQL 2019; I just tested.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by jchang61 Friday, February 28, 2020 6:42 PM
    Thursday, February 27, 2020 10:33 PM

All replies

  • My guess for INCLUDE columns below does not work

    CREATE TABLE t3 ( c1 INT, c2 INT, c3 INT, INDEX ix_1 NONCLUSTERED (c1,c2) INCLUDE(c3) );

    so what is the correct syntax?

    That syntax is indeed correct. But it only works on SQL 2019; I just tested.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by jchang61 Friday, February 28, 2020 6:42 PM
    Thursday, February 27, 2020 10:33 PM
  • the system I need this to work is 17, RTM, it's a QA system, yet no one wants to apply patches

    I will look for current 2017 to check, unless someone already knows?


    jchang

    Friday, February 28, 2020 6:43 PM
  • I will look for current 2017 to check, unless someone already knows?

    For SQL 2017, I guess the answer is: you can't do that. You will need to create the index separately.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 28, 2020 10:18 PM
  • Hi Erland Sommarskog,

    Thanks for your reply.

    This can be beneficial to other community members reading this thread. 

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Saturday, February 29, 2020 4:54 AM
  • Hi jchang61,

    Thanks for your post.

    This can be beneficial to other community members reading this thread. 

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Saturday, February 29, 2020 4:55 AM
  • CREATE TABLE t3 ( c1 INT, c2 INT, c3 INT, INDEX ix_1 NONCLUSTERED (c1,c2) INCLUDE(c3) );

    Works on 

    Microsoft SQL Azure (RTM) - 12.0.2000.8 
    Microsoft SQL Server 2019 (RTM-CU2) (KB4536075) - 15.0.4013.40

    Does not work on

    Microsoft SQL Server 2017 (RTM-CU15-OD) (KB4510083) - 14.0.3208.1
    Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)  
    Microsoft SQL Server 2016 (SP2-CU11-GDR) (KB4535706) - 13.0.5622.0
    Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (Intel X86) (the latest SQL on 32-bit)
    Microsoft SQL Server 2008 R2 (SP3-OD) (KB3144114) - 10.50.6542.0 (Intel X86) (my last XP machine)

    yuxi: please open a bug/request? perhaps at least get the Include syntax to work with version 2017, 

    I would be willing to skip the older versions, but I cannot force an upgrade of some 2017 installations


    jchang

    Saturday, February 29, 2020 9:39 PM