none
where clause.

    Вопрос

  • Dear all

    1) select * from test where continetid =1 and countryid=2 and state=3 and districtid=4 and pincode=5

    2) select * from test where pincode =5 and districtid=4 and  state=3 and countryid=2 and continetid =1

    I have unique id for all pincode ,

    So i know that there is no need of  other filters except pincode.

    but supose i have to use all of them.

    then which query i should preffer.

    yours sincerelly.

Ответы

  • Hello,

    The order of the columns for filter doesn't matter, both queries will result in the same execution plan.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Предложено в качестве ответа Shailesh Thapliyal 8 июня 2012 г. 8:03
    • Помечено в качестве ответа rajemessage 14 июня 2012 г. 13:31
  • Just like you said if the pincode is unique then why you need to put all filters? this is what I can't understand.

    but I will certainly put pincode as the first filter. so 2 is my option, given that you don't have a clustered index on other filter names

    • Помечено в качестве ответа rajemessage 14 июня 2012 г. 13:31

Все ответы

  • Hello,

    The order of the columns for filter doesn't matter, both queries will result in the same execution plan.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Предложено в качестве ответа Shailesh Thapliyal 8 июня 2012 г. 8:03
    • Помечено в качестве ответа rajemessage 14 июня 2012 г. 13:31
  • Just like you said if the pincode is unique then why you need to put all filters? this is what I can't understand.

    but I will certainly put pincode as the first filter. so 2 is my option, given that you don't have a clustered index on other filter names

    • Помечено в качестве ответа rajemessage 14 июня 2012 г. 13:31
  • Hello,

    In this example the order of the filters really doesn't matter. You can check it by getting and execution plan of the query, and you will see the same results. If you ask me I would prefer second one just because it seems better to read( since the unique column goes first), but it really won't make a difference in performance.

  • If you have pincode as unique the why don't you use only pincode?

    select *
    from test
    where pincode=5

    If you want to use all of them, you can do something like this. In this case you don't have always to pass the other parameters except pincode:

    CREATE PROCEDURE MySelect
    @pincode int,
    @districtid int=NULL,
    @state int=NULL,
    @countryid int=NULL,
    @continetid int=NULL
    AS
    BEGIN
    	select *
    	from test
    	where pincode=@pincode
    	and (districtid=@districtid or @districtid is null)
    	and ([state]=@state or @state is null)
    	and (countryid=@countryid or @countryid is null)
    	and (continetid =@continetid or @continetid is null)
    END
    GO


    • Изменено irusul 8 июня 2012 г. 6:42
  • filter on zipcode is just enough.

    SQL Champ
    Database Consultants NY

  • I have unique id for all pincode ,

    If by unique id you mean a clustered primary key or unqiue clustered index, then the other columns will not matter for performance but may further limit the result to zero rows if the predicates are false.  I say clustered because you are selecting all columns and the most optimal way to select all columns by a unique key is via the clustered index (unless all other columns are also included in a non-clustered index).


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/