Skip to main content

Search Value using Non-Clustered index: SQL Server

Similar to the clustered index, a non-clustered index also contains the index also contains the index key values and the row locators that point to the storage location of the data in a table. However, in a non-clustered index, the physical order of the rows is not the same as the index order.

Non-clustered indexes are typically created on columns used in joins and the WHERE clause. These indexes can also be created on columns where the values are modified frequently. The SQL Server creates non-clustered indexes by default when the CREATE INDEX command is given. There can be as many as 249 non-clustered indexes per table.

The data in a non-clustered index is present in a random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout a table. The non-clustered index tree contains the index keys in a sorted order, with the leaf level of the index containing a pointer to the data page and the row number in the data page.
  • The SQL Server perform the following steps when it uses a non-clustered index to search for a value:
  • The SQL Server obtains the address of the root page from the sysindexes table.
  • The search value is compared with the key values on the root page.
  • The page with the highest key value less than or equal to the search value is found.
  • The page pointer is followed to the next lower level in the index.
  • Steps 3 and 4 are repeated until the data page is reached.
  • The rows are searched on the leaf page for the specified value. If a match is not found, the table contains no matching rows. If a match is found, the pointer is followed to the data page and row-ID in the table and requested row is retrieved. 
Search Value using Non-Clustered index: SQL Server

The figure displays a non-clustered index present on the Eid attribute o the Employee table. To search for any value, the SQL Server would start from the root page and move down the B-Tree until it reaches a leaf page that contains a pointer to the required record. It would then use this pointer to access the record in the table. For example, to search for the record containing Eid E006 by using the non-clustered index, the following steps would be performed by the SQL Server:

  • The SQL Server starts from page 603, which is the root page.
  • It searches for the highest key value on the page, the page with a key value less than or equal to the search value, that is, to the page containing the pointer to Eid E005.
  • The search continues from page 602.
  • Eid E005 is found and the search continues to page 203.
  • Page 203 is searched to find a pointer to the actual row. Page 203 is the last page, or the leaf page, of the index.
  • The search then moves to page 302 of the table to find the actual row.

In an index, more than one row can contain duplicate values. However, if you configure an index to contain unique values, the index will also contain unique values. Such an index is called a unique index. You can create a unique index on a columns that contain unique values, such as the primary key columns.
A unique index can be clustered or non-clustered depending on the nature of the column


Popular posts from this blog

difference between structure and union in C Language

In c language article we will see the difference between union and structure. Both are the user define datatype in c language. See the table which is mentioned below: ASP.NET Video Tutorial Series Structure Union1.The keywordstruct is used to define a structure 1. The keyword union is used to define a union. 2. When a variable is associated with a structure, the compiler allocates the memory for each member. The size of structure is greater than or equal to the sum ofsizes of its members. The smaller members may end with unused slack bytes. 2. When a variable is associated with a union, thecompiler allocates thememory by considering the size of the largest memory. So, size of union is equal to the size of largest member. 3. Each member within a structure is assigned unique storage area of location. 3. Memory allocated is shared by individual members of union. 4. The address of each member will be in ascending order This indicates that memory for each member will start at different offset v…

Difference between Linear search and Binary Search in c language

SQL Video Channel : Download all SQL Video

Binary Search Linear Search Works only on sorted items. such as  1,2,3,4,5,6  etc
Works on sorted as well as unsorted items. 12,4,5,3,2,1 etc Very efficient if the items are sorted Very efficient if the items are less and present in the beginning of the list. such as Suppose your list items are : 12,3,4,5,1 and you want to search 12 number then you get beginning in the list. Works well with arrays and not on linked lists. Works with arrays and linked lists.
Number of comparisons are less More number of comparisons are required if the items are present in the later part of the array or its elements are more.

Memory representation of Linked List Data Structures in C Language

Memory representation of Linked List

             In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.

               Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:

      Suppose next node is allocated at an address 506, so the list becomes,

  Suppose next node is allocated with an address with an address 10,s the list become,

The other way to represent the linked list is as shown below:

 In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of next node. The last node …