SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join Table Indexing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swraman
Starting Member

5 Posts

Posted - 04/20/2013 :  01:29:57  Show Profile  Reply with Quote
Hi,

I'm trying to learn more about sql, and am very new to it.

I have many junction tables that link the primary keys of two other tables. My question is which should I put an index on? Is there a general consensus on how to handle cases like this? Example would be viewing the permissions granted to a certain user, then viewing the permissions a user belongs to.

Is there any way to make these both run efficiently?

Is there any way to have SQL create an index on both tables separately, so it two index lists, one sorted by each index?

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/20/2013 :  02:34:06  Show Profile  Reply with Quote
the choice of indexes depends on what queries you frequently apply on tables, which columns queries target, selectivity of values in the table etc factors. So you need to first analyse and find the columns targeted by frequent queries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 04/20/2013 :  02:49:37  Show Profile  Visit jackv's Homepage  Reply with Quote
It is a wide topic - even if you do all the analysis - create the right indexes , maintenace is stiil required - as data is potentially changing all the time - which influences the Optimizer - some notes http://www.sqlserver-dba.com/2012/06/why-does-query-optimizer-choose-a-clustered-index-scan.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

swraman
Starting Member

5 Posts

Posted - 04/20/2013 :  13:51:36  Show Profile  Reply with Quote
Thanks. Exactly what I needed to know.

Is there any reason that SQL doesn't implement maintaining two indexed tables, one sorted by one column and the other sorted by another column? I can see many applications where that would be useful - for instance when searches are often done both ways but items are rarely added to the table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/22/2013 :  01:35:46  Show Profile  Reply with Quote
sql will maintain separate index tables for non clustered indexes. for clustered index it will sort the original table itself based on index columns which is why you can have only a single clustered index on a table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

swraman
Starting Member

5 Posts

Posted - 04/22/2013 :  23:15:08  Show Profile  Reply with Quote
So, if I have two columns in a table that I will be doing searches on, say" email address" and "username".

If I create an index on Email Address and on username, it will maintain sorted tables of both of these (assuming neither is my clustered index)?
Go to Top of Page

swraman
Starting Member

5 Posts

Posted - 04/22/2013 :  23:19:38  Show Profile  Reply with Quote
Or, would it be better to have two tables: The User table with the index on Email Address, then a second 2 column table that maps username to email address (1:1 mapping).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/23/2013 :  12:43:17  Show Profile  Reply with Quote
quote:
Originally posted by swraman

So, if I have two columns in a table that I will be doing searches on, say" email address" and "username".

If I create an index on Email Address and on username, it will maintain sorted tables of both of these (assuming neither is my clustered index)?


not sorted tables. but sorted list of just the index column values and each of value will point to clustered index position (if one present) or to actual position where the rows containing value exists

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000