Hi, Tree montha ago we elimin 1

[calico]

Hi,

Tree montha ago we eliminate cluster index option in all of our Sql tables.
Now I realize that it was not a good idea to eliminate all cluster index of our Sql tables (MS Sql
Server 7.0).
This suggession came from our supplier to improve performance of the system. We bought this
system. We didn't develop this system.

Now I can notice very bad performance. May be contention problem !
Also I identify some supplier's reports that do not work correcyly since have eliminated cluster
index.

Can we replace back the cluster index without problem ?
Does the time required to replace back cluster index is the same as the time that it took to eliminate
cluster index ?

Do we have to download and reload database ?

Thank you

Danny (I like very much this forum !) Danny Presse

 

[jnicho02]

Danny,
It will take much longer to create a clustered index than to remove it...In fact removing an index shouldn't take very long at all.

When you create a clustered index you are specifying the physical order in which the data is held in the table. You do this so that data ranges that you wish to query most often are physically next to one another, making the disk read faster. The 'create index' will therefore have to physically reorder the rows inside the table....this shouldn't be too bad for you as they are mostly in the right order already. Any new rows that you have added since dropping the index will physically be at the end of the table.

Estimating the time that a 'create clustered index' will take is not easy. It may be quick, or it may take ages.

Downloading and reloading the database will have no effect on the indexes. My home ---->

http://www.geocities.com/jnicho02/

My company ->

http://www.firstlightassociates.co.uk/

 

[TomSark]

You can add the clustered index have to a table at anytime, without reload a table or whatever...

Keep in mind that if you have existing non-clustered indexes, that they will be automatically rebuilt when you add the clustered index.

Also, make sure that you have 1.2 times the size of your existing table available, as this is the average amount of space required to build a clustered index. After it is built, then it will, on the average, consume about 5% of your table space. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[harryrich]

I found it very easy to change an index to a clustered index using the enterprize manager (check the box and save the table design). However, it took considerable time and my database (which had as its only user table the one being reorganized) more than doubled its allocation fot the database and log files. And I had trouble getting the log back to size. If you run into this last you should take a look at the FAQ for this forum.