• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

which columns do I choose to index

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
al4629740
Asked:
al4629740
7 Solutions
 
Asim NazirCommented:
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
 
Louis01Commented:
0
 
AnujSQL Server DBACommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
David ToddSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
al4629740Author Commented:
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
 
David ToddSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now