Article
    C#
    ADO.Net
    .NET
    ASP.Net & Web Forms
    Custom Controls
    Web Development
    Exception Handling
    XML
    Database
    Security in .Net
    Testing
    Web Services
    Windows Services
    Windows Controls
    WCF
    AJAX
    WPF
    XAML
    Reporting
    Setup
    VB.Net
    LINQ
    JQuery
    SilverLight
    JavaScript
    HTML5
    Crystal Report
    Cloud Computing
    Share Point
    Visual C++
    MVC
    Android
    PHP
    Java
    HTML
    WordPress
    Joomla
    Products
    Drupal
    Windows Phone
    JSON
    LightSwitch
    iPhone/iPad
    Ruby on Rails
    IIS 7
    Windows 8
    CSS/CSS3
    Excel
    MS Access
    Shortcut Keys
    Visual SourceSafe
    Team Foundation Server
    API(s)
    Sencha-Touch
    Single Page App
    Bootstrap
Follow Us
Follow _MindStick_ on Twitter View MindStick Software's LinkedIn profile View MindStick Software's Facebook profile
Top Contributor
Advertisement
Advertise with Us
Mindstick
Article Article  Forum Forum  Blog Blog  Quiz Quiz  Beginner Beginner  Careers Careers  Contact Contact  Login Login  
Home | Product | Services | About Us | Interview | DeveloperSection | Submit an Article | Submit Blog

Home >> Database >> Index in SQL Server
Index in SQL Server
Index in SQL Server


by Arun Singh on 7/6/2011 3:41:46 PM

Views: 2368       Comments: 0

Index in SQL Server

Index:

 Index is most important concept of SQL Server database to increase performance of retrieving data from database. Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns.

Syntax: Creating Index on Table

-----SYNTAX DEMONSTRATION OF CREATING INDEX

CREATE INDEX <INDEX_NAME>

ON

<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>

Example: Creating Index on Table

---- DEMONSTRATION OF CREATING INDEX ON TABLE-----

CREATE INDEX TEST_INDEX

ON

USERLOGIN(ID)

Types of Index use in SQL Server:

There are many types of Indexes in SQL Server in which some important indexes are given as follows:

·         Unique Index

·         Clustered Index

·         Non-Clustered Index

Unique Index:

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself.

UNIQUE constraints allow SQL Server administrators to specify that a column may not contain duplicate values. When you create a new UNIQUE constraint, SQL Server checks the column in question to determine whether it contains any duplicate values. If the table contains preexisting duplicates, the constraint creation command fails.

Syntax: Creating Unique Index on Table

----SYNTAX DEMONSTRATION OF CREATING UNIQUE INDEX ON TABLE

CREATE UNIQUE INDEX <INDEX_NAME>

ON

<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>

Example: Creating Unique Index on Table

---- DEMONSTRATION OF CREATING UNIQUE INDEX ON TABLE-----

CREATE UNIQUE INDEX TEST_UNIQUE

ON USERLOGIN(ID,EmailId)

Syntax: Drop Index from table

----SYNTAX DEMONSTRATION OF DROP INDEX FROM TABLE

DROP INDEX <INDEX_NAME> ON <TABLE_NAME>

Example: Drop Index from table

---- DEMONSTRATION OF DROP INDEX FROM TABLE

DROP INDEX TEST_UNIQUE ON USERLOGIN

The benefits of unique indexes include the following:

*      Data integrity of the defined columns is ensured.

*       Additional information helpful to the query optimizer is provided.

Clustered Index:

Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. If there is no primary key in a table, you can add one clustered index to that table with CREATE CLUSTERED INDEX statement.

A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index.

With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

*      Can be used it for frequently used queries.

*      Provide a high degree of uniqueness.

Syntax: Creating Cluster index on Table

--- SYNTAX DEMONSTRATION OF CREATING CLUSTERED INDEX-------------

CREATE CLUSTERED INDEX <CLUSTERED_INDEX_NAME>

ON

<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>

Example: Creating Cluster index on Table

--- DEMONSTRATION OF CREATING CLUSTERED INDEX------

CREATE CLUSTERED INDEX TEST_CLUSTERED

ON

USERLOGIN(ID,EMAILID)

Syntax: Drop Clustered Index from table

----SYNTAX DEMONSTRATION OF DROP INDEX FROM TABLE

DROP INDEX <INDEX_NAME> ON <TABLE_NAME>

Example: Drop Clustered Index from table

---- DEMONSTRATION OF DROP INDEX FROM TABLE

DROP INDEX TEST_UNIQUE ON USERLOGIN

Non-Clustered Index:

A Non-Clustered index contains the index key values and row locators that point to the storage location of the table data. You can create multiple Non-Clustered indexes on a table or indexed view. Generally, Non-Clustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index. Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the Non-Clustered index to find the location of the data value in the table and then retrieves the data directly from that location.

This makes Non-Clustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries.

Non-Clustered Index implemented in following ways:

*      PRIMARY KEY and UNIQUE constraints.

*      Index independent of a constraint.

*      Non-Clustered index on an indexed view.

Syntax: Creating Non-Clustered Index on Table

---SYNTAX DEMONSTRATION OF CREATING NON-CLUSTERED INDEX--

CREATE NONCLUSTERED INDEX <NONCLUSTERED_INDEX_NAME>

ON

<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>

Example: Creating Non-Clustered Index on Table

---DEMONSTRATION OF CREATING NON-CLUSTERED INDEX--

CREATE NONCLUSTERED INDEX TEST_NONCLUSTERED

ON

USERLOGIN (ID,NAME,EMAILID)

Syntax: Drop Non-Clustered Index from table

----SYNTAX DEMONSTRATION OF DROP INDEX FROM TABLE

DROP INDEX <INDEX_NAME> ON <TABLE_NAME>

Example: Drop Non-Clustered Index from table

---- DEMONSTRATION OF DROP INDEX FROM TABLE

DROP INDEX TEST_UNIQUE ON USERLOGIN

 

Limit of indexes on a table:

 For SQL Server 2005:
1 Clustered Index + 249 Non-Clustered Index = 250 Index on a table
For SQL Server 2008:
1 Clustered Index + 999 Non-Clustered Index = 1000 Index on a table

 

Report Abuse Form
Reason:    
 

Title :
Comment :
Text ColorBackground Color
BoldItalicUnderline
LeftCenterRightJustify
Ordered ListBulleted List
IndentOutdent
Horizontal Rule
SubscriptSuperscript
HyperlinkImage
Design ModeDesign
View HtmlHtml
     
 
Latest Article by Arun SinghRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Latest BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Top Viewed ArticlesRSS Feed
    
    
    
    
    
    
    
    
    
    
Top Viewed BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
Latest Interview QuestionsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Total Online Users: 4942
Advertisement
MindStick SurveyManager
Advertise with Us
  
Copyright © 2014MindStick. All Rights Reserved.