Create index question

Question

Create index question

Peter D-251663

Ten Centuries

Points: 1010
More actions
March 22, 2014 at 1:28 pm

#289630

I would like to create an index with a few options but having issues trying to parse and run it successfully and need some assistance with the syntax. The issue is near the end of the statement. Here's the create statement below:
-- **********************************************************************
-- Index PSAPSACTIVITYDEFN on table PSACTIVITYDEFN
-- **********************************************************************
IF EXISTS(SELECT 1
FROM sysindexes si
INNER JOIN sysobjects so
ON so.id = si.id
WHERE si.[Name] = N'PSAPSACTIVITYDEFN' -- Index Name
AND so.[Name] = N'PSACTIVITYDEFN') -- Table Name
BEGIN
DROP INDEX [PSAPSACTIVITYDEFN] ON [PSACTIVITYDEFN]
END
GO
CREATE INDEX [PSAPSACTIVITYDEFN] ON [PSACTIVITYDEFN]
(
[ACTIVITYNAME] ASC,
[VERSION] ASC
)
WITH (FillFactor = 100)
WITH DROP_EXISTING ON [SECONDARY]
GO
--Appreciate input.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What exactly is the issue?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Problem I see is the DROP EXISTING in the create index statement. If the index exists you drop it. For the DROP EXISTING to work the index must exist.

milos.radivojevic Default port Points: 1499 More actions · Answer 3

Your create index statement fails because of syntax error.

This part:

WITH (FillFactor = 100)

WITH DROP_EXISTING

needs to be rewritten to:

WITH

(

FILLFACTOR = 100,

DROP_EXISTING = ON

)

With so corrected syntax your statement still can fail. DROP_EXISTING = ON option requires an index. If the index does not exist the statement fails. And in your case the index does not exist definitely because it's dropped by your previous IF EXISTS... statement. So, since you drop it explicitely in previous statement you have to remove DROP_EXISTING = ON option from the CREATE INDEX options. Or if you are sure that the index exist, you have to remove the whole IF EXISTS construct and leave the DROP_EXISTING = ON option.

The second thing which can cause creating index to fail is the last part - ON [SECONDARY]. The file group with this name must exist.

___________________________
Do Not Optimize for Exceptions!

Peter D-251663 Ten Centuries Points: 1010 More actions · Answer 4

Thanks Everyone for your input including the most recent post. Once I get to work Monday, I'll try your suggestions to create this existing index on the existing secondary file group.

Cheers

Peter-Vancouver B.C.