Error while creating covering-index (Msg: 170)

Question

Error while creating covering-index (Msg: 170)

abhilashca

Mr or Mrs. 500

Points: 508
More actions
June 29, 2009 at 3:57 am

#75492

I got into a strange situation. I tried to use covering-index using the INCLUDE keyword. But, I's invited with error:
"Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'INCLUDE'."
The Query I'm using is like:
CREATE INDEX IX_UpdateTime
ON Logs(UpdateTime)
INCLUDE (UpdateDate, UpdateId)
Can anyone suggest, where I went wrong?
Thanks.
---
.NET Developer
Blog: Did you say .NET?[/url]
Follow me on Twitter

Viewing 9 posts - 1 through 8 (of 8 total)

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

As far as know you can't use the INCLUDE in SQL 2000

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 2

Strange if this is happening in SQL Server 2005 .

try this even though there is nothing special i have changed .

CREATE INDEX [rerer1] ON [dbo].[MyTable]

([Name] ASC)

INCLUDE ([ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

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

Compatibility mode 80?

Edit: Never mind. Ignore this. I misread the forum this was in.

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

abhilashca Mr or Mrs. 500 Points: 508 More actions · Answer 4

GilaMonster (6/29/2009)
Compatibility mode 80?

How can I check the compatibility mode is 80?. Im using sql server 2000.

---
.NET Developer
Blog: Did you say .NET?[/url]
Follow me on Twitter

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

If you using 2000 you can't use include...

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

abhilashca Mr or Mrs. 500 Points: 508 More actions · Answer 6

Christopher Stobbs (6/29/2009)
If you using 2000 you can't use include...

How sad...!!!

Is there any alternate approach in sql 2000, to implement the same.

🙁

---
.NET Developer
Blog: Did you say .NET?[/url]
Follow me on Twitter

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

Add the columns in the index key. Not possible if there's lots or if they're of a type that can't be included.

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

abhilashca Mr or Mrs. 500 Points: 508 More actions · Answer 8

GilaMonster (6/29/2009)
Add the columns in the index key. Not possible if there's lots or if they're of a type that can't be included.

Thanks for the reply.

As you said, let me try the traditional way for adding Indexes.

🙂

---
.NET Developer
Blog: Did you say .NET?[/url]
Follow me on Twitter