Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-01 : 23:29:11
|
| Asma writes "what is difference between clustered & non clustered index?" |
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2002-01-02 : 01:15:24
|
| A search for "clustered index" in books online would provide this text for you:CLUSTEREDCreates an object where the physical order of rows is the same as the indexed order of the rows, and the bottom (leaf) level of the clustered index contains the actual data rows. A table or view is allowed one clustered index at a time.A view with a clustered index is called an indexed view. A unique clustered index must be created on a view before any other indexes can be defined on the same view.Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created.If CLUSTERED is not specified, a nonclustered index is created.Note Because the leaf level of a clustered index and its data pages are the same by definition, creating a clustered index and using the ON filegroup clause effectively moves a table from the file on which the table was created to the new filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. It is important that the filegroup have at least 1.2 times the space required for the entire table.NONCLUSTEREDCreates an object that specifies the logical ordering of a table. With a nonclustered index, the physical order of the rows is independent of their indexed order. The leaf level of a nonclustered index contains index rows. Each index row contains the nonclustered key value and one or more row locators that point to the row that contains the value. If the table does not have a clustered index, the row locator is the row's disk address. If the table does have a clustered index, the row locator is the clustered index key for the row.Each table can have as many as 249 nonclustered indexes (regardless of how they are created: implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX). Each index can provide access to the data in a different sort order.For indexed views, nonclustered indexes can be created only on a view with a clustered index already defined. Thus, the row locator of a nonclustered index on an indexed view is always the clustered key of the row. |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-02 : 01:47:15
|
| i think this discussion will help you more on understanding about indexes.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9071as Clarke suggested read thru the BOL on Using Clustered & non clustered Indexes. it is explained in good detail there.HTH----------------------------Anything which Doesn't Kills you Makes you Stronger |
|
|
|
|
|
|
index