none
SSMS 18.3.1 - Receiving Error 'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE TABLE option. RRS feed

  • Question

  • Hello,

    I am receiving the following error

    'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE TABLE option.

    when I take the following steps:

    1. Right Click on table > Script Tables as > Create To > New Query Window

    2. Change the name of the table and PK

    3. Click Execute(F5)

    4. Receive error

    Monday, October 21, 2019 5:31 PM

All replies

  • Hello,

    I am receiving the following error

    'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE TABLE option.

    To which SQL server version are you connected where you are executing this query? 

    This option is available in SQL2019 onwards and Azure SQL DB. 

    Can you run select @@version in same query window and share the output.


    Regards,
    Vaibhav

    Monday, October 21, 2019 5:38 PM
  • Hello Vaibhav,

    I am receiving the error on Azure Managed Instance.

    Microsoft SQL Azure (RTM) - 12.0.2000.8  

    Thank you,

    Sylvia

    Wednesday, October 23, 2019 4:20 PM
  • Hello Vaibhav,

    I am receiving the error on Azure Managed Instance.

    Microsoft SQL Azure (RTM) - 12.0.2000.8  

    Sorry, my bad. I rechecked the MS Document and it says - 

    OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

    Applies to: SQL Server 2019 (15.x) and later.


    I did a quick test. The script failed in SQL Azure DB but it worked in SQL2019 version. 

    BTW, you are scripting the table. So I guess the table should be present in SQL2019 SQL and later you are executing script in Azure SQL DB. Please confirm.


    Regards,
    Vaibhav

    Thursday, October 24, 2019 6:59 AM
  • I am scripting the table from Azure Managed Instance and renaming it and trying to execute it there in the same database.
    Friday, October 25, 2019 9:21 PM
  • Thank you for your reply.

    Did you mean that you are using Azure SQL Database instead of SQL Server ? If so , please post your issue in the corresponding forum . 

    Microsoft Azure > Azure SQL Database Forum 

    Also , I find this option in SQL Server 2019. 

    OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF } Applies to: SQL Server 2019 (15.x) and later.
    Specifies whether or not to optimize for last-page insert contention. The default is OFF. See the Sequential Keys section of the CREATE INDEX page for more information.

    If you have post your issue in the corresponding forum ,in order to close this thread, please kindly mark helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel



    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.

    Monday, October 28, 2019 8:46 AM
  • Hello Rachel,

    Sorry I thought it was an SSMS 18.3.1 but it turns out its a combination of Azure Managed Instance and SSMS 18.3.1.  I have posted the question on the Azure SQL Database Forum.

    Thank you,

    Sylvia

    Tuesday, October 29, 2019 11:36 PM
  • Thank you for your reply.

    If you have post your issue in the corresponding forum ,in order to close this thread, please kindly mark helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    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.

    Wednesday, October 30, 2019 8:41 AM
  • I am updating the post with the response I received from Azure SQL Database Forum.

    This feature is available only on SQL Server 2019 for now. Its not yet available on Azure SQL Managed Instance or Azure SQL Server yet and this is why we are receiving the error.

    Thank you,

    Sylvia

    Thursday, October 31, 2019 11:21 PM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    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.

    Friday, November 1, 2019 8:11 AM