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?
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.