SQL Server Performance

Is adding a WHERE statement always helpful

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by MANUFERN, Aug 7, 2006.

  1. MANUFERN New Member

    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.
  2. waqar Member

    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. *~
  3. MANUFERN New Member

  4. Roji. P. Thomas New Member

    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. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  5. mmarovic Active Member

    The seek you got is in reality the same operation as clustered index scan.
  6. FrankKalis Moderator

    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
  7. MANUFERN New Member

    Thanks Frank and MMarovic, I agree to both ur opinions on the above.

Share This Page