HELP needed with Syntax error ALTER INDEX

Question

HELP needed with Syntax error ALTER INDEX

FreeHansje

SSChampion

Points: 11751
More actions
June 26, 2009 at 2:21 am

#139206

We use a similar script and recently partitiond a database. Now I receive a syntaxerror on this statement:
ALTER INDEX [MUTA_PI3]
ON [prd_00rpd_hrm].[hrm].[MUTA]
REBUILD
WITH
(ONLINE = ON,
FILLFACTOR = 90,
MAXDOP = 0)
PARTITION = 2;
error: Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'PARTITION'.
I can't figure out what the correct syntax is. Leaving out the PARTITION statement works, leaving out the WITH statement works. I can't find an example combining the 2.
Help please?
Greetz,
Hans Brouwer

Viewing 6 posts - 1 through 5 (of 5 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

ALTER INDEX [MUTA_PI3]
ON [prd_00rpd_hrm].[hrm].[MUTA]
REBUILD
WITH
(ONLINE = ON,
FILLFACTOR = 90,
MAXDOP = 0)
PARTITION = 2;
error: Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'PARTITION'.

Try this..

ALTER INDEX [MUTA_PI3]

ON [prd_00rpd_hrm].[hrm].[MUTA]

REBUILD PARTITION = 2

WITH

(ONLINE = ON,

FILLFACTOR = 90,

MAXDOP = 0)

Pradeep Singh

FreeHansje SSChampion Points: 11751 More actions · Answer 2

PS,

This is a solution I had tried first.

Don't get me wrong, but have you checked this in SSMS? I don't think so...

BTW, I have found the problem. Some options don't work with the PARTITION option.

Greetz,
Hans Brouwer

Pandian S SSChasing Mays Points: 625 More actions · Answer 3

According to MSDN specification....

ALTER INDEX cannot be used to repartition an index or move it to a different filegroup. This statement cannot be used to modify the index definition, such as adding or deleting columns or changing the column order. Use CREATE INDEX with the DROP_EXISTING clause to perform these operations.

ps. One Orange Chip Points: 29422 More actions · Answer 4

FreeHansje (6/26/2009)
PS,
This is a solution I had tried first.
Don't get me wrong, but have you checked this in SSMS? I don't think so...
BTW, I have found the problem. Some options don't work with the PARTITION option.

I didnt try that. I studies technet for that. there were two options, one that i/u mentioned. Other one required one to create a partition function/schema and then create the index.. will again search for those links and post them 🙂

Pradeep Singh

Paul White SSC Guru Points: 150467 More actions · Answer 5

Hey guys,

'Pandian S' posted what I believe is the correct answer...in case you missed it in all the excitement there 😉

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi