Gaurav,
Since you already have and index on flight_id & tix_id, a seperate index on just flight_id is uneccessary. Queries where only filght_id is a criteria can take advantage of the flight_id, tix_id index since flight_id is first.

A Seperate index on just tix_id makes more sence because queries where only tix_id is a criteria would not take advantage of the flight_id, tix_id index since it's not the first field in the index.

-------------- Original message --------------
From: "Gaurav Kohli " <mssqldba-ezmlmshield-x88233121.[Email address protected]

> Thanks for your response Stephen,
>
> The table is called "air_tix_flight". This has got two columns "tix_id"
> and "flight_id", both integers. And this has got two indexes.
>
> The first index is "air_tix_flight8", which is a clustered index on
> "flight_id".
> The second index is "air_tix_flight4", which is a non clustered index on
> "flight_id" and "tix_id".
>
> Both the indexes were created using the recommendations from the Index
> Tuning Wizard.
>
> The question here is that should I go ahead and drop the clustered index
> as "flight_id" is already there in the non clustered one.
>
> Thanks
> Gaurav
>
>
> -----Original Message-----
> From: Stephen Davey
> [mailto:mssqldba-ezmlmshield-x41005222.[Email address protected]
> Sent: Friday, June 29, 2007 5:48 PM
> To: LazyDBA Discussion
> Subject: Re: Reindexing Error
>
> Gaurav,
>
> Sounds to me like the table you're trying to reindex has two indexes
> that use the same column or columns in its definition. Check the index
> definitions to verify that they all use unique columns. If there is a
> duplicate index definition, and both are needed, you will probably have
> to rebuild each index individually.
>
> S. Davey (6/29)
>
>
> MI DIT\Agency Services
> 7:30 A.M. - 4:00 P.M. ET
> 517.335.4237 (M- F CCC )
>
>
> >>> "Gaurav Kohli "
> > AM >>>
> Hi Guys,
>
> I get the error:
>
> "A column has been specified more than once in the order by list.
> Columns in the order by list must be unique."
>
> When I run the query:
>
> DBCC DBREINDEX(Table_Name,' ',90)
>
> We are on SQL Server 2000
>
> Any Ideas?
>
> Thanks
> Gaurav Kohli
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>

MS Sql Server LazyDBA home page