Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Julesa non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough