Solved

which columns do I choose to index

Posted on 2013-10-31
8
288 Views
Last Modified: 2013-11-04
I am going to index my table in SQL 2012.  How do I determine which columns to index?  Can I index too many columns?  Can an index ever be done incorrectly and make it slower?  Also, can I remove an index with no problem if I choose to do it differently?
0
Comment
Question by:al4629740
8 Comments
 
LVL 10

Assisted Solution

by:Asim Nazir
Asim Nazir earned 72 total points
ID: 39616114
Hi,

We have different types of Indexes available. Two of them are Clustered and Non-clustered.

Clustured: Physically sorts table data and there can be one and only one Clustered index on a table.
Non-clustered: You can have more than 1 such indexes on a table.

Normally, Numeric columns are considered most suitable for a Index. If you go for varchar column type based indexes then it can make data fetching slow.

See this for more details.

I hope it helps.
0
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 72 total points
ID: 39616128
0
 
LVL 15

Assisted Solution

by:Anuj
Anuj earned 71 total points
ID: 39616134
How do I determine which columns to index?
Index are created on columns where you do most of the searches\reads like, Columns in where clause, Columns in ON clause ( INNER JOIN  Table ON Column), Columns in order by, group by and other aggregates. Simply the index columns should be carefully chosen based on your queries and searches. Be aware that too many indexes will causes overhead for Inserts,Updates and Deletes.

Can I index too many columns?
Yes you can have multiple columns as index Keys. Eg. CREATE INDEX IX_NCL_Employee_FirstName_LastName ON Employee(FirstName, LastName)

Above statement will create index on Employee table with Index columns FirstName and LastName. Queries which searches for FirstName and LastName will get benefited of this Index.

Can an index ever be done incorrectly and make it slower?
Yes, Incorrect and Fragmented Index makes your query slower, when INSERTS, UPDATES and DELETES occurs, there is an overhead of updating the indexes too.

You should do proper maintenance like defragmenting and find & removing unused indexes etc

can I remove an index with no problem if I choose to do it differently?
Before changing the index, you should check the usage of the index like how many times the indexes was referenced etc. If it is seldom used and there isn't any benefit of having them then you are safe to remove.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 142 total points
ID: 39616252
Hi,

What does the table look like?

A good place to start is  this:

Each table has an int identity that matches the name of the table. A clustered primary key is created against this column.
Each column that has a foreign key relationship is indexed.
Consider indexing key date columns

example
-- <servername>
use ExpertsExchange
go

if exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.Client' )
		and type in( N'U' )
	)
	drop table dbo.Client
;
go

if not exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.Client' )
		and type in( N'U' )
	)
	create table dbo.Client (
		ClientID int identity constraint pk_Client primary key clustered

	) on [PRIMARY]
go

if exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.Sales' )
		and type in( N'U' )
	)
	drop table dbo.Sales
;
go

if not exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.Sales' )
		and type in( N'U' )
	)
	create table dbo.Sales (
		SalesID int identity constraint pk_Sales primary key clustered
		, SalesDate int
		, ClientID int constraint fk_Sales_ClientID foreign key references dbo.Client( ClientID )
		, Quantity float
		, Description nvarchar( 50 )
		, LinePrice money
		, etc nvarchar( 100 )
	) on [PRIMARY]
go

create index idx_Sales_ClientID on dbo.Sales(
	ClientID
	)
go

create index idx_Sales_SalesDate on dbo.Sales(
	SalesDate
	)
go

Open in new window


While several columns are individually indexed, its unlikely that there will be a need to search on sales for the description, or the quantity, or many other things that are part of a sale, while the saleID, SalesDate, ClientID are often used in joins or searches.

HTH
 David
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 143 total points
ID: 39617681
>> How do I determine which columns to index? <<

Ask your DBA :-) .

The most critical index you choose is the clustered index, which should ALWAYS be carefully chosen based on the data in that specific table and how it will be used.  NEVER base the clustered index on a dopey "rule" that a clustered index should "automatically" (by default) be an IDENTITY column (or a uniqueidentifier or any other type of pre-ordained column).   NEVER, NEVER, NEVER!  The clus index is far too important to be based on a rote, nursery-rhyme like saying.


>> Can I index too many columns? <<

Absolutely, it happens all the time.  You must periodically review missing index info and index usage stats to determine whether you need to make index changes.


>> Can an index ever be done incorrectly and make it slower? <<

Again, absolutely.  The first key column is the critical one for lookup success; if it doesn't match, the index is vastly less useful.


>> Also, can I remove an index with no problem if I choose to do it differently?  <<

Nonclustered indexes, yes.  
Clus index no: a clus index is the table, and thus removing it requires rebuilding the entire table and all nonclus indexes.  Adding a clus index also requires rebuilding the entire table and nonclus indexes.  That's another reason it's so important to determine the correct clus index for any table.
0
 

Author Comment

by:al4629740
ID: 39618063
Can I just avoid doing a clustered index and just setup unclustered indexes?

If I use the Database Tuning Advisor, will it just tell me what I need to index?
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 142 total points
ID: 39620258
Hi,

For performance, the key as Scott mentioned above is getting the clustered index right is key. For many smaller databases etc, the dopey rule I mentioned above isn't a bad place to start.

The index tuning wizard needs a load to work with, that is, it needs some idea of the queries against the tables in question. So as a base design tool isn't very good.

And the other thing, this wizard isn't particularly smart, so do test its recommendations!

HTH
  David
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 143 total points
ID: 39621597
>> For many smaller databases etc, the dopey rule I mentioned above isn't a bad place to start. <<

Actually, a particularly terrible place to start is that any table even has a "default clustered index:".  That is the problem, because NO table has a  "default clustered index".  EVERY table needs a properly chosen clustered index.  The 100% wrong time to be lazy is in choosing a clustered index!


SQL Server manages tables with clustered indexes better than heaps (a table without a clustered index is called a "heap").  Also, you most often get good performance gains from the correct clustering index.  Likewise, you can severely hurt your performance from the wrong clustered index ... which is almost always a "default clus index" created on an IDENTITY column.

Database Tuning Advisor will give you recommendations, but it recommends far too many indexes.  You'll still need someone who understands it to filter through all that.

If you don't have anyone available, use this forum and/or others to get at least some type of knowledgeable index recommendations.


Again, you can most often add/remove/change nonclus indexes later very easily, based on usage stats.  But the clus index is very difficult to change and has the most affect on performance.  Both those reasons combined are why it's so critical to get the clus index right.  That's also why it's completely backwards to just slap a clus index on identity without thinking about it and then concentrate on the nonclus indexes.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now