SQL 2012 create index "incorrect syntax near 'INCLUDE'"
A consultant created me a script to add some indexes, in it, it has a line like so
--sp_helpindex 'tmfWork_HAI'
CREATE NONCLUSTERED INDEX [IX_CreateDate_WI]
ON [dbo].[tmfWork_HAI]
(
CreateDate
INCLUDE
(
WorkOrderKey
);
GO
It generates this error:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'INCLUDE'.
any idea why?
--sp_helpindex 'tmfWork_HAI'
CREATE NONCLUSTERED INDEX [IX_CreateDate_WI]
ON [dbo].[tmfWork_HAI]
(
CreateDate
INCLUDE
(
WorkOrderKey
);
GO
It generates this error:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'INCLUDE'.
any idea why?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Fyi, your consultant left out the critical WITH clause to explicitly specify at least FILLFACTOR, and to specify the filegroup to create the index on.
ASKER
hmm. I have no idea the signfiicance of that. I am a infrastructure guy not SQL guy.
The script has like 30 index creations. Most are more simple than the one above and just look like:
--sp_helpindex 'tsrSR'
CREATE NONCLUSTERED INDEX [IX_ShipToAddrKey]
ON [dbo].[tsrSR]
(
ShipToAddrKey
);
GO
Did did a 24 hour analysis of our db performance and came back with a list of suggestions. One of the suggestions was this list of 30 indexes. (he also recommended removing a handful)
The script has like 30 index creations. Most are more simple than the one above and just look like:
--sp_helpindex 'tsrSR'
CREATE NONCLUSTERED INDEX [IX_ShipToAddrKey]
ON [dbo].[tsrSR]
(
ShipToAddrKey
);
GO
Did did a 24 hour analysis of our db performance and came back with a list of suggestions. One of the suggestions was this list of 30 indexes. (he also recommended removing a handful)
He might be right on the indexes overall, but he's not really thorough and/or knowledgeable if he didn't also specify an explicit FILLFACTOR. As a DBA, I would never accept a default FILLFACTOR.
ASKER
thanks for the heads up
ASKER