SQL SERVER BASICS TUTORIAL -PART 5

* Indexes are used by the query to retrieve the data very quickly. We can create the index on both the tables and views.

                * Indexes are just like the index in the book. In case of book if u want to go for the specific chapter, first we look up the page number in the index then we will go for the specific page to locate that chapter. if suppose u don't have the page index, we need to look up the each and every page to locate  the specific chapter right.

                Consider that we are having a table with millions of records and i need to find the records by specific column and also we don't have the index on  that column   then what will happen is it will scan each and every row from the table. So this type of table scan will definitely hurt the performance right.

 

 

*/

 Run the below command to create the customerinfo table without idenity column :-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[CustomerInfo](

                [CustomerID] [int] NOT NULL,

                [FirstName] [nvarchar](50) NULL,

                [LastName] [nvarchar](50) NULL,

                [Age] [int] NULL,

                [Gender] [varchar](10) NULL,

 CONSTRAINT [PK_CustomerInfo] PRIMARY KEY CLUSTERED

(

                [CustomerID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

If we look at the above customerinfo table, the [customerid] column is marked as primary key column.

Once we mentioned the particular column as primary key column, then by default clustered index will be created on that column.

To check it out whether index has been created or not run the following command.

SP_HELPINDEX  CustomerInfo

RESULT:-

PK_CustomerInfo            clustered, unique, primary key located on PRIMARY      CustomerID

Clustered index is nothing but it will determine or affect the physical stored order of the table.

If we look at the above customerinfo table, the customerid column is marked as primary key column .once we mentioned the particular column as primary key column, then by default clustered index will be created and then the data will be sorted by customerid column physically.

To check it out run the following command...if u look at the below script, you can come to know that we are trying to insert the records in a non-sequential  manner .even if you try to do so, it will be stored sequentially only as we have the clustered index on the customer  column.

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (3, N'Steve', N'Waugh', 34, N'Male')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (2, N'Shane', N'Watson', 32, N'Male')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (4, N'Peter', N'Siddle', 23, N'Male')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (1, N'Mone', N'Morkel', 44, N'Male')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (6, N'Serena', N'Williams', 23, N'Female')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (5, N'Maria', N'Sharapova', 25, N'Female')

To check It out run the following command:-

SELECT * FROM CustomerInfo

/*

CustomerID                   FirstName                LastName            Age        Gender

1                                              Mone                    Morkel                  44            Male

2                                              Shane                   Watson                  32            Male

3                                              Steve                    Waugh                  34            Male

4                                              Peter                     Siddle                   23            Male

5                                              Maria                    Sharapova            25            Female

6                                              Serena                  Williams               23            Female

*/

/*

                * We can have only one clustered index per table..but we can create clustered index on more than columns.This called composite clustered index.

                 * To create the clustered index on more than first we need to delete the existing clustered index on customerinfo table.

                * To do that just expand the customerinfo table in object browser .then  expand keys section and delete the primaykey or expand the indexes section and delete the clustered index.

To drop Index using query:-

 

  DROP INDEX TABLENAME.INDEXNAME

 

*/

Basic Syntax To Create The Index:-

CREATE INDEX [CLUSTERED OR NONCLUSTERED] INDEXNAME ON TABLENAME(COLUMNNAMELIST)

To Create The Clustered Index On More Than One Column:-

CREATE CLUSTERED INDEX INDX_CustomerInfo_GENDER_AGE ON CustomerInfo(GENDER DESC ,AGE ASC)

To Check It Out Whether Index Has Been Created Or Not Run The Following Command:-

SP_HELPINDEX CustomerInfo

Now run the following command and see the result:-

SELECT * FROM CustomerInfo

/*

CustomerID                 FirstName  LastName           Age        Gender

4                                              Peter     Siddle                     23            Male

2                                              Shane   Watson                   32             Male

3                                              Steve    Waugh                    34            Male

1                                              Mone    Morkel                   44            Male

6                                              Serena  Williams                 23            Female

5                                              Maria    Sharapova             25             Female

*/

/*

 If we look at the above result, you can come to know that clustered index will affect the physical stored order of the data in the table.

                As we created clustered index on gender and age column, the data will be sorted by gender in descending first and then will be sorted by age column in ascending order.

*/

/*

                                Non-clustered index is just like a book only. Index is stored in one location and the data is stored in another location. The index will have the pointer to locate the actual data .It won't affect the physical stored order of the data in the table.

 

*/

To Create Non-Clustered Index:-

CREATE INDEX INDX_CustomerInfo_LASTNAME ON CustomerInfo(LASTNAME)

 --OR—

CREATE NONCLUSTERED INDEX INDX_CustomerInfo_LASTNAME ON CustomerInfo(LASTNAME)

   Nonclustered key word is the optional one.

To Check It Out Whether Index Has Been Created Or Not Run The Following Command:-

SP_HELPINDEX CustomerInfo

/*

INDX_CustomerInfo_GENDER_AGE    clustered    located on PRIMARY                Gender(-), Age

INDX_CustomerInfo_LASTNAME          nonclustered   located on PRIMARY           LastName

*/

/*

                Once We Created Non-Clustered Index On The Lastname Column The Data Will Be Sorted And Stored Separately By Lastname Column With Row Address:-

  EX:-

 

Siddle       -> ROW ADDRESS

Watson                   -> ROW ADDRESS

Waugh     -> ROW ADDRESS

.

.

.

Here ROW ADDRESS is nothing but it is just like a Hexa- decimal number.

                Whenever we try to retrieve the data from customerinfo table by lastname , first it will look at the data from the index location and then fetch the data from  actual physical table by using its row address.

                Non clustered index is slower than clustered index as it has to extra look up to fetch the data.

                Note: we can have as many as non clustered indexes per table.

 

                Note: Index sometimes will hurt the performance also if u have more indexes on the specific table and do lot of modification (i.e. Insert,update or delete) on   the table as well. So we need to create the index on appropriate column only. And also we should not create more indexes on table if lot of modification happened.

 

 */