Home » SQL Server

Difference Between clustered ,non-clustered Indexing and relation of Foreign key with non-clustered


    I am working on sql server 2008 and i am very much confused about non-clusterIndexing ,as i know about the benefits/advantages of Clustere Indexing on a Table and there must be  one cluster Index in a Table But I dont have clear knowledge about Non-ClustereIndexing .I just Know that can be  249 in a table but my issue is:

1)Is Non-Cluster Index columns and foreign key columns are same thing

2) how Non-Cluster index helps us  to improve sqlsever Performance.

3) How Much It can be in a table and can we user it on any type of column or only Interger type of Column.

4) Which is the best way to use it

Plz give me complete knowledge about Non-Cluster Indexing..Thanks alot



4 Answers Found


Answer 1

For a general description of clustered  and non-clustered  indexes, read the two sub topics linked from this page: http://msdn.microsoft.com/en-us/library/ms179613(SQL.100).aspx. Non-clustered indexes and foreign  keys are different things. For general considerations on using non-clustered indexes, see http://msdn.microsoft.com/en-us/library/ms179325(SQL.100).aspx.

Actually, there is no requirement to have a clustered index on a table, but almost all tables support better performance if they have a clustered index.

Indexes in general increase performance by reducing I/O. Without indexes, SQL Server would always have to read all of the rows in a table to find the subset of rows that have the values specified in joins or WHERE clauses. If you have indexes covering the columns referenced in the joins and WHERE clauses, sql  Server can use the indexes to read only the rows that match the query conditions. For example, say there is an Employee table with 100,000 employees, and you execute:

SELECT * FROM Employee WHERE EmpID = 12345

Without an index, SQL Server would have to scan through all the rows in the table looking for the one row that has the EmpID value 12345. On average, each query would have to read half the rows in the table, or 50,000 rows. If you have an index, SQL Server usually only has to read 1-3 index pages, and then the data page with the one row.

The main difference  between a clustered and non-clustered index is that for a clustered index the data rows are sorted in sequence for the index key. The data rows are essentially the bottom level of the clustered index. For a non-clustered index, the data rows are not sorted in the index key  sequence. The bottom level of the non-clustered index is a set of pointers to whatever rows match each key in the index.

To design indexes, you have to think through all the ways your application is going to access the data in a table; which columns in the table are going to be referenced in all the queries that reference the table. You then create indexes to cover the sets of columns that are going to be referenced in joins and WHERE clauses. Since you can only have one clustered index per table, you generally make the index that will satisfy most of the queries the clustered index, and all the others non-clustered indexes. For more info on quidelines for deciding which index to make a clustered index, see http://msdn.microsoft.com/en-us/library/ms190639(SQL.100).aspx.


Answer 2

When you create a non cluster index it's depend on operations on table.

Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes

Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as few columns as possible

Please refer the below link for more info



Answer 3

Thanks ....

    Your information will help me alot


Answer 4

Thanks alot for such great information


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure