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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-06-15 : 08:33:10
|
| vinod writes "Q1>Should I apply filter in sequence(based on primarykey,not null,comparision,between clause) ? -How to apply filter in correct format,and SQL server internally execute it.Q2>Should I use 'is not Null' to be put at the last? Q3>Should I use 'between clause' rather than relation operator i.e(empid>10 and empid<200)Q4>Does filters of sequence has any impact on the query execution process table1[pkey1,col1,col2,col3] -->pkey is pkey1 table2[pkey2,pkey1,col11,col12,col13]--->pkey is pkey2, and pkey1 is foreign key CaseI->select table2.pkey2,table1.col1 ,table2.col12 from table1 inner join table2 on table1.pkey1=table2.pkey1 where (table1.col3>100 and table1.col3<300 ) and table2.pkey1=2020 and table2.col13 is not nullCaseII->select table2.pkey2,table1.col1 ,table2.col12 from table1 inner join table2 on table1.pkey1=table2.pkey1 where (table1.col3 between 101 and 299 ) and pkey1=2020 and table2.col13 is not null" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-06-15 : 08:38:30
|
| 1. You can, but you don't have to. SQL Server's query optimizer will evaluate multiple execution plans and use the best one based on execution cost (least I/O, least memory used, etc.)2. See #1.3. The optimizer converts "BETWEEN a and b" to "Col>=a and Col<=b", so again, see #1.4. See #1. The only way to guarantee that SQL Server processes your query exactly the way you write it is to use optimization hints. You should avoid using hints, UNLESS you determine through careful and repeated testing that the hints actually improve performance. Never assume that they will, TEST AND MAKE SURE. It's safe to assume that if you have questions about hints then you should not be using them.You can find out more about hints in SQL Server Books Online. |
 |
|
|
|
|
|
|
|
Query Excution Process- pros and cons