Evaluation of conditions

I have a Join condition like
Select columns
From Table1 T1
Inner JOIN Table2 T2
ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and T1.Col2
In table1, I have two cols that store some string range values. Col1 can
have inidividual entries and col1 and col2 may have range values.
I want check if T2.Col1 is contained in the table1 or not.
I want to make sure that this query is optimized.
If first condition is matched, will the second condition be evaluated?
Suggestions are welcome.
Thanks in advance.
--
Regards,
Lalit BhatiaLalit
If post your table's structure+ an expected result, so we will be able to
not guessing but give you more accurate suggestion
Have you looked at execution plan of the query? Do you really need such
"complicated" query?
"Lalit Bhatia" <xyz@.abc.com> wrote in message
news:OrZpdLFDGHA.3140@.TK2MSFTNGP14.phx.gbl...
>I have a Join condition like
> Select columns
> From Table1 T1
> Inner JOIN Table2 T2
> ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and T1.Col2
> In table1, I have two cols that store some string range values. Col1 can
> have inidividual entries and col1 and col2 may have range values.
> I want check if T2.Col1 is contained in the table1 or not.
> I want to make sure that this query is optimized.
> If first condition is matched, will the second condition be evaluated?
> Suggestions are welcome.
> Thanks in advance.
> --
> Regards,
> Lalit Bhatia
>|||I have changed the name of tables and cols only. columns contain Phone
Numbers.
Structure is same as I given in the query. I want search phone number in the
range and individual numbers.
I have a composite index on T1.Col1 and T1.Col2.
and Table1 has huge data 1000000 records and table2 is also has good amount
of data in production.
In fact at development end we usually do not have that much amount of data.
execution plan shows that it is using 49% from Table2 and 51% from Table1
using index
because everything that I want is present in the Index itself.
I would like to know, Is there any better way or this approach is ok or
good.
Regards,
Lalit Bhatia
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eHxTJOFDGHA.3876@.tk2msftngp13.phx.gbl...
> Lalit
> If post your table's structure+ an expected result, so we will be able to
> not guessing but give you more accurate suggestion
> Have you looked at execution plan of the query? Do you really need such
> "complicated" query?
>
>
> "Lalit Bhatia" <xyz@.abc.com> wrote in message
> news:OrZpdLFDGHA.3140@.TK2MSFTNGP14.phx.gbl...
>|||Lalit
Can you post DDL+ sample code?
What is your query?
CREATE TABLE #Test
(
col VARCHAR(1000)
)
INSERT INTO #Test VALUES ('04-9880124')
INSERT INTO #Test VALUES ('03-1225055')
INSERT INTO #Test VALUES ('06-5965555')
INSERT INTO #Test VALUES ('02-9880149')
INSERT INTO #Test VALUES ('03-9112124')
INSERT INTO #Test VALUES ('09-8858828')
"Lalit Bhatia" <xyz@.abc.com> wrote in message
news:%23dOzhkFDGHA.2912@.tk2msftngp13.phx.gbl...
>I have changed the name of tables and cols only. columns contain Phone
> Numbers.
> Structure is same as I given in the query. I want search phone number in
> the
> range and individual numbers.
> I have a composite index on T1.Col1 and T1.Col2.
> and Table1 has huge data 1000000 records and table2 is also has good
> amount
> of data in production.
> In fact at development end we usually do not have that much amount of
> data.
> execution plan shows that it is using 49% from Table2 and 51% from Table1
> using index
> because everything that I want is present in the Index itself.
> I would like to know, Is there any better way or this approach is ok or
> good.
> --
> Regards,
> Lalit Bhatia
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eHxTJOFDGHA.3876@.tk2msftngp13.phx.gbl...
>|||Create Table PhoneList
(
Code varchar(5),
PhoneNumber varchar(25),
PhoneNumberTo varchar(25)
PRIMARY KEY Nonclustered (Code, PhoneNumber)
)
Create Table PhoneReceip
(
IDPhReceip INT,
InPhoneNumber varchar(25)
)
INSERT PhoneList values ('BS01', '6005231234', NULL)
INSERT PhoneList values ('BS01', '6005231235', NULL)
INSERT PhoneList values ('BS01', '6005241000', NULL)
INSERT PhoneList values ('BS02', '6005241001', '6005241999')
INSERT PhoneList values ('BS02', '6005251555', NULL)
INSERT PhoneList values ('BS02', '6005255555', NULL)
INSERT PhoneList values ('BS02', '6005264231', '6005264239')
INSERT PhoneReceip values (10100, '6005231234')
INSERT PhoneReceip values (10100, '6005231555')
INSERT PhoneReceip values (10101, '6005241051')
INSERT PhoneReceip values (10101, '6005251555')
When I query for 10100 then '6005231234' should be returned and when I query
10101 both records should return because '6005241051' is in the range
between '6005241001' and '6005241999' and '6005231555' is there as
individual entry.
Select InPhoneNumber
From PhoneList T1
Inner JOIN PhoneReceip T2
ON PhoneList.PhoneNumber = PhoneReceip.InPhoneNumber Or T2.InPhoneNumber
Between T1.PhoneNumber and T1.PhoneNumberTo
WHERE IDPhReceip = 10100 and Code = 'BS01'
Phone Number is always 10 digit string.
There is no DLL for this. just this query that is run inside the SP.
Regards,
Lalit Bhatia
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e3WsI1FDGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Lalit
> Can you post DDL+ sample code?
> What is your query?
>
> CREATE TABLE #Test
> (
> col VARCHAR(1000)
> )
> INSERT INTO #Test VALUES ('04-9880124')
> INSERT INTO #Test VALUES ('03-1225055')
> INSERT INTO #Test VALUES ('06-5965555')
> INSERT INTO #Test VALUES ('02-9880149')
> INSERT INTO #Test VALUES ('03-9112124')
> INSERT INTO #Test VALUES ('09-8858828')
>
>
>
> "Lalit Bhatia" <xyz@.abc.com> wrote in message
> news:%23dOzhkFDGHA.2912@.tk2msftngp13.phx.gbl...
Table1
to
evaluated?
>|||select PhoneReceip.IDPhReceip
, PhoneReceip.InPhoneNumber
from PhoneReceip
join PhoneList
on PhoneReceip.InPhoneNumber
between PhoneList.PhoneNumber
and COALESCE(PhoneList.PhoneNumberTo,PhoneList.PhoneNumber)
*** Sent via Developersdex http://www.examnotes.net ***|||Lalit
Thanks for DDL
SELECT *
FROM PhoneReceip pp
JOINPhoneList pl
ON pp.InPhoneNumber
BETWEEN pl.PhoneNumber
AND COALESCE(pl.PhoneNumberTo,pl.PhoneNumber)
WHERE IDPhReceip=10101
"Lalit Bhatia" <xyz@.abc.com> wrote in message
news:eeyDGQGDGHA.272@.TK2MSFTNGP10.phx.gbl...
> Create Table PhoneList
> (
> Code varchar(5),
> PhoneNumber varchar(25),
> PhoneNumberTo varchar(25)
> PRIMARY KEY Nonclustered (Code, PhoneNumber)
> )
> Create Table PhoneReceip
> (
> IDPhReceip INT,
> InPhoneNumber varchar(25)
> )
> INSERT PhoneList values ('BS01', '6005231234', NULL)
> INSERT PhoneList values ('BS01', '6005231235', NULL)
> INSERT PhoneList values ('BS01', '6005241000', NULL)
> INSERT PhoneList values ('BS02', '6005241001', '6005241999')
> INSERT PhoneList values ('BS02', '6005251555', NULL)
> INSERT PhoneList values ('BS02', '6005255555', NULL)
> INSERT PhoneList values ('BS02', '6005264231', '6005264239')
> INSERT PhoneReceip values (10100, '6005231234')
> INSERT PhoneReceip values (10100, '6005231555')
> INSERT PhoneReceip values (10101, '6005241051')
> INSERT PhoneReceip values (10101, '6005251555')
> When I query for 10100 then '6005231234' should be returned and when I
> query
> 10101 both records should return because '6005241051' is in the range
> between '6005241001' and '6005241999' and '6005231555' is there as
> individual entry.
> Select InPhoneNumber
> From PhoneList T1
> Inner JOIN PhoneReceip T2
> ON PhoneList.PhoneNumber = PhoneReceip.InPhoneNumber Or T2.InPhoneNumber
> Between T1.PhoneNumber and T1.PhoneNumberTo
> WHERE IDPhReceip = 10100 and Code = 'BS01'
> Phone Number is always 10 digit string.
> There is no DLL for this. just this query that is run inside the SP.
> --
> Regards,
> Lalit Bhatia
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e3WsI1FDGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Table1
> to
> evaluated?
>|||Thanks.
hope this will solve my problem.
Regards,
Lalit Bhatia|||We can also use IsNULL instead of COALESCE here.
could you explain which one will be faster?
Regards,
Lalit Bhatia
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23NMmMwGDGHA.2292@.tk2msftngp13.phx.gbl...
> Lalit
> Thanks for DDL
> SELECT *
> FROM PhoneReceip pp
> JOINPhoneList pl
> ON pp.InPhoneNumber
> BETWEEN pl.PhoneNumber
> AND COALESCE(pl.PhoneNumberTo,pl.PhoneNumber)
> WHERE IDPhReceip=10101
>
> "Lalit Bhatia" <xyz@.abc.com> wrote in message
> news:eeyDGQGDGHA.272@.TK2MSFTNGP10.phx.gbl...
or
able
Col1
>|||http://sqljunkies.com/WebLog/amacha...11/30/5311.aspx
"Lalit Bhatia" <xyz@.abc.com> wrote in message
news:O1KHIBHDGHA.2664@.TK2MSFTNGP15.phx.gbl...
> We can also use IsNULL instead of COALESCE here.
> could you explain which one will be faster?
> --
> Regards,
> Lalit Bhatia
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23NMmMwGDGHA.2292@.tk2msftngp13.phx.gbl...
> or
> able
> Col1
>