SQL Server Performance Forum – Threads Archive
Is adding a WHERE statement always helpful
Hi Have been reading in some blogs that having a WHERE clause in the SELECT queries is helpful. I just wanted to know is this true?For e.g I have a table With following columns
STOREID char(5)
DEPTID int
With both the above acting as a COMPOSITE Key. Most if not all of my selects and Joins with the table above result in only one of the 2 columns being used , as a result giving me Clustered Index Scan.
Let me presume here DEPTID is used as a part of the JOIN With another table.
If i simply add a WHERE condition to above, say
WHERE STOREID >’00000′
It does result in giving me a clustered index seek. But does this really help ,as my Query Plan shows me the same results with Clustered Index Scan and also with Clustered Index Seek.
I know this is not quite a 2005 issue, but wonder is anyone can help.
Hi, Not sure where you read this article. But sure using where clause require server to filter records and it is unnecessary to filter when it is not required (unless you have million of records).
________________________________________________
~* Opinions are like a$$holes, everyone got one. *~
Yup i am off the same opinion , but was just a bit curious. Here is one website which mentions this as its very first point!
http://www.harriyott.com/2006/01/sql-server-performance-tips.aspx Thanks
quote:Originally posted by MANUFERN
But does this really help ,as my Query Plan shows me the same results with Clustered Index Scan and also with Clustered Index Seek.
When you have to get ALL the rows back, a scan is better than a seek.
Roji. P. ThomasBut does this really help ,as my Query Plan shows me the same results with Clustered Index Scan and also with Clustered Index Seek.
Microsoft SQL Server MVP
http://toponewithties.blogspot.com
The seek you got is in reality the same operation as clustered index scan.
Taking your question literally, I guess the answer should be: No. A WHERE clause isn’t always helpful. Especially when you need to get all rows from a table. However, those case are fairly rare in real-world scenarios, methinks. When I query a table with 80 million row in it for a single fiscal year, I surely won’t transfer all those rows back to the client and evaluate there. I surely would use the appropriate WHERE clause in my statement to narrow down the resultset as much as possible. For further information this might be helpful:http://www.sql-server-performance.com/transact_sql_where.asp —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Thanks Frank and MMarovic, I agree to both ur opinions on the above.
]]>