Tuesday, August 26, 2008

Heat: Why Use a Clustered Index

A client asked this: We created primary keys for every validation table but continue to get the 'Invalid Attribute / Identifier' error. Is there anything else that we can do?

Yes, you can create a clustered index on a validation table. You should be aware, however, that this will need to be performed anytime an edit set is committed or a quick customize is applied. It should also be done by an experienced database administrator.

To create a clustered index on a validation table:

* Open MSSQL Enterprise Manager.
* Select Tools>>SQL Query Analyzer.
* Ensure that your database is selected in the drop down at the top of the screen

Use the following query to create clustered indexes for tables that currently do not have an existing index: (Note: HEAT creates the index with a naming convention as follows: Field_Name0).

CREATE CLUSTERED INDEX [Index_Name] ON Table_Name ([Column_Name])

Use this query to create clustered indexes for tables that currently already have an existing index but that index is not clustered:

CREATE CLUSTERED INDEX [Index_Name] ON Table_Name ([Field_Name])
WITH DROP_EXISTING ON [PRIMARY]

Repeat the query for every table that does not currently have a clustered index

No comments: