Indexing SQL Database (server 2012)

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?
Eyal FilkovskyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ZberteocConnect With a Mentor Commented:
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
 
ZberteocCommented:
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
 
Eyal FilkovskyAuthor Commented:
Wheew... looks like I have some research to do. Thank you!
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.

All Courses

From novice to tech pro — start learning today.