What is the impact of CRUD operations and JOINS with multiple indexes?

I have a table with 120 columns. It has 8 indexes on it (1 clustered, 7 non-clustered). As a test, I inserted 3,400 rows into the table and it took an average of 12 seconds.

I created the same table with a different name and created 32 additional indexes on it (total 40 indexes) and again insert same 3,400 rows it took an average of 13.5 seconds.

I read before when an index is created, a copy of the table is created and sorted by that column that participates in the index.

I have 2 questions:

1) Is it true that for every index a copy of the table is created?

2) 32 indexes add only 1.5 seconds for the same INSERT command. Does this average remain for 10,000 rows? or for 80 indexes?

I have another question:

3)when we have a Foreign key on a column SQL Server automatically create non-cluster index on it.When we have a forein key on a column If we create 开发者_JAVA技巧a non-cluster index on it,is this Index useful for quering or Joins?

1) op asks: Is it true that by every index a copy of table created? it is not true. an index will store the indexed columns and the pointer back (like the the clustered PK, etc.) to the row. it will also store any INCLUDE columns, but that is another topic.

2) it is hard to guess on the timings not knowing the hardware, the tables, or the indexes in question. I'm sure that SQL Server is good at optimizing the maintenance of indexes. You should test this by inserting the same rows with only a PK and then try again with 40 indexes and then again with 80. I'm sure the bulk of the time is just the inserts.

EDIT

3) op asks when we have a Foreign key on a column SQL Server automatically create non-cluster index on it.this Index useful for quering or Joins? SQL Server does NOT automatically create an index for foreign keys. However, adding an index on FK columns may be useful. If you write a query that joins two tables on the FK, then the index MAY be used. Consider the following:

tableA
RowID    int PK
RowDate  datetime
OtherID  int

tableB
OtherID  int PK
someValue varchar(5)

if you add a FK for tableA.OtherID to tbaleB.OtherID and use this query:

select * from tableA a INNER JOIN tableB b ON a.OtherID=b.OtherID
WHERE a.RowDate<'2011/1/1'

then an index on tableA.OtherID won't be used, an index on tableA.RowDate will be used and the PK index on tableB.OtherID will be used.

select * from tableA a INNER JOIN tableB b ON a.OtherID=b.OtherID 
WHERE b.someValue='AA'

could also be written as:

select * from tableb b INNER JOIN tableA b ON b.OtherID=a.OtherID 
WHERE b.someValue='AA'

then an index on tableA.OtherID may be used, and any index on tableb.someValue will be used.

1 ) Is it true that for every index a copy of the table is created?

No. Every index will create a data structure such as a btree or hash table with pointers to your table. one pointer per row. they are fairly inexpensive in terms of disk space.

2 ) 32 indexes add only 1 .5 seconds for the same INSERT command. Does this average remain for 10, 000 rows? or for 80 indexes?

you will have to benchmark it to know for sure, but most likely, yes. indexes typically scale in a lineary fashion.

1) Is it true that for every index a copy of the table is created?

Partially. The columns that are indexed by a particular (non-clustered) index will be "copied" into index data structure (typically a B-Tree), but the rest of the columns will not. BTW, some RDMBSes support a form of index compression (such as Oracle, not sure about MS SQL Server), which may significantly "shrink" a footprint of a low-selectivity index.

As for a clustered index, the table and the index are actually the same data structure. So if all you have is a single index (i.e. just a primary key), making it clustered will typically lower the space required compared to a classical B-Tree / table heap combination that you get from a non-clustered index.

However, clustered index increases "copying" that is going on for all other (i.e. non-clustered) indexes. From MSDN:

"The key values from the clustered index are used by all non-clustered indexes as lookup keys and therefore are stored in each nonclustered index leaf entry."

If you cannot escape having many indexes, at least get rid of the cluster and use only non-clustered indexes.

2) 32 indexes add only 1.5 seconds for the same INSERT command. Does this average remain for 10,000 rows? or for 80 indexes?

With only 3,400 rows (or 10,000 for that matter), I suspect you are still well within the confines of the cache, which may mask the performance problem you'll probably have maintaining all these indexes for a larger data set. As always, you won't know for sure until you actually benchmark with representative amounts of data...

--- EDIT ---

3)when we have a Foreign key on a column SQL Server automatically create non-cluster index on it.this Index useful for quering or Joins?

It may or may not increase the performance, depending on how particular query is constructed. It may also be significant for cascading referential integrity (ON DELETE/UPDATE CASCADE).

If you can live without this index, removing it will increase the performance of modifications, as with removing any other index.