question

RodMartin-0072 avatar image
0 Votes"
RodMartin-0072 asked SeeyaXi-msft edited

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

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.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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)


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RodMartin-0072 avatar image
0 Votes"
RodMartin-0072 answered RodMartin-0072 edited

@OlafHelper-2800 ,

Yep! That did it.

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

Thx.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

On one hand it's better to avoid the usage of reserved key word, on the other no one can say which additional words gets reserved in 10 years.

0 Votes 0 ·