Solved

Indexing SQL Database (server 2012)

Posted on 2016-08-23
3
29 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
  • 2
3 Comments
 
LVL 26

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 26

Accepted Solution

by:
Zberteoc earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

What to do when Windows Update is not working correctly? What tools can I use to detect the cause of the malfunction problem? What does this numeric error code mean? These and other questions that you have been asking in the past are answered here (…
Resolve DNS query failed errors for Exchange
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…
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…

867 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

19 Experts available now in Live!

Get 1:1 Help Now