Error while creating covering-index (Msg: 170)

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Error while creating covering-index (Msg:...

Error while creating covering-index (Msg: 170)

Rate Topic

Display Mode

Topic Options

Author

Message

Abhilash-1061318

Posted Monday, June 29, 2009 3:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 08, 2009 11:47 PM
Points: 7, Visits: 14

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?
Follow me on Twitter

Post #743449

Christopher Stobbs

Posted Monday, June 29, 2009 4:06 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, October 29, 2009 7:53 AM
Points: 1,542, Visits: 2,198

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
Numbers / Tally Tables

SQL-4-Life

Post #743455

hi_abhay78

Posted Monday, June 29, 2009 4:11 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 264, Visits: 323

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)

Post #743459

GilaMonster

Posted Monday, June 29, 2009 4:48 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:17 PM
Points: 14,802, Visits: 10,594

Compatibility mode 80?

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

Gail Shaw

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

Post #743481

Abhilash-1061318

Posted Monday, June 29, 2009 5:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 08, 2009 11:47 PM
Points: 7, Visits: 14

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?
Follow me on Twitter

Post #743500

Christopher Stobbs

Posted Monday, June 29, 2009 5:24 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, October 29, 2009 7:53 AM
Points: 1,542, Visits: 2,198

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
Numbers / Tally Tables

SQL-4-Life

Post #743503

Abhilash-1061318

Posted Monday, June 29, 2009 5:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 08, 2009 11:47 PM
Points: 7, Visits: 14

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?
Follow me on Twitter

Post #743511

GilaMonster

Posted Monday, June 29, 2009 5:41 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:17 PM
Points: 14,802, Visits: 10,594

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

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

Post #743518

Abhilash-1061318

Posted Monday, June 29, 2009 5:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 08, 2009 11:47 PM
Points: 7, Visits: 14

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.
Smile

---
.NET Developer
Blog: Did you say .NET?
Follow me on Twitter

Post #743523

« Prev Topic | Next Topic »

Permissions