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.
| Author |
Topic |
|
westmich
Starting Member
35 Posts |
 Posted - 2002-05-19 : 16:17:22
|
| Let's say I have a table of employee data like this:EmpID | Fname | Lname | Address | City | State | Zip | DeptIDFurthermore, let's say I wanted send out a mailing to employees in a certain department using the following query that's run quite frequently:Select Fname, Lname, Address, City, State, ZipFrom EmployeesWhere DeptID = 101Now would it make the most sense to create an index on EmployeeID and DeptID assuming EmployeeID is the primary key? Is it the index that comes into play in the Where clause? Or would you index the fields you are most often 'select'ing.ThanksEdited by - westmich on 05/19/2002 16:18:59 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-19 : 16:38:44
|
| A primary key usually gets an index created automatically for it. You shoul index the fields you most commonly use in you WHERE clause, although SQL Server will not always use the index you think. You can force a specific index by using optimizer hints, but it's usually best to trust the Query Optimizer. If you'd like to know how to index your tables, try out you most commonly used tables either with a SHOWPLAN, or you can view it graphically in QA. If you are having large table scans in some queries, you are missing important indexes on queried fields. If you are unfamiliar with this whole 'index thing', read up in BOL.Sarah Berger MCSD |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-19 : 16:50:18
|
quote:
A primary key usually gets an index created automatically for it.
Actually, creating a primary key always creates a unique index. This is what enforces the uniqueness of the values in the primary key. |
|
|
|
westmich
Starting Member
35 Posts |
Posted - 2002-05-20 : 07:24:39
|
| Thanks :-) |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-05-20 : 22:09:04
|
quote:
quote:--------------------------------------------------------------------------------A primary key usually gets an index created automatically for it.--------------------------------------------------------------------------------Actually, creating a primary key always creates a unique index. This is what enforces the uniqueness of the values in the primary key.
When I set a column as PK and look at the table properties/indexes, the Unique Constraint option is selected, but an index is also created. Why doesn't the Unique Index option get selected if that gets created automatically?EDITED: Sorry, dumb question. Because a primary key is a constraint, of course. It's bedtime...Sarah Berger MCSDEdited by - simondeutsch on 05/20/2002 22:10:35 |
|
|
|
|
|
|
|
|
Understanding Indexes - Newby