A dumb question about sql Indexes

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A dumb question about sql Indexes

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-09 : 17:39:56

I am creating indexes on my tables, I just wanted to verify something. If I am always searching a table by either column 1, or column 2 as in

select * from
tbl1
where col1 = 1

or

select * from
tbl1
where col2 = 1

can I combine both Col1 and Col2 into 1 index w/ multiple columns, or is it much better to create a seperate index for each column?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-09 : 17:43:15

You should have two indexes for this example. You could use a composite index in this case:

select * from
tbl1
where col1 = 1 and col2 = 1

Tara Kizer

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-09 : 17:55:57

One more quick question, If my table has a PK on it, there is no need to have another index on the PK Column correct? Wont the PK will serve as a Clustered index already, or will there be any benefit to adding another one?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-09 : 18:03:34

1st question: Yes, well if we are talking about a one column PK
2nd question: That's if it's clustered. PKs can be non-clustered.
3rd question: Adding another what?

Tara Kizer

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-09 : 18:12:13

1 - answered
2 - answered
3 - another index, you answerd this in 2

One final question if I am joining two columns in a query

Select *
from a inner join b on a.ID = b.ID and a.ID2 = b.ID2

I am assuming it's the same as if that was to appear in the where clause, and on this I should use a composite index with both ID,and ID2.

Correct?

I appriciate the help I know these questions are fairly obvious, but I just want to verify this because I am updating roughly 70 tables, and I do not want to need to do it twice.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-09 : 18:16:21

Yes.

Also, when you create a primary key constraint, an index is created along with it. When you create a foreign key constraint, an index is not created along with it. You need to add indexes to each of the FKs. This is typically what you are joining on anyway, that is PKs and FKs.

Tara Kizer

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-09 : 18:21:23

Perfect Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources