Order By Index doesnt work any more afer sql 2019 update.

Question

Very simple query. I just need to sort by Index. Used to do this all the time. Whats the deal?

SELECT TOP (1000) [Index]
      ,[Product]
      ,[Company]
      ,[WONum]
      ,[MatlNum]
      ,[SealMatlNo]
      ,[ValveOptions]
      ,[Misc#]
      ,[5]
  FROM [Powerapps].[dbo].[SN_Test_DF_Idx_Full2]
  order by Index asc

Msg 1018, Level 15, State 1, Line 12
Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Other columns work fine. This has neven been an issue before.

Answer 1

INDEX is a reserved key word, you have to set it in brackets [ ] =>

 SELECT TOP (1000) [Index]  
       ,[Product]  
       ,[Company]  
       ,[WONum]  
       ,[MatlNum]  
       ,[SealMatlNo]  
       ,[ValveOptions]  
       ,[Misc#]  
       ,[5]  
   FROM [Powerapps].[dbo].[SN_Test_DF_Idx_Full2]  
   order by [Index] asc  

See Reserved Keywords (Transact-SQL)

Answer 2

@Olaf Helper ,

Yep! That did it.

So, is it bad form to name this column "Index"???

Thx.