Index question

Last Post 28 Jan 2011 05:11 AM by dstoltz. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dstoltz
New Member
New Member

--
27 Jan 2011 12:10 PM
Hi All,

I'm using Management Studio 2008, with a SQL 2000 database. I have a table with 80,000 rows of zipcode information.

When doing a simple "SELECT * FROM TABLE WHERE zipcode = xxx" it takes too long - so I'm trying to figure out how to add an index to the zipcode column. This column has duplicates for some zipcodes, and there is no identity column in the table.

What is the proper way to add an index? I know there are different kinds too - yikes...don't know anything about them.

Any advise?

Thanks!
Dave
gunneyk
New Member
New Member

--
27 Jan 2011 03:18 PM
I am not sure what the issue is. Are there any indexes on it now? If you query most on the Zipcode column then add a clustered index on that column like this and you should be all set.


CREATE CLUSTERED INDEX [ix_zipcode_zipcode] on zipcode(zipcode) ;
rm
New Member
New Member

--
27 Jan 2011 03:18 PM
I'll put non-clustered index on zipcode column first based you your query.
dstoltz
New Member
New Member

--
27 Jan 2011 03:45 PM
Forgive me, but what's the difference between clustered and non-clustered?
dstoltz
New Member
New Member

--
27 Jan 2011 04:19 PM
Never mind - dumb question - tons of info on Google about that...

I got it working...

thanks!
russellb
New Member
New Member

--
28 Jan 2011 04:16 AM
One question: why do you have duplicate zips in that table? We maintain a zips table with a unique clustered index over zip. Other tables that may contain multiples of the same zip are foreign keyed to this table.
dstoltz
New Member
New Member

--
28 Jan 2011 04:23 AM
The zip data I purchased has duplicates (not many) because some regional information within that zipcode have different naming convensions, and each one, is listed as its own row. The good news is that there is a column called "primary" which contains a "P" to easily isolate the primary zipcode info (if there are duplicates of that zip). That is the row I use.

I created a non-unique non-clustered index on the zipcode column.

I have no idea what unique and non-unique do, but the queries are much faster now.
rm
New Member
New Member

--
28 Jan 2011 04:43 AM
With unique index, column or list of columns in that index must have unique value.
dstoltz
New Member
New Member

--
28 Jan 2011 05:11 AM
Ahhhh...ok. Makes sense...

I wouldn't be able to use a unique index then anyway, since I have some duplicates in that column.

Thanks RM! I wish I had 1/10 of your SQL knowledge.
You are not authorized to post a reply.

Acceptable Use Policy