question

zillabaug avatar image
zillabaug asked

what is the best way of handling tables with no clusterd index?

I have a database in SQL 2000 139 tables that have no clustered index.the tables in the database have no referential integrity( ie no primary key -foreign key relationship established). any suggestion as to how to handle them? what is the best practice of handling this? what can I do to these tables to optimize the database?
sql-server-2000performance-tuningquery-analyzeroptimizer
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
MAXKA avatar image
MAXKA answered
As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases–such as an identity column, or some other column where the value is increasing–and is unique. In many cases, the primary key is the ideal column for a clustered index. No clustered index means, the data would be scattered anywhere sql server finds a space to fit in. This generally implies a good sign for inserts but not that good for select, updates and deletes. I would suggest, visit MSDN page for benefits of having clustered indexes over not having them, because that will help you in understanding what exactly you need from clustered index. See if you have performance issues, so that Clustered index will help you in enhancing the queries response as well. As a solution, i believe you should go and look where clustered index can be created: That is if the tables out of the ones you listed are actively queried, then go ahead and create the correct and required index, and if tables are not queried like scans seeks and updates are null on them, you may not need to be. Rest you need to analyse and fir whatever suits you best as having clustered index is never going to harm that much compared to the ones where there is no clustered index.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Excellent answer. I'll only add a couple of things. Best if the clustered index is unique, but it doesn't have to be. Also, make sure it's used because you only get one, so better it goes on a column or columns that aren't monotonically increasing if it's going to be used by your queries.
1 Like 1 ·
Thanks a lot MAXKA
0 Likes 0 ·
thanks grant
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.