Solved

which columns do I choose to index

Posted on 2013-10-31
8
300 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher 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:
Scott Pletcher 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 43
SQL LINE CONTINUATION ISSUE 12 33
TSQL - How to declare table name 26 29
Rename a column in the output 3 10
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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