SQL Server Performance Forum – Threads Archive

doubt in creating index

I have a table for which I have defined the cust_id coloumn as primary key.Now clustured index is created on pimary key.
Is there is any necessity to create nonclustured index after the creation of clustured index

No, if you have cust_id as integer (not combined key)and clustered index is a unique one. BOL says: – "A clustered index is particularly efficient on columns that are often searched for ranges of values." Clustered indexes are also efficient for finding a specific row when the indexed value is unique. For example, the fastest way to find a particular employee using the unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column. –And Nonclustered indexes can be defined on a table with a clustered index, a heap, or an indexed view. In MicrosoftÂ® SQL Serverâ„¢ 2000, the row locators in nonclustered index rows have two forms: If the table is a heap (does not have a clustered index), the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The entire pointer is known as a Row ID.
If the table does have a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server 2000 makes duplicate keys unique by adding an internally generated value. This value is not visible to users; it is used to make the key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.
Because nonclustered indexes store clustered index keys as their row locators, it is important to keep clustered index keys as small as possible. Do not choose large columns as the keys to clustered indexes if a table also has nonclustered indexes.
Deepak Kumar –An eye for an eye and everyone shall be blind

You can start by using the Index Tuning Wizard AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

You don’t *need* to create additional nonclustered indexes. However, you might *want* to create some to speed up things. But this comes down to the queries you run against this table. I would second, that you should start by using the Index Tuning Wizard. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>