[RESOLVED] Indexes confused.

[luta]

Hi,

I am using SQL Server 2000 and all I know about Indexes so far is theoretical reading about the subject, never had to create one.
I now see the need to create some indexes.

I have a query which I have inherited from someone else's similar to the following:

INSERT INTO A (x,y,z)
SELECT(x,y,z) FROM B
WHERE StartDate >= '21-03-2008) AND EndDate >= '31-03-2008'

Create index ix_X on dbo.B(x)

This alone Selects over 15 million records and I used the "set showplan_text on" on the Select to find out the actual created index is not being used at all.
The select is doing a table scan which takes ages.
My question is: Shouldn't the Index be created before the INSERT INTO SELECT statement? And is it worth created an Index at all in this case if so what will be the appropriate Indexes to speed up the statement as for what I know Indexes slows down inserts.

Thank you

luta

[si_the_geek]

The index is on the correct table, but on the wrong field.. it needs to be on StartDate and/or EndDate to have any effect (as those are the values being searched).

Indexes do slow down Insert's etc, but only on the table that the index is on - so it will slow down an Insert into B, but not from B.

[luta]

thank you.
How about the creation of the Index, is this before the insert... select statement or after?
Thabk you again
luta

[si_the_geek]

There is no point creating it after, as the work has already been done.

It almost certainly isn't worth creating it at run-time either, as creating an index takes time - probably much more than you will save by having it.

Setting up the index should be part of the initial table design.

[luta]

Ok, just to clarify myself. If I have a CREATE TABLE Statement then I can create an Index after, and when I do a Select, etc that Index I created will then be used.

Thanks
luta

[si_the_geek]

That's right.