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 |
|
redbrad0
Posting Yak Master
176 Posts |
 Posted - 2005-05-15 : 22:19:34
|
| I have done a ton of reading this weekend on different books of SQL Server. So here goes my next question....When selecting records using where is there a best order to use in the where clause? I was thinking (which I could be wrong) if your select statement basically looked like the statement below where I changed the table names with field types...Select ID From Table WHERE smallint=3 and nvarchar='this text'Basically my thought process is this... if the first where clause goes by the smallest field type (smallint) then sql server can limit the records it has to do a table scan from. So the query that would be less optimized would be...Select ID From Table WHERE nvarchar='this text' and smallint=3Am I correct? Any other insight?Quality NT Web Hosting & Design |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-15 : 22:30:03
|
| Nope - the optimiser will do what it thinks best (unless you use forceplan). The order of statements doesn't matter.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
cmason
Starting Member
2 Posts |
Posted - 2005-06-02 : 21:58:41
|
| What if there are joins involved? Should the "where table1.idField = table2.idField" come before, or after statements such as "table.EmplNum = 99"? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-06-02 : 22:06:51
|
| It's better to do your joins as ANSI joins.I.E.INNER JOIN table2 ON table2.idfield = table1.idfieldSupport for the old syntax is going to go away.DamianIta erat quando hic adveni. |
|
|
|
cmason
Starting Member
2 Posts |
Posted - 2005-06-02 : 22:19:20
|
| I understand but this is existing code and they like it the old way. However, since their code is using more "or" statements it may be a good time to help them make the switch.Thank you |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-06-03 : 11:53:46
|
| >> What if there are joins involved? Should the "where table1.idField = table2.idField" come before, or after statements such as "table.EmplNum = 99"?Doesn't mattter.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Best Practice for select where xxxxx