Index Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Index Problem

Author

Topic

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 05:36:02

create clustered index AOP_C_Idx on AOP_Master([Date])

Running this query gives the error

Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

However I do not have any other indexes on the table. What could be the problem ???

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2004-03-18 : 05:42:06

What query?

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 05:54:08

Create Index query

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 06:13:52

Is this the whole statement or just a snippet?

--Frank
http://www.insidesql.de

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 06:15:29

This is the whole code

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 06:20:22

Interesting!

There seems to be no Line 2 in which the error occured.
No more code?
No CREATE TABLE statement....?

--Frank
http://www.insidesql.de

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 06:32:19

The table has already been created ... tell me one thing should a clustered index necessarily be unique

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 06:37:12

Maybe this is what brett calls a miracle :D

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 06:43:20

[code]
USE FRANK_PLAYGROUND
GO
IF OBJECT_ID('CI_test') IS NOT NULL
DROP TABLE CI_test
GO
CREATE TABLE CI_test (col1 INT, col2 CHAR(20))
GO
CREATE CLUSTERED INDEX CI_t ON CI_test (col2)
GO
INSERT INTO CI_test (col1, col2) VALUES(1, 'Hello World')
INSERT INTO CI_test (col1, col2) VALUES(1, 'Hello World')
SELECT * FROM CI_test

col1 col2
----------- --------------------
1 Hello World
1 Hello World

(2 row(s) affected)
[/code]
It must not be UNIQUE unless you create it this way. I would say, it depends...

--Frank
http://www.insidesql.de

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 06:48:18

Just run the above code and see the execution plan

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 07:03:04

Do you mean the Table Scan?

--Frank
http://www.insidesql.de

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 07:05:02

no the Querytext for Query 1

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 07:14:14

Äh, yes...so what...?!?

--Frank
http://www.insidesql.de

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 07:15:30

just noted that the statement shown is somewhat unrealistic.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 07:18:44

Just to make sure. My Query 1 shows
insert [FRANK_PLAYGROUND].[dbo].[CI_test] select * from [FRANK_PLAYGROUND].[dbo].[CI_test]
What's wrong that this is done when creating a clustered index?
`

--Frank
http://www.insidesql.de

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 07:20:58

Is this what you expected when you create a clustered index ???

I was confused by the statement.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 07:31:17

Well, I'm not quite sure on this, but I bet that has something to do with establishing the page chain for the clustered index.

--Frank
http://www.insidesql.de

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-18 : 07:48:13

Thats what I was pointing out at .. didnt find any doc that it was done ...still searching .. will let you know in case i find something ... and any more ideas for the problem ... I also tried creating clustered index on another column of the same table and same error was repeated.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-18 : 08:33:50

Can you create any kind of index on the table?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-03-19 : 16:11:45

http://support.microsoft.com/default.aspx?scid=kb;en-us;293177

IT IS A DOCUMENTED BUG

As I thought

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources