Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 7th, 2007, 04:09 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default Simple Indexing Question

Hi,

I have a website that runs queries on historical data for companies. There are a couple of tables with about 8.5 million records. I had a bit of a blow up a week ago and I had to delete and replace one of the tables which was bad news. I've reloaded the data but now recall that the indexing needed changing. The table is currently using a clustered index by Date and then by company ID (CompID), whereas all queries are for individual companies to select data by date. On this table I previously had it set as an index by CompID then by Date to make queries run more efficiently so I think a clustered index is the right thing. I last had to change this about 5 years ago so I'd be grateful for a bit of hand-holding.

I used Enterprise Manager to look at the table/alltasks/manage indexes. It says PK_Table1_1, clustered: yes, columns: Date, CompID.

I clicked Edit and then get the following:

Checks against Date and CompID in that order

and checks against; Clustered, Unique Values, File Group PRIMARY and Drop existing (grayed out)

I want to make the index first by CompID then by Date. If I click on CompID and change the column order by moving up and then click OK, I get the message:

Server: Msg 1907, Level 16, State 3
Cannot recreate the index 'PK_Table1_1'. The new index definition does not match the constraint being enforce by the existing index.

I do wonder if I need to delete the existing index and then create another? I tried creating a new index but this seems to preclude having a clustered index, perhaps because you can have just one clustered index per table. Maybe if I delete the existing clustered index, I can create a new one by CompID then by Date?

I'd be very grateful for any advice,

Pavesa

 
Old April 9th, 2007, 01:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

You need to first drop the Primary Key contraint set for that table and then recreate it with your current requirement.

_________________________
- Vijay G
Strive for Perfection
 
Old April 9th, 2007, 10:42 AM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vijay,

thanks very much for getting back on this.

So, I should first use Enterprise Manager to go into design table and then remove the primary key from Date and CompID. Then I can go into All Tasks/Manage Indexes and edit the current index to change the order to CompID then Date (highlight CompID and then click the Up button and click OK). That should change the Index from by Date then CompID to CompID then Date and then I go back into design table and put the primary key back on Date and CompID?

That should work?

Handholding is what I need on this I'm afraid!

Thanks

Pavesa

 
Old April 10th, 2007, 12:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Pavesa,

Yes, what you mentioned was right if you choose to do it from All Tasks > Manage Indexes.

You could also do that by entering into Table Design window, Click on the Manage Indexes/Keys button from the Tool bar(second one from the Right), where you can go in and select CompId and then the Date under the "Column name", and make changes to the "Order", click "Close" then save the Table and quit... That should work. This is easier way to do, since it manages the KEYs too, you won't be getting the PK constraint error while doing it this way.

Let me know if that works.
cheers.

_________________________
- Vijay G
Strive for Perfection
 
Old April 10th, 2007, 06:37 AM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vijay,

that seemed to work fine, no error.

Thanks for your help, much appreciated.

Pavesa

 
Old April 10th, 2007, 07:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Good to know that :)

_________________________
- Vijay G
Strive for Perfection




Similar Threads
Thread Thread Starter Forum Replies Last Post
not at all simple question petergoodman XSLT 0 July 18th, 2008 08:37 AM
simple question petergoodman XSLT 8 July 18th, 2008 07:57 AM
Simple question VBAprutser VB How-To 2 August 20th, 2007 06:00 AM
Simple Question ironchef Java GUI 0 September 14th, 2006 04:56 PM
indexing performance question BinFrog SQL Server 2000 1 February 23rd, 2005 11:47 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.