What is non clustered index and what is B-tree structre in index.

Question

what is non clustered index. can i make non clustered index using 2 table of 4 columns. For example 2 table is first is Customer and second is Employee table. 4 colums from both table like cust_name, cust_id, emp_id, emp_name. can i make non clustered index using these columns.

Jignesh Trivedi · Answer

Hi A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes can be created using one or more columns of a database table providing the basis for both rapid random lookups and efficient access of ordered records. The data is present in random order but the logical ordering is specified by the index. The data rows may be randomly spread throughout the table. The non-clustered index tree contains the index keys in sorted order with the leaf level of the index containing the pointer to the page and the row number in the data page. In non-clustered index: * The physical order of the rows is not the same as the index order. * Typically created on column used in JOIN WHERE and ORDER BY clauses. * Good for tables whose values may be modified frequently ==> non clustered index on multiple table is not possible but other solution is create view and define index on it hope this help.