Use or Not use Indexes

What factors influence creation of indexes?

When the table is large in size (row count)

  • Contains millions if not 100’s of millions rows

When the table is used frequently

  • The table is used more than just infrequently

When the columns are frequently used in the WHERE clause

  • The query against the database has where clause

Columns that are frequently referenced in the ORDER BY and GROUP BY clauses

  • If you’re sorting data, then its beneficial

Indexes should be created on columns with a high number of unique values

  • Create indexes on columns that are all virtually unique such as integer

Create a single column index on single where clause query

  • If the where clause has a single column then create a single index

Create a multiple column index on multiple where clause query

  • If the where clause has multiple columns then create multiple index

Build index on columns of integer type

  • Integers take less space to store, which means the query will be faster

The order of the composite index matters, so place the column in the index as with the where clause

  • Use the column with the lowest cardinality first, and the column with the highest cardinality last, which means keep the unique column first and less unique column last (select distinct col1 from table)

Testing is the key in determining what will work for your environment. Play with different combinations of indexes, no indexes, single-column indexes, and composite indexes.

When Should Indexes Be Avoided?

Indexes should not be used on small tables

  • Smaller tables do better with a table scan

Indexes should not be used on columns that contain a high number of NULL values.

  • Maintenance on the index can become excessive

Don’t use an index that will return a high percentage of data

  • Few distinct values such as gender

Creating indexes come with a cost

  • Indexes take up disk space
  • INSERT, UPDATE, and DELETE operations become slower, since the database system need to update the values in the table, and it also needs to update the indexes

So when should we create an index? The first answer to this is when there is large amount of data (but other considrations)

If we create an index how does the clustered index help us retrieve last name data faster? bu sorting the lname column you alphabetize the lname and as such, if you encounter two SAME lname, they will be next to each other.

Create a clustered index on lname to sort the data. This will improve retrieval of lname data

USE [SQL2]
GO

--create a table and use the SQL Data Generator to insert 1 million rows

--CREATE TABLE [dbo].[People2](
--	[Fname] [varchar](20) NULL,
--	[Lname] [varchar](20) NULL,
--	[Phone] [varchar](20) NULL
--) ON [PRIMARY]

--GO

--Use SQL Data Generator to poulate 10 million rows
--select count (*) from people2
--15,000,000 rows

--When to create/use and not to use an index.  Find the phone number for lname Lambert   --<< don't use

SELECT TOP 10 [Fname]
      ,[Lname]
      ,[Phone]
  FROM [SQL2].[dbo].[People2]


--When to create/use and not to use an index.  Find the phone number for lname Conrad --<< don't use

  SELECT TOP 100 [Fname]
      ,[Lname]
      ,[Phone]
  FROM [SQL2].[dbo].[People2]

 --When to create/use and not to use an index.  Find the phone number for lname Sanders  --<< don't use


SELECT TOP 1000 [Fname]
      ,[Lname]
      ,[Phone]
  FROM [SQL2].[dbo].[People2]

   --When to create/use and not to use an index.  Find the phone number for lname Robertson  --<<  use

  SELECT  [Fname] 
      ,[Lname]
      ,[Phone]
  FROM [SQL2].[dbo].[People2]
  Where lname = 'Robertson'


USE [SQL2]
GO

CREATE CLUSTERED INDEX [Ind_People2_Lname] 
ON [People2]([Lname] ASC)

  SELECT   [Fname] 
      ,[Lname]
      ,[Phone]
  FROM [SQL2].[dbo].[People2]
  Where lname = 'Robertson'