Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

Imp SQL Difference Interview Questions SQL Indexes SQL Server

Difference between Clustered Index and Non clustered Index

Byvivekjohari

Jan 3, 2010 #SQL Advance Concepts, #SQL Important Differences, #SQL Indexes
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.

Trending
Exception handling in SQL Server

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

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

Related Post

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

Jan 1, 2025 vivekjohari
SQL Server

Database Partitioning in SQL Server

Dec 31, 2024 vivekjohari
SQL Server

What are the important python libraries?

Dec 31, 2024 vivekjohari
32 thought on “Difference between Clustered Index and Non clustered Index”

  3. 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?

    Reply

  4. 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

    Reply

  19. … [Trackback]

    […] There you will find 11739 more Information to that Topic: techmixing.com/2010/01/difference-between-clustered-and.html […]

    Reply

Leave a Reply

You missed

Career Coaching

How to Update Your Resume After a Career Break: A Strategic Approach

February 28, 2025 Megha No Comments
Career Coaching

The Importance of Soft Skills in the Modern Workplace

February 28, 2025 Megha No Comments
Career Coaching

Unlocking the Power of Resume Keywords: Your Guide to Standing Out

February 28, 2025 Megha No Comments
Career Coaching

Top 10 High-Demand Careers in India for 2025: Secure Your Future Today

February 28, 2025 Megha No Comments

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading