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.
| Author |
Topic |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2004-10-07 : 01:19:34
|
| Hi,If I have 1 column in my database that holds the following possible integer values (1,2,3). (Field#1)And another column that can hold ANY integer value, which one should I put in the WHERE clause first? (Field#2)example:WHERE Field#1 = @1 AND Field#2 = @2ORWHERE Field#2 = @2 AND Field#1 = @1? |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-07 : 03:19:16
|
| are you saying they both hold only integer values...It does not make any difference..Hemanth GorijalaBI Architect / DBA... |
 |
|
|
Jaap
Starting Member
19 Posts |
Posted - 2004-10-07 : 04:03:06
|
| It does not make any difference.Creating an index on both columns does.First field(2) and than field(1) is normaly faster than field(1), field(2) because the hitcount on field(2) is better. There is one good reason to make the an index on field(1), field(2).This reason is when most of the records has the same value in field(1)For examle field(1) is a status field of an Invoice.Status 1 = QuotationStatus 2 = Pro forma invoiceStatus 3 = InvoiceAnd field(2) = ClientID.Most (lets say 95%) of the records will have status 3 (after some time) but your online mostly working with Status 1 and Status 2. (5%)Then it's good to make the index on field(1), field(2) because you reduce most (95%) of the records (status 3) on field(1)Field(2) is in the subset of Status 1 and 2 neary unique so this give's a good performance.When you are working a lot with Status 3 the index should be field(2), field(1).Jaap |
|
|
|
|
|
|
WHERE Cluase, which is faster?