How Can I remove Table Scan?

I have the following query and just cannot seem to remove the table scan on
Table2. I have posted the indices from all tables in the join. I think Ive
covered everything according to rule. but is it overkill?
select count(a.Vh_SummVeh_id)
from Table1 a (nolock),
Table2 b (nolock),
Table3 c (nolock),
Table4 d (nolock)
where a.VH_Group_ID = b.Vh_group_id
and a.Vh_Rating_Class_ID = b.Vh_Rating_Class_ID
and c.vh_value_id = b.vh_value_id
and c.Vh_band_low_id = a.Vh_band_low_id
and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
and isnull(d.Vh_SummVeh_id,0) <> 0 -- IS NULL
sp_help Table1
nonclustered located on PRIMARY VH_SummVeh_id, VH_Group_ID,
Vh_Rating_Class_ID, Vh_band_low_id
sp_help Table2
nonclustered, unique located on PRIMARY Vehicle_id
nonclustered located on PRIMARY Vh_reg_area_id
nonclustered located on PRIMARY Vh_VhAll_group_id
nonclustered located on PRIMARY Vh_Motor_group_id
nonclustered located on PRIMARY Vh_mfg_yr_id
nonclustered located on PRIMARY Vh_value_cv_id
nonclustered located on PRIMARY Vh_category_id
nonclustered located on PRIMARY Vh_mfg_age_id
nonclustered located on PRIMARY Vh_group_id
nonclustered located on PRIMARY Vh_rating_class_id
nonclustered located on PRIMARY Vh_model_id
nonclustered located on PRIMARY Vh_make_id
nonclustered located on PRIMARY Vh_value_id
nonclustered located on PRIMARY Vh_reg_year_id
sp_help Table3
nonclustered, unique located on PRIMARY Vh_value_id
clustered, unique located on PRIMARY Vh_value_lower, Vh_value_upper,
Vh_value_id
nonclustered located on PRIMARY Vh_value_band_id, Vh_band_low_id
sp_help Table4
nonclustered located on PRIMARY Po_risk_detail_id, Effective_date_id,
Tr_seq_id
nonclustered located on PRIMARY Product_id
nonclustered located on PRIMARY Inception_date_id
nonclustered located on PRIMARY Client_ver_id
nonclustered located on PRIMARY Tr_sub_type_id
nonclustered located on PRIMARY Rated_driver_id
nonclustered located on PRIMARY f_Ren_Flag
nonclustered located on PRIMARY Cp_cover_class_ind_id
nonclustered located on PRIMARY cp_attribute_id
nonclustered located on PRIMARY Cp_gen_ind_id
nonclustered located on PRIMARY Policy_id
nonclustered located on PRIMARY dim_keys_id
nonclustered located on PRIMARY Po_tr_pay_method_id
nonclustered located on PRIMARY Vh_SummVeh_id
nonclustered located on PRIMARY Po_tr_status_id
nonclustered located on PRIMARY Po_tr_bus_type_id
nonclustered located on PRIMARY Po_rd_postcode_id
nonclustered located on PRIMARY Cover_id
nonclustered located on PRIMARY Cur_trn_dt
nonclustered located on PRIMARY Vehicle_id
nonclustered located on PRIMARY Cover_idWhat exactly is this test supposed to achieve:
and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
?
This one also makes no sense to me:
and isnull(d.Vh_SummVeh_id,0) <> 0
Try replacing these two with valid tests first.
E.g.:
and (b.vehicle_id = d.vehicle_id or b.vehicle_id is null) -- just guessing
here
and (d.Vh_SummVeh_id is null)
ML|||try
Updating statistics
--
Regards
R.D
--Knowledge gets doubled when shared
"marcmc" wrote:

> I have the following query and just cannot seem to remove the table scan o
n
> Table2. I have posted the indices from all tables in the join. I think Ive
> covered everything according to rule. but is it overkill?
> select count(a.Vh_SummVeh_id)
> from Table1 a (nolock),
> Table2 b (nolock),
> Table3 c (nolock),
> Table4 d (nolock)
> where a.VH_Group_ID = b.Vh_group_id
> and a.Vh_Rating_Class_ID = b.Vh_Rating_Class_ID
> and c.vh_value_id = b.vh_value_id
> and c.Vh_band_low_id = a.Vh_band_low_id
> and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
> and isnull(d.Vh_SummVeh_id,0) <> 0 -- IS NULL
> sp_help Table1
> nonclustered located on PRIMARY VH_SummVeh_id, VH_Group_ID,
> Vh_Rating_Class_ID, Vh_band_low_id
> sp_help Table2
> nonclustered, unique located on PRIMARY Vehicle_id
> nonclustered located on PRIMARY Vh_reg_area_id
> nonclustered located on PRIMARY Vh_VhAll_group_id
> nonclustered located on PRIMARY Vh_Motor_group_id
> nonclustered located on PRIMARY Vh_mfg_yr_id
> nonclustered located on PRIMARY Vh_value_cv_id
> nonclustered located on PRIMARY Vh_category_id
> nonclustered located on PRIMARY Vh_mfg_age_id
> nonclustered located on PRIMARY Vh_group_id
> nonclustered located on PRIMARY Vh_rating_class_id
> nonclustered located on PRIMARY Vh_model_id
> nonclustered located on PRIMARY Vh_make_id
> nonclustered located on PRIMARY Vh_value_id
> nonclustered located on PRIMARY Vh_reg_year_id
> sp_help Table3
> nonclustered, unique located on PRIMARY Vh_value_id
> clustered, unique located on PRIMARY Vh_value_lower, Vh_value_upper
,
> Vh_value_id
> nonclustered located on PRIMARY Vh_value_band_id, Vh_band_low_
id
> sp_help Table4
> nonclustered located on PRIMARY Po_risk_detail_id, Effective_date_id,
> Tr_seq_id
> nonclustered located on PRIMARY Product_id
> nonclustered located on PRIMARY Inception_date_id
> nonclustered located on PRIMARY Client_ver_id
> nonclustered located on PRIMARY Tr_sub_type_id
> nonclustered located on PRIMARY Rated_driver_id
> nonclustered located on PRIMARY f_Ren_Flag
> nonclustered located on PRIMARY Cp_cover_class_ind_id
> nonclustered located on PRIMARY cp_attribute_id
> nonclustered located on PRIMARY Cp_gen_ind_id
> nonclustered located on PRIMARY Policy_id
> nonclustered located on PRIMARY dim_keys_id
> nonclustered located on PRIMARY Po_tr_pay_method_id
> nonclustered located on PRIMARY Vh_SummVeh_id
> nonclustered located on PRIMARY Po_tr_status_id
> nonclustered located on PRIMARY Po_tr_bus_type_id
> nonclustered located on PRIMARY Po_rd_postcode_id
> nonclustered located on PRIMARY Cover_id
> nonclustered located on PRIMARY Cur_trn_dt
> nonclustered located on PRIMARY Vehicle_id
> nonclustered located on PRIMARY Cover_id
>|||ps: i would prefer not to use index hint
also: http://techrepublic.com.com/5100-22_11-1050429.html
which explains the problem with counting nulls|||Have you tried any of my suggestions? How did they affect the execution plan
?
ML|||Thanks but statistics are updated,
the coalesce reduces the amount of disk IO by 5%.
The isNull gets away from a SQL "feature" [IS NOT NULL] that ensures the
number of IS NOT NULLs counted is correct(see thread 'Are Null counts
reliable' from yesterday).
So any other ideas on why when the selected column to update from is indexed
as well as the columns in the join, a table scan still exists? And how can I
overcome this?|||Hey ML, I have.
"and (d.Vh_SummVeh_id is null)" I cannot used because it does not give me
the correct number of ISNULL records.
The other uses a table scan also.|||there are 4million records in Table2, is it possible the optimizer thinks th
e
tablescan is the best way to run the query?
"ML" wrote:

> Have you tried any of my suggestions? How did they affect the execution pl
an?
>
> ML|||I have evaluated the performance on using a hint which drops the table scan
and creates a 95% bookmark. performance did not increase.
I think the optimizer may be right to do its table scan.
Thanks all round|||There's also no clustered index on Table4. Try making the one on the
vehicle_id column in Table4 a clustered index.
MLsql

How can i pass GUID as sqlreport input paramater?

Hi,
How can I pass GUID as sqlreport Input parameter? I get the following error -

"Failed to convert parameter value from a String to a Guid. (System.Data)"

I also tried passing guid within the curly braces but of now use. Please help!
Ashu

Try creating a ReportParameter object and then storing the GUID, as a string, inside this parameter.

Example:

Microsoft.Reporting.Winforms.ReportParameter tempParameter = new Microsoft.Reporting.Winforms.ReportParameter();

tempParameter = new Microsoft.Reporting.Winforms.ReportParameter("ID", GUID.ToString());

Finally, pass this ReportParameter object to the report.

Example:

reportViewer.LocalReport.SetParameters(tempParameter);

how can I parse

hi
Can some one help my, how can I parse the following XML.
<WhoisRecord>
<CreatedDate>1996-03-27T00:00:00Z</CreatedDate>
<UpdatedDate>2004-06-21T00:00:00Z</UpdatedDate>
<ExpiresDate>2014-03-28T00:00:00Z</ExpiresDate>
<Registrant>
<Name>Microsoft Corporation</Name>
<Address>One Microsoft Way</Address>
<City>Redmond</City>
<StateProvince>WA</StateProvince>
<PostalCode>98052</PostalCode>
<Country>US</Country>
<CountryCode>US</CountryCode>
</Registrant>
<Domain>
<Name>HOTMAIL.COM</Name>
<UpdatedDate>21-Jun-2004</UpdatedDate>
<ExpiresDate>28-Mar-2014</ExpiresDate>
<CreatedDate>27-Mar-1996</CreatedDate>
<NameServer>NS1.HOTMAIL.COM</NameServer>
<NameServer>NS3.HOTMAIL.COM</NameServer>
<NameServer>NS2.HOTMAIL.COM</NameServer>
<NameServer>NS4.HOTMAIL.COM</NameServer>
<NameServerAddress>216.200.206.140</NameServerAddress>
<NameServerAddress>209.185.130.68</NameServerAddress>
<NameServerAddress>216.200.206.139</NameServerAddress>
<NameServerAddress>64.4.29.24</NameServerAddress>
</Domain>
</WhoisRecord>
Look up OPENXML in Books Online.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Farhan" <Farhan@.discussions.microsoft.com> wrote in message
news:ECCC3FA5-AC99-49D3-8E61-7D893BCAF7D1@.microsoft.com...
> hi
> Can some one help my, how can I parse the following XML.
> <WhoisRecord>
> <CreatedDate>1996-03-27T00:00:00Z</CreatedDate>
> <UpdatedDate>2004-06-21T00:00:00Z</UpdatedDate>
> <ExpiresDate>2014-03-28T00:00:00Z</ExpiresDate>
> <Registrant>
> <Name>Microsoft Corporation</Name>
> <Address>One Microsoft Way</Address>
> <City>Redmond</City>
> <StateProvince>WA</StateProvince>
> <PostalCode>98052</PostalCode>
> <Country>US</Country>
> <CountryCode>US</CountryCode>
> </Registrant>
> <Domain>
> <Name>HOTMAIL.COM</Name>
> <UpdatedDate>21-Jun-2004</UpdatedDate>
> <ExpiresDate>28-Mar-2014</ExpiresDate>
> <CreatedDate>27-Mar-1996</CreatedDate>
> <NameServer>NS1.HOTMAIL.COM</NameServer>
> <NameServer>NS3.HOTMAIL.COM</NameServer>
> <NameServer>NS2.HOTMAIL.COM</NameServer>
> <NameServer>NS4.HOTMAIL.COM</NameServer>
> <NameServerAddress>216.200.206.140</NameServerAddress>
> <NameServerAddress>209.185.130.68</NameServerAddress>
> <NameServerAddress>216.200.206.139</NameServerAddress>
> <NameServerAddress>64.4.29.24</NameServerAddress>
> </Domain>
> </WhoisRecord>

How can I launch a report without generating it automatically?

I am accessing a report in IE by typing the following link in the address field:

http://localhost/ReportServer/Pages/ReportViewer.aspx?/Reports/TheNameOfMyReport

When the page is loaded, the report is generated automatically because all report parameters have default values.

How can I modify my report so that it is not automatically generated? I would like to wait until the user clicks on "View Report" before rendering the report.

As a temporary solution, I removed the default value of one report parameter but I would like to get it to work even if I leave all default values in.

I have seen sites where they describe a list of extra parameters you can include in the url to modify the behavior of the report like:

&rs:Command=Render

&rs:Format=Excel

&rc:NoHeader=true ...

but I haven't seen anything to tell the reporting engine not to render a report.

Any help would be appreciated, thx.

Is this not possible. I am also looking forward to a way to do the same?|||This appears to be only possible with your workaround above or by using the Reportviewer control in Visual Studio.

how can I insert !

I have a following table :
CREATE TABLE [dbo].[defaultvalue] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[flag] AS ([id] + 1)
) ON [PRIMARY]
GO
I want to insert the data in it, how can I insert ?
Thanks
NOOR
Hi,
Use the DEFAULT VALUES along with insert statement.
insert into dbo.defaultvalue DEFAULT VALUES
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> I have a following table :
> CREATE TABLE [dbo].[defaultvalue] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [flag] AS ([id] + 1)
> ) ON [PRIMARY]
> GO
> I want to insert the data in it, how can I insert ?
> Thanks
> NOOR
>
|||Hi,
Use the DEFAULT VALUES along with insert statement.
insert into dbo.defaultvalue DEFAULT VALUES
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> I have a following table :
> CREATE TABLE [dbo].[defaultvalue] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [flag] AS ([id] + 1)
> ) ON [PRIMARY]
> GO
> I want to insert the data in it, how can I insert ?
> Thanks
> NOOR
>
|||Thanks Hari
Thanks
Noor
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23zRneCcXEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the DEFAULT VALUES along with insert statement.
> insert into dbo.defaultvalue DEFAULT VALUES
> --
> Thanks
> Hari
> MCDBA
> "Noor" <noor@.ngsol.com> wrote in message
> news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
>
|||Thanks Hari
Thanks
Noor
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23zRneCcXEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the DEFAULT VALUES along with insert statement.
> insert into dbo.defaultvalue DEFAULT VALUES
> --
> Thanks
> Hari
> MCDBA
> "Noor" <noor@.ngsol.com> wrote in message
> news:OO38tPZXEHA.1888@.TK2MSFTNGP11.phx.gbl...
>

How can i get the process detais using TSQL instructions ?

Hi Friends,
How can i get the process detais using TSQL instructions ?
i want the same information that i get when i execute the
following procedures
"Expand Management, and then expand Current Activity.
Click Process Info.
The current server activity is displayed in the details
pane."
Thank You
JohnnyHi
You can use profiler to see what EM is doing and it would have come up with
he undocumented procedure:
EXEC sp_MSget_current_activity 51,1
John
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>|||Hi,
To get information of a SPID then go with this:-
From Query Analyzer, Execute the below system procedure to get the SPID for
the user.
sp_who
After getting the sp_id use the below command to get the process detailsfor
the SPID
DBCC INPUTBUFFER(SPID)
Thanks
Hari
MCDBA
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>|||select *
from master..sysprocesses
-Sue
On Fri, 18 Jun 2004 12:50:37 -0700, "Johnny Silvestre"
<johnny_silvestre@.yahoo.de> wrote:
>Hi Friends,
>How can i get the process detais using TSQL instructions ?
>i want the same information that i get when i execute the
>following procedures
>"Expand Management, and then expand Current Activity.
>Click Process Info.
>The current server activity is displayed in the details
>pane."
>Thank You
>Johnny

How can i get a return code of 1 for an osql command which has a lower severity..

I am running the following OSQL command and capturing the return code
for the error .Whenver i have an error like server not exists or uable
to login I get a return code of 1 for the %ERRORLEVEL%.However
whenever I have an errorof a wrong dbcompatibility error the retun
code id 1 even though sql returns an iformation message from OSQL that
the right copatibilty levels are 60,70 and 80.How can i get OSQL to
return the right return code whenver a error of this type occurrs from
batch mode sql.The OSQL i am running from the batch is

osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
-Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
ECHO %errorlevel% >> %logbatch%
IF %ERRORLEVEL% NEQ 0 Goto SQLError

sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80

Thanks in anticipation.

Ajay[posted and mailed, please reply in news]

Ajay Garg (ajayz90@.hotmail.com) writes:
> I am running the following OSQL command and capturing the return code
> for the error .Whenver i have an error like server not exists or uable
> to login I get a return code of 1 for the %ERRORLEVEL%.However
> whenever I have an errorof a wrong dbcompatibility error the retun
> code id 1 even though sql returns an iformation message from OSQL that
> the right copatibilty levels are 60,70 and 80.How can i get OSQL to
> return the right return code whenver a error of this type occurrs from
> batch mode sql.The OSQL i am running from the batch is
> osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
> -Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
> ECHO %errorlevel% >> %logbatch%
> IF %ERRORLEVEL% NEQ 0 Goto SQLError
> sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80

Here is a simple example that illustrates:

E:\temp>osql -E -n -Q "EXIT (SELECT 47)"

----
47

(1 row affected)

E:\temp>echo %ERRORLEVEL%
47

E:\temp
By putting the entire SQL batch within EXIT(), OSQL will return the
value of the last result set to the command-line environment.

For details, see the topic on OSQL in Books Online.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402EF532A497Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Ajay Garg (ajayz90@.hotmail.com) writes:
> > I am running the following OSQL command and capturing the return code
> > for the error .Whenver i have an error like server not exists or uable
> > to login I get a return code of 1 for the %ERRORLEVEL%.However
> > whenever I have an errorof a wrong dbcompatibility error the retun
> > code id 1 even though sql returns an iformation message from OSQL that
> > the right copatibilty levels are 60,70 and 80.How can i get OSQL to
> > return the right return code whenver a error of this type occurrs from
> > batch mode sql.The OSQL i am running from the batch is
> > osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
> > -Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
> > ECHO %errorlevel% >> %logbatch%
> > IF %ERRORLEVEL% NEQ 0 Goto SQLError
> > sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80
> Here is a simple example that illustrates:
> E:\temp>osql -E -n -Q "EXIT (SELECT 47)"
> ----
> 47
> (1 row affected)
> E:\temp>echo %ERRORLEVEL%
> 47
> E:\temp>
> By putting the entire SQL batch within EXIT(), OSQL will return the
> value of the last result set to the command-line environment.
> For details, see the topic on OSQL in Books Online.

I noticed someting even more interesting.Even though the return code
was 0
when there was an error the job on sql server which actually failed
showed that it ran sucessfully.Even though when i run the job as an
Xp_cmdshell command on the sql server it shows that it failed what
could be the reason that it behaves that way?

Thanks in anticipation.

Ajay|||Ajay Garg (ajayz90@.hotmail.com) writes:
> I noticed someting even more interesting.Even though the return code
> was 0
> when there was an error the job on sql server which actually failed
> showed that it ran sucessfully.Even though when i run the job as an
> Xp_cmdshell command on the sql server it shows that it failed what
> could be the reason that it behaves that way?

I'm sorry, but I don't follow. Could you clarify with an example?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How can I force the query to return values?

I want the following query to return a row even when table 'X' is empty. How would I do this?

SELECT TOP 1 @.Var1, @.Var2, @.Var3 from X

The parameters @.Var1, @.Var2 and @.Var3 are passed to the stored procedure in which the above query is included.

When table is empty, it reurn nothing. It only return a row when table is not empty.

The following should work:

SELECT TOP 1 Coalesce(@.Var1, "-1"), Coalesce(@.Var2, "-1"), Coalesce(@.Var3, "-1") from X

Coalesce will evaluate if the value of the field is null, if so it will return whatever value you specify in the second half of the Coalesce statement (I put "-1" in there as an example.)

-Aaron

|||

It doesn't work. I think you assumed that @.Var1 is a column. It's a sp parameter.

I ran your query for an empty table and it returned no rows.

|||

Ahhh yes, I did assume it was a column.

Could you post your full query code so I can get a better understanding of exactly what you are doing?

|||

You could do something like this:

IFEXISTS (SELECT *FROM ..WHERE ..)BEGINSELECT..FROM ...WHERE ...ENDELSEBEGINSELECT..FROM..WHERE 1=0END

|||

The situation is pretty straight forward and in one minute you will be be ready-to-go:Create a table X with no rows. Then run the following query on it:

select top 1 @.var1, @.var2, @.var3 from X

You will notice that till table X has rows, the above query will not return anything.

The challenge here is to return a row even when table X is empty.

|||

If what I posted doesnt work you can use SELECT NULL, NULL in the NOT EXISTS part of the IF condition.

|||

This may sound stupid... but working

IF(SELECTCOUNT(*)FROM EMPTY_TABLE)=0

BEGIN

Select'1'as Col1,'2'as Col2,'3'as Col3

END

ELSE

BEGIN

SelectTOP 1'1'as Col1,'2'as Col2,'3'as Col3from EMPTY_TABLE

END

? if this is what you want replace the constants with parameter

|||

Not sure why you want this (would be interested to know) but here you go:

select top 1 t1.var1, t1.var2, t1.var3from (select @.Var1, @.Var2, @.Var3)as t1 ( var1, var2, var3)leftouter join Xon 1=1-- example heredeclare @.xintset @.x = 1select top 1 t1.x, o.*from (select @.x) t1 (x)leftouter join (select *from sys.objectswhere 1=0) oon 1=1

Use an inventive LEFT OUTER JOIN:|||

As a side note, anyone needing to check whether rows exist in a table or not such as here

satya_tanwar:

IF(SELECTCOUNT(*)FROM EMPTY_TABLE)=0

Would beMUCH better served usingEXISTS:

IFEXISTS (select 1 from tbl)BEGIN-- code for data that existEND ELSE BEGIN-- code for data that doesn't existEND

The reason is that using COUNT(*) means the table or an index needs to be iterated. Using EXISTS checks for the first record to be returned and then that processing stops with success.|||

Quick addendum to David's suggestion.

It doesnt matter if you use 1 or COUNT(*) or <column1> if you use EXISTS.

IF EXISTS( SELECT COUNT(*) FROM Table WHERE <condition>. EXISTS returns a boolean so as soon as it finds the first record that matches the condition, it will return with a TRUE. The subquery doesnt return any result set when used with an EXISTS.

|||

ndinakar:

It doesnt matter if you use 1 or COUNT(*) or <column1> if you use EXISTS.

Actually...it matters quite a bit. SELECT COUNT(*) FROM tbl will always return a singleton (not including a GROUP BY clause in the query) butSELECT <something> FROM tbl may not. So:

ndinakar:

IF EXISTS( SELECT COUNT(*) FROM Table WHERE {condition}. EXISTS returns a boolean ...

Comparing that IF statement to this one

IFEXISTS (select 1from tblwhere {condition})

There is quite a difference in how these statements behave.

|||

Here's something from BOL: Am I understanding this wrong?

--------------

When a subquery is introduced with the keyword EXISTS, the subquery functions as an existence test.The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

Notice that subqueries that are introduced with EXISTS are a bit different from other subqueries in the following ways:

The keyword EXISTS is not preceded by a column name, constant, or other expression.
The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.
|||

Where the confusion lies is that the querySELECT COUNT(*) FROM TBL does in fact return a record even when no data exists in the table. Here is an example to demonstrate

declare @.ttable (idint)ifexists (selectcount(*)from @.t )print'Data Exists! [using count(*)]'else print'No data! [using count(*)]'ifexists (select 1from @.t )print'Data Exists!'else print'No data!'

If both methods were equivalent, then you would seeNo Data! both times. Since the COUNT(*) query returns a singleton, it in fact causes a TRUE to be returned from the EXISTS() function.

|||

Oh ok. I see your point. And its a good point. Even if there are no rows matching the condition, the query would return a 0 which translates to TRUE.