Solved

Indexing SQL Database (server 2012)

Posted on 2016-08-23
3
20 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
Comment Utility
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
Comment Utility
Wheew... looks like I have some research to do. Thank you!
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
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.

Join & Write a Comment

Every now and then, Microsoft does something that totally impresses me. It doesn't happen often, but in this case I must say I am thoroughly impressed with Windows Server Backup. One of the long time issues with Windows Backup has been the ability t…
OfficeMate Freezes on login or does not load after login credentials are input.
In this Micro Tutorial viewers will learn how to restore their server from Bare Metal Backup image created with Windows Server Backup feature. As an example Windows 2012R2 is used.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

763 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

5 Experts available now in Live!

Get 1:1 Help Now