Which column to put index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Which column to put index

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-06 : 10:48:34

i've these following table and data

tblCamera
TransID | BranchID | X | Y | TransDate
---------------------------------------------
1 | 12 | 90 | 76 | 3/12/2007
2 | 9 | 45 | 79 | 3/12/2007
3 | 12 | 69 | 34 | 3/12/2007
4 | 12 | 78 | 56 | 3/12/2007
5 | 9 | 67 | 35 | 3/12/2007
6 | 12 | 78 | 56 | 3/12/2007
*TransID is a primary key

tblBranch
BranchID | BranchName
--------------------------
9 | Kota Raja
10 | Kota Alam
11 | Kota Lama
12 | Kota Tua
13 | Kota Hantu
*BranchID is a primary key

Which column need to put an index? Can anyone show me the best way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-06 : 11:07:57

Clustered on tblCamera.TransID,tblBransc.BranchID
NonClustered on tblCamera.BranchID

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-06 : 16:00:05

What are the most common queries run on those tables?
How big do you expect them to get?

--
Gail Shaw

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-06 : 21:43:53

Queries will return more than 2 millions of row. This is the most queries run on those tables.

SELECT b.MAIN_ID
FROM
tblCamera AS a INNER JOIN
tblBranch AS b
ON a.BranchID = b.BranchID
ORDER BY a.BranchID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources