STUDENTS 3K

  • Home
  • Students+
    • Engineering Students
    • Education Loan
    • Study Abroad Guide
    • Projects Download
  • Freshers
    • Aptitude Q & A
    • Placement Papers
    • Verbal Ability
    • Interview Questions
    • IT Company Details
    • Job Updates
  • Study Resources
    • Career Guidance
    • LAB Programs
      • C Programs
      • CPP Programs [C++]
      • Java Programs
    • Question Papers
    • Learn English
    • Notice Board
  • More –>>
    • Love calculator
You are here: Home / Interview / SQL: Clustered vs non clustered Index

SQL: Clustered vs non clustered Index

An index in SQL may be defined as a structure linked with a particular table and is stored on the disk, mainly created to enhance the speed of the retrieval of data. The key for an index in SQL is created from one or more than one column present in a particular table or even view.

There are two types of indexes used in SQL and they are as follows:

1. Clustered Indexes: This type of Indexes are used to store and sort out the rows of data in a specific table or view basically according to their key values. Only one clustered index is allowed in SQL for one precise table because, the sorting out of the rows of data can be done in only one particular order. If a table has a clustered index linked to it in SQL then this table is known as Clustered Table. In case, of the absence of a clustered index the rows of data or data rows get stored in heap which is a totally unordered structure.

  • Related: SQL & .NET Interview questions and Answers

2. Non-Clustered Indexes: The second type of Index in SQL is known as Non-Clustered index. This type of Index contains the key values known as non clustered index key values and each of this key value has a pointer directed towards the data row containing that particular key value. This pointer which is there in the row of a non clustered index is known as a row locator. The basic structure of this row locator is based on the way the pages are stored. If the data pages are stored in a heap then the row locator will be the pointer to the specific row but if these data pages are stored in a clustered table then this row locator will be the clustered index key.

Read: More Interview Questions

As we have understood the basic concept of clustered indexes and non-clustered indexes in SQL, let’s go through the basic difference between these two types of indexes:

1. The basic difference between a clustered index and a non clustered index in SQL is that, one table can have exactly one clustered index associated with it but a single table can be associated with more than one non clustered indexes and the number can be as large as 250.

2. Another important difference between these clustered and non-clustered indexes is that in case of databases like SQL server the clustered index is made or created on PRIMARY KEY constraint whereas when it comes to non clustered index, it can be created on any key or in other words this type of index is not declared while specifying the PRIMARY KEY Constraints.

3. A clustered Index basically contains the data that means the rows in there every leaf node, but in case of the non clustered index, it contains the pointer to the specific data in the leaf node which adds one more step to the whole process of retrieving the data. Also, the clustered Index sort all the rows of data physically but this type of physical sorting of all the data is absent in case of non-clustered rows.

4. When it comes to clustered index, no separate storage other than the table storage is required to store the rows as in this case the rows are sorted and stored on the index key only. In case of non-clustered index, it requires different and separate storage space than of table.

Filed Under: Interview Tagged With: Index, Interview, SQL, SQL Interview questions





Random Materials :

  • Data Mining and Data Warehousing
  • Difference Between Class and Primitive Types
  • Database Roles in SQL
  • CASE Expression in SQL
  • Concurrent Update Problem

Follow Us on Facebook

Advertisement

Company Profile

  • About Students3k
  • Contact Us
  • Earn Money Online
  • Website Archive Page
  • Privacy Policy
  • Disclaimer

Categories

  • Important Programs
  • Study Abroad
  • IT Companies
  • Career Guidance for Students
  • Teachers
  • Verbal Analogies

We Are Social

  • Facebook Fans
  • Twitter Follow
  • Google Plus
  • Pinterest Page

© Copyright 2012 - 2016 Students3k.com · All Rights Reserved · Designed By Benefits

Show