locked
Few question for sql server indexes RRS feed

  • Question

  • User264732274 posted

    there is two type of index one is cluster index and another one is non-cluster index.

    1) what is unique index and how it is different from cluster & non-cluster index ?

    2) guide me in detail how index data is arrange for  cluster & non-cluster index with example ?

    3) discuss with example or sample like when we create index how data is searched for cluster & non-cluster index with example ?

    4) give me example when we should create cluster index and on what are the fields for which we should create non-cluster index ?

    discuss 4th point with good examples.

    5) some time people create non-cluster index for PK fields but i like to know the situation when people will create non-cluster index for PK fields. discuss 5th point with good examples.

    thanks

    Tuesday, December 8, 2015 7:00 AM

Answers

  • User525132329 posted

    sudip_inn

    1) what is unique index and how it is different from cluster & non-cluster index ?

    Both clustered and nonclustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value. For more information, see Create Unique Indexes.

    sudip_inn

    2) guide me in detail how index data is arrange for  cluster & non-cluster index with example ?

    • Clustered

      • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

      • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

    • Nonclustered

      • Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.  

      • The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key. 

      • You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns.

    There are tons, of examples online, if you would like help on a specific one, it would be ideal to post the relevant code/sample to discuss it further. Also look at the following diagrams for more detail:

    Clustered Index: https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

    Nonclustered Index: https://technet.microsoft.com/en-us/library/ms177484(v=sql.105).aspx

    sudip_inn

    3) discuss with example or sample like when we create index how data is searched for cluster & non-cluster index with example ?

    4) give me example when we should create cluster index and on what are the fields for which we should create non-cluster index ?

    discuss 4th point with good examples.

    There is no easy way to decide how to design indexes without evaluating the app itself. But again if you want in-depth information about how to do so, please look at the following link:

    https://technet.microsoft.com/en-us/library/ms190804(v=sql.105).aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 10, 2015 3:01 AM

All replies

  • User525132329 posted

    Take a look at this documentation:

    https://msdn.microsoft.com/en-us/library/ms190457.aspx

    5) some time people create non-cluster index for PK fields but like to know the situation when people will create non-cluster index for PK fields. discuss 5th point with good examples.

    Creating a non-clustered index on a PK field is usually not a motivation in and of itself. Usually the motivation is to define a clustered index on a non-PK field for more efficient lookups and since you can have only one clustered index, you are forced to make a non-clustered index for the PK field.

    Take for example that you store movies in a database and you create a view in your application that shows them in alphabetical order. Also assume that this is the default view and it is used heavily in your application. It would be ideal to create a clustered index on the movie title rather than a surrogate PK that is an integer or GUID. By creating a clustered index on the movie title, the database engine will store them in that sort order and the lookups become extremely efficient, If you want to lookup by the PK field, the non-clustered index will be utilized, which by assumption is used less frequently.

    Tuesday, December 8, 2015 7:26 AM
  • User264732274 posted

    i mention so many points in my post but you answer only one.

    i have seen people create non-cluster index on PK fields when people store PK value like A0001 or A0002.........A0010 etc or some time people store guid for pk field then they create non-cluster index too but i like to know the reason for which people create non-cluster index when pk fields has guid as a value or A0001 or A0002.........A0010 etc.

    if you know the reason then please share. thanks

    Wednesday, December 9, 2015 9:14 AM
  • User525132329 posted

    sudip_inn

    1) what is unique index and how it is different from cluster & non-cluster index ?

    Both clustered and nonclustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value. For more information, see Create Unique Indexes.

    sudip_inn

    2) guide me in detail how index data is arrange for  cluster & non-cluster index with example ?

    • Clustered

      • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

      • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

    • Nonclustered

      • Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.  

      • The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key. 

      • You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns.

    There are tons, of examples online, if you would like help on a specific one, it would be ideal to post the relevant code/sample to discuss it further. Also look at the following diagrams for more detail:

    Clustered Index: https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

    Nonclustered Index: https://technet.microsoft.com/en-us/library/ms177484(v=sql.105).aspx

    sudip_inn

    3) discuss with example or sample like when we create index how data is searched for cluster & non-cluster index with example ?

    4) give me example when we should create cluster index and on what are the fields for which we should create non-cluster index ?

    discuss 4th point with good examples.

    There is no easy way to decide how to design indexes without evaluating the app itself. But again if you want in-depth information about how to do so, please look at the following link:

    https://technet.microsoft.com/en-us/library/ms190804(v=sql.105).aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 10, 2015 3:01 AM