sql server 2008 not supporting sql server 2000

  • Hello this code ok in sql server 2000, but in 2008 INDEX

    SELECT BUDEPT.DEPT, Dept_Description.DEPT_NAME

    FROM Dept_Description (create index =PK_Dept_Description) INNER JOIN

    BUDEPT ( INDEX =IX_BUDEPT) ON Dept_Description.DEPT = BUDEPT.DEPT

    GROUP BY BUDEPT.DEPT, Dept_Description.DEPT_NAME

    ORDER BY BUDEPT.DEPT

    error:Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required

  • I'm not sure on why you included the "create" word. It shouldn't be part of the table hint, that might be the error and should occur as well on 2000.

    You should include WITH to use table hints, but most important, you need to be sure that you need them. You shouldn't include index hints, unless you've tested the query and found out that it is a real solution. SQL Server is able to identify which index is needed and when.

    This should work, but it's still a bad practice.

    SELECT BUDEPT.DEPT, Dept_Description.DEPT_NAME

    FROM Dept_Description WITH(index =PK_Dept_Description) INNER JOIN

    BUDEPT WITH( INDEX =IX_BUDEPT) ON Dept_Description.DEPT = BUDEPT.DEPT

    GROUP BY BUDEPT.DEPT, Dept_Description.DEPT_NAME

    ORDER BY BUDEPT.DEPT

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply