SQL Server 2016创建Sequence时报错，寻求解决方案

Hey there, let's work through this sequence creation error together— I’ve dealt with similar headaches before, so here’s what you should check:

SQL Server introduced SEQUENCE objects back in 2012, so if your database is set to a compatibility level lower than 110 (the level for SQL Server 2012), the engine won’t recognize the CREATE SEQUENCE syntax at all. Here’s how to fix this:

• First, run this query to check your current compatibility level:

  SELECT name, compatibility_level 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName'; -- Replace with your actual database name
  

• If the result is below 110, update it to match your SQL Server 2016 version (level 130) with this command:

  ALTER DATABASE YourDatabaseName 
  SET COMPATIBILITY_LEVEL = 130;
  

  Note: Make sure you have the necessary permissions to alter the database, and test this in a non-production environment first if possible.

The Msg 319 error about the WITH keyword often pops up if there’s an unclosed statement in your query window, or if SSMS is executing more than just your sequence creation code. Try these fixes:

• Highlight only the CREATE SEQUENCE line and hit Execute (F5) instead of running the entire window.
• If you’re running this as part of a larger batch, ensure every statement before it ends with a semicolon. For example:

  -- Correct batch with proper semicolon termination
  SELECT * FROM dbo.YourExistingTable; -- Semicolon here is critical
  CREATE SEQUENCE dbo.seq_changeid AS INT START WITH 1 INCREMENT BY 1;
  

Sometimes invisible characters (like full-width spaces copied from a document) or tiny typos can throw off the SQL parser, even if your code looks correct:

• Copy your CREATE SEQUENCE statement into a plain text editor (like Notepad) to strip any fancy formatting, then paste it back into SSMS and try again.
• Double-check that you spelled SEQUENCE correctly (no missing letters like SEQUNCE) and that you’re using standard ASCII spaces, not non-breaking or full-width ones.

While you’re using SQL Server 2016, an outdated version of SSMS might have parsing issues even if the server supports the SEQUENCE syntax. You can check your SSMS version by going to Help > About Microsoft SQL Server Management Studio. If it’s several years old, updating to a newer (but still compatible) version might resolve the problem.

Once you’ve tried these steps, your sequence should create without those syntax errors. Let me know if you run into any other roadblocks!

内容的提问来源于stack exchange，提问作者alangilbi