Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

SQL Server table creation and index errors

Thx1137's profile image Thx1137 posted 1 month ago in General Permalink

Adding an index to SQL Server 2016 results in the following SQL statement. "ALTER TABLE "dbo"."SnapShots2" ADD INDEX "Index 3" ("DeviceMessageID");"

When executed this gives the error: "The operation 'ALTER TABLE ADD INDEX' is supported only with memory optimized tables."

The following statement works: "CREATE INDEX "Index 2" ON snapshots2 ("DeviceMessageID");"

If I try to execute a copy of the SQL in the "CREATE Code" tab (after making a new table name), it fails for several reasons.

  1. REAL(24,0), I have to change it to REAL.
  2. FLOAT(53,0), I have to change it to FLOAT.
  3. "COLLATE 'SQL_Latin1_General_CP1_CI_AS'" provides the error "Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'." so it has to be removed.
  4. the "FOREIGN KEY INDEX" entries have a syntax error.
  5. The "CONSTRAINT" entries have a syntax error.
  6. The geography columns are listed as column type "unknown"
ansgar's profile image ansgar posted 1 month ago Permalink

Yes, the table designer yet creates many MySQL/MariaDB specific queries, thought that ALTER TABLE ... ADD INDEX ... is already meant to be used on MS SQL. So that now even needs different queries on different MS SQL versions.

You can watch out for issues in the tracker, related to MSSQL.

Please login to leave a reply, or register at first.