how to determine which columns to INDEX?

If the database has been in production and statistics have been generated, try using the built-in index tuning wizard.

These are must

Project.projectID (PK)
Users.UserId (PK)

Project_Users : ProjectId, UserId (PK) gives answer for find role for project pand user u. Also what users in project p

Optional

Users : UserId, ProjectId (AK) gives answer for find projects for user u

oops, I deleted some wording I guess, last line above should read as

Optional

Project_Users : UserId, ProjectId (AK) gives answer for find projects for user u

are there any downgrades to assigning indexes? If i assign an index which i shouldnt be assigning will there be a perfomance downgrade?
thx

yes there would be... but negligeble... when updating, deleting, inserting a bit more work will be needed on db side... rearrange the indexes... more indexes more work... but if tables ara small dont worry... if you have millons of records with lots of multiple columns indexes, then you should worry...

Final review on thos post, and my answer is:

The user searches in the Project table by ProjectId
Project.projectID (PK)

The User searches in the Users table by UserId, Fname or Lname.
Users.UserId (PK)
Users.FName (IX)
Users.LName (IX)

The User searches in the Project_Users table by UserId.
Project_Users : UserId, ProjectId (PK) gives answer for "find projects for user u"

HainKurt:, question for you.

>>>The user searches in the Project table by ProjectId
Project.projectID (PK)

shouldnt projectID  in this table be set as index?

thx

PK is already an index, just a special one (which physically sorts the records and it will be unique)
Each table should have one PK...

Also I think if a column contains duplicate values
It cannot be indexed, right?

- if you are a DBA, index maintenance is another aspect to look into. i'm sharing an article that covers what you need to maintain the existing index if it helps:
http://www.mssqltips.com/tip.asp?tip=1339

your question on "are there any downgrades to assigning indexes? If i assign an index which i shouldnt be assigning will there be a perfomance downgrade?"

- in that same article, i would suggest you to look into topic 4. Over indexed tables and 5. Under indexed tables which covers on too many indexes on a table may degrade performance and too few will results in a full table scan which will also degrade performance. these tips are good when planning to create index for your tables.

if you create a PK on some columns, they cannot be duplicated

for example users table with userid as pk
you cannot insert 2 records with same userid

for example user_projects table with userid, projectid as pk
you cannot insert 2 records with same userid & projectid

UserID  PrjID
1          101
1          102
2          102
3          105
3          102

these are valid, but you cannot enter (1,102) again since it is already there and there is a PK on these columns
but you can enter (1,103)

guys , i understand all those concepts.
>>>if a column contains duplicate value, it can be index BUT you cannot create a PK on that column

i am specifically talking about table Project_Users where
i have composite primary key (ProjectId +UserId).
CREATE UNIQUE INDEX IDX_UQ_USER_ID ON PROJECT_USERS (USER_ID);  works
but
CREATE UNIQUE INDEX IDX_UQ_PROJECT_ID ON PROJECT_USERS (PROJECT_ID); fails

Error is
18:00:50  [CREATE - 0 row(s), 0.000 secs]  [Error Code: 1505, SQL State: S0001]  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'etrading.PROJECT_USERS' and the index name 'IDX_UQ_PROJECT_ID'. The duplicate key value is (227565).
which is why i said columns with duplicate values cannot be index ed( the above error says that,right?)

- yes it fail because you are specifying UNIQUE index where there are duplicate data in that column. take out the unique:

CREATE INDEX IDX_UQ_PROJECT_ID ON PROJECT_USERS (PROJECT_ID);

aaah.. Makes sense.
Thx