Difference between Clustered Index and Non clustered Index

Indexes-Indexing  is way to sort and search records in the table. It will improve the speed of locating and retrieval of records from the table.It can be compared with the index which we use in the book to search a particular record.
In Sql Server there are two types of Index
1) Clustered Index
2) Non Clustered Index

Clustered Index:– Clustered index physically stored the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined.
In a table only 1clustered index is possible.
In a clustered Index, the leaf node contains the actual data.

Non Clustered Index:- In case of Non clustered index it create a sperate list of key values (or created a table of pointers) which points towards the location of the data in the data pages.

In a table, we can create 249 Non clustered indexes in SQL 2005 and 999 Non clustered indexes in SQL Server 2008 and onward.

In a non clustered Index, the leaf node contains the pointer to the data rows of the table.

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Imp SQL Difference, Interview Questions, SQL Indexes, SQL Server and tagged , , . Bookmark the permalink.

31 Responses to Difference between Clustered Index and Non clustered Index

  1. Vivek Ji it's good and simplest description of difference between Clustered Index and Non clustered Index. Thank You.

  2. Priya R says:

    nice…simply superb

  3. Trinath says:

    Hi Vivek,
    If i have one clustered index and 10 non-clusterd index on a table.
    when i insert new data into table without disabling the indexes.
    do i need to re-build them after inserting the data?

  4. Trinath says:

    and how bad will be the performance if i insert the data in to a table with 10 indexes with out disabling them, compared to a table with no indexes and inserting the same data

  5. Siva Krishna says:

    Hi vivek simply super…

  6. Nice article!! Now we can have 999 non clustered index per table.

  7. admin says:

    Yes Vimal and I have added this in my article too:-)

  8. Really Nice Information,Thank You Very Much For Sharing.
    WordPress Development Company

  9. siva ram says:

    Can you please explain it by taking some examples

  10. Anonymous says:

    azure technology provides a cloud capabilities to your network. Azure can be done through saas and iaas. learn more in microsoft azure training

  11. Pingback: https://ligaemas.online/

  12. Pingback: 바카라사이트

  13. Pingback: บาคาร่า,gclub

  14. Pingback: agencia de viajes en managua

  15. Pingback: hashtags cloud

  16. Pingback: istanbul escort

  17. Pingback: Zobarstnieciba Riga

  18. Pingback: Thrift Store

  19. Pingback: dualtron thunder

  20. Pingback: Landscaping in Long Island NY

  21. Pingback: tree service

  22. Pingback: 안마기 렌탈비

  23. Pingback: 바카라사이트

  24. Pingback: tor search engine url

  25. Pingback: มาตรา 116

  26. Pingback: D.S.S CREATIONS WITH S.L CINE ART CREATE FIRST LOGO INTRO

  27. Pingback: You should feel pretty too

  28. Pingback: Togelsumo

  29. Pingback: Mitch Brownlie Queensland

Leave a Reply