March 5, 2008 at 2:59 pm
I need some direction with a db I inherited. I'm planning on making some changes to it and just wanted some opinions before I start. The primary table (I'll refer to as Table A) contains a varchar(30) column (Column A-1) with "unique" values that acts as the "primary key" for other tables (even though it is not specifically designated as the primary key). There is another important column in Table A that contains full names (Column A-2), i.e. 'Doe, John, J'. Table A has a clustered index on Column A-1 and it contains approximately 40 million records. There is a simple, non-clustered index on Column A-2. I'm planning on adding an identity column and designating it as the primary key. I then plan on adding the primary key as a foreign key in all of the related tables. Should I make the new identity column the clustered index or a non-clustered index given that the Column A-1 already has a clustered index? I understand that I can not have two clustered indexes on a table and would have to remove the other one.
The overall goal is to reduce the query time. It takes about 55 seconds to query Column A-2 with a % wildcard. Any ideas are well appreciated.
Thanks!
March 5, 2008 at 3:08 pm
Have you considered making the NCI covering? without covering the rest of the fields needed, IF the NCI is being used at all, bookmark lookups would make this take a while.
Without some specifics - it's going be awfully difficult to comment on most of your other questions. Without looking at the query, the execution plan, etc... - there's not much to base an opinion on.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply