Please tell me more about Index

Please tell me more about Index

Post by Tomm » Sat, 12 May 2001 19:38:22

Hi there,
My question is creating a Index on a large table (col1,col2,... col20)
(10000000 records).  I found it  toke too long to create an index on the
large table (4 mins) and  it also toke about 3 mins to delete an Index. Is
it normal?
 Say I have a cluster index on the table (col1,col2), now i  want to "select
* from table where col5= '1234' ",so I add index on col5. Soon I want to
"select * from table where col4='234' ",so I add a index on col4 and delete
the index on col5, because book on line suggest user should not keep too
much index on one table. I think Spending too much time to add or delete a
index  might not worth , how to balance it?  please advice.



Please tell me more about Index

Post by Tobias Thernstr? » Sun, 13 May 2001 05:25:53

You should keep the indexes that are being used, adding and deleting indexes
will cause great locking problems and is not a way to go. What BOL means is
drop indexes that you know are not being used, do not add and delete indexes
for each individual query. Remember that indexes may harm your write
performance, so having more than around 5 indexes (ofcourse depending on
size, columns etc.) is not advisable on write intensive tables.

Hope this helps!

/ Tobias
SQL Server MCT


1. Can someone please tell me...

why this script doesn't work.

EXECUTE sp_dropdevice 'contacts', DELFILE

EXECUTE sp_addumpdevice 'disk','contacts','c:\dumps\contacts.dmp'


When I run it I get the following output.

File: 'c:\dumps\contacts.dmp' closed.
Device dropped.


The system cannot find the file specified.

(1 row(s) affected)

Physical file deleted.
'Disk' device added.

The above I understand, it's the bit below that's causing the

Msg 3201, Level 16, State 1
Can't open dump device 'c:\dumps\contacts.dmp', device error or device
off line. Please consult the SQL Server error log for more details.

When I view the folder there is nothing in it. This may be the correct
action I don't know. Can someone please help.

Thanx in advance.


2. dead mouse

3. Can I tell WHEN an Index was created on a table

4. Indexes and Relationships

5. How to tell if a given index exists on a table

6. distinct count in Aggregate function?

7. SELECT statement drove me crazzzyy - Could someone please tell me the different

8. FW: User Group Meeting Sep.. 23 - RSVP Request

9. Please tell me how to do that:

10. Please tell me why I can't compile or run sdk

11. Should I tell server that index is unique ?

12. sb please Tell me Why

13. Please tell me where can I use variables ?