?
Solved

Indexing SQL Database (server 2012)

Posted on 2016-08-23
3
Medium Priority
?
41 Views
Last Modified: 2016-08-26
I'm looking for ways to speed up my SQL database and think indexing may help.
What type of indexing do you recommend and what would the syntax be to create the index?
0
Comment
Question by:Eyal Filkovsky
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41767489
Indexes are per table not at database level. Every table should have an index, which is also a primary key(PK). Good candidates for PK are columns or groups of columns that are unique. A PK has to uniquely identify a row in a table. There are 2 main kinds of indexes in SQL server, clustered index and nonclustered index. A table can have only one clustered index, which is the index that determines the physical row order in a table and is also used as a reference by any other nonclustered index in the table. Clustered means that the index actually is the table (it is clustered with the data) and if you have other indexes they will not point directly to  a row but to the cluster index that identifies the row. You could think of nonclustered indexes as "indirect" indexes. In SQL server the PKs are by default created as clustered but they don't have to be. When it comes to what columns you want to create index on you should thing on how you will use and query the table. ALL the columns that will participate in JOIN operations have to be indexed. Foreign Keys, that is column that point to rows in related tables, usually used in joins, also have to have indexes. Columns that are frequently used in WHERE clause also need to be indexed.

This is just in a nutshell what you need to know about indexes but the subject is quite large and it takes time to fully understand.  You can start here:

https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx

For syntax and examples you can look here:

http://www.tutorialspoint.com/sql/sql-indexes.htm

You can also create indexes using the UI from Management Studio.
1
 

Author Comment

by:Eyal Filkovsky
ID: 41767697
Wheew... looks like I have some research to do. Thank you!
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 41767727
This is a big subject and definitely can't be covered in an EE question. It needs longer time to study. Later when you return with specific tables and columns we can help for sure. ScottPletcher is one of the best experts when it comes to indexes.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

718 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