Determining numbers of rows affected in advance

Hi,
When workinf on a SQL2005 Db through Sql Server Management studio, is
there a way to determine in advance how many rows will be affected as
a result of an Update,Insert,or Delete statement without actually
performing the query?
thanks "in advance"> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and then
rollback. Another method is to determine the number of rows that will be
affected is to change the DML to a SELECT COUNT(*) query.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"|||Aamir
I'm not sure what you mean by 'through SQL Server Management Studio' but I
strongly recommend you don't do any data modification through a graphical
window, but always do it through TSQL code, using a query window or
application. You have much more control, and the ability to use the
techniques Dan suggested.
If you only want an estimate, you could also just look at the estimated
query plan after entering your query in a query window. You can get this
plan with Cntl-L
If you hold your cursor over the estimated plan's first icon, it should
display the estimated number of rows to be returned.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> Hi,
> When workinf on a SQL2005 Db through Sql Server Management studio, is
> there a way to determine in advance how many rows will be affected as
> a result of an Update,Insert,or Delete statement without actually
> performing the query?
> thanks "in advance"|||The SELECT COUNT(*) method can be much more efficient than your actual DML
statement because it will probably (hopefully!!) have a very tight, cheap
query plan using indexes since it just needs a count. And if you then DO
choose to run the actual statement then these pages will already have been
pulled into RAM hopefully making the run of the DML faster. Note that this
is still only recommended if for some reason you really do need the count
prior to the execution.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:34560279-FE9B-4EA1-BBBF-36ABFCB77F22@.microsoft.com...
>> When workinf on a SQL2005 Db through Sql Server Management studio, is
>> there a way to determine in advance how many rows will be affected as
>> a result of an Update,Insert,or Delete statement without actually
>> performing the query?
> No, but you can execute the DML in a transaction, select @.@.ROWCOUNT and
> then rollback. Another method is to determine the number of rows that
> will be affected is to change the DML to a SELECT COUNT(*) query.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>> Hi,
>> When workinf on a SQL2005 Db through Sql Server Management studio, is
>> there a way to determine in advance how many rows will be affected as
>> a result of an Update,Insert,or Delete statement without actually
>> performing the query?
>> thanks "in advance"
>|||But for anything but really trivial quries, that optimizer estimate is
probably so off that it may not be very useful.
Linchi
"Kalen Delaney" wrote:
> Aamir
> I'm not sure what you mean by 'through SQL Server Management Studio' but I
> strongly recommend you don't do any data modification through a graphical
> window, but always do it through TSQL code, using a query window or
> application. You have much more control, and the ability to use the
> techniques Dan suggested.
> If you only want an estimate, you could also just look at the estimated
> query plan after entering your query in a query window. You can get this
> plan with Cntl-L
> If you hold your cursor over the estimated plan's first icon, it should
> display the estimated number of rows to be returned.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
> > Hi,
> >
> > When workinf on a SQL2005 Db through Sql Server Management studio, is
> > there a way to determine in advance how many rows will be affected as
> > a result of an Update,Insert,or Delete statement without actually
> > performing the query?
> >
> > thanks "in advance"
>
>|||Well, I did say it was only an estimate. :-)
It may be way off, but it might not be. I think it would be better than
nothing, and perhaps better than completely running the whole query just to
get a rowcount, depending on how exact the OP needs the number to be.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
> But for anything but really trivial quries, that optimizer estimate is
> probably so off that it may not be very useful.
> Linchi
> "Kalen Delaney" wrote:
>> Aamir
>> I'm not sure what you mean by 'through SQL Server Management Studio' but
>> I
>> strongly recommend you don't do any data modification through a graphical
>> window, but always do it through TSQL code, using a query window or
>> application. You have much more control, and the ability to use the
>> techniques Dan suggested.
>> If you only want an estimate, you could also just look at the estimated
>> query plan after entering your query in a query window. You can get this
>> plan with Cntl-L
>> If you hold your cursor over the estimated plan's first icon, it should
>> display the estimated number of rows to be returned.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Aamir Ghanchi" <aamirghanchi@.gmail.com> wrote in message
>> news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com...
>> > Hi,
>> >
>> > When workinf on a SQL2005 Db through Sql Server Management studio, is
>> > there a way to determine in advance how many rows will be affected as
>> > a result of an Update,Insert,or Delete statement without actually
>> > performing the query?
>> >
>> > thanks "in advance"
>>|||Thanks for all the responses.
And I'm sorry, should have clarified it. I am actually running a query
from query window in the management studio.
The estimate query plan was really way off and was in decimals ?
I liked the Rollback Transaction solution and it fits my needs. I know
it may be costly but I am not worried about that in my circumstances.
The Select and Selct Count statements are good but still not the same
as running the actual queries (some of them much complex with multiple
joins & subqueries)
Once again, thanks all.
On Jan 7, 12:21=A0am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Well, I did say it was only an estimate. :-)
> It may be way off, but it might not be. I think it would be better than
> nothing, and perhaps better than completely running the whole query just t=o
> get a rowcount, depending on how exact the OP needs the number to be.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "Linchi Shea" <LinchiS...@.discussions.microsoft.com> wrote in message
> news:237D20BB-D329-464B-974E-D74B48C80E6B@.microsoft.com...
>
> > But for anything but really trivial quries, that optimizer estimate is
> > probably so off that it may not be very useful.
> > Linchi
> > "Kalen Delaney" wrote:
> >> Aamir
> >> I'm not sure what you mean by 'through SQL Server Management Studio' bu=t
> >> I
> >> strongly recommend you don't do any data modification through a graphic=al
> >> window, but always do it through TSQL code, using a query window or
> >> application. You have much more control, and the ability to use the
> >> techniques Dan suggested.
> >> If you only want an estimate, you could also just look at the estimated=
> >> query plan after entering your query in a query window. You can get thi=s
> >> plan with Cntl-L
> >> If you hold your cursor over the estimated plan's first icon, it should=
> >> display the estimated number of rows to be returned.
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>www.InsideSQLServer.com
> >>http://blog.kalendelaney.com
> >> "Aamir Ghanchi" <aamirghan...@.gmail.com> wrote in message
> >>news:6b8c041a-2093-43c4-b294-3f4b908f36c3@.r60g2000hsc.googlegroups.com..=.
> >> > Hi,
> >> > When workinf on a SQL2005 Db through Sql Server Management studio, is=
> >> > there a way to determine in advance how many rows will be affected as=
> >> > a result of an Update,Insert,or Delete statement without actually
> >> > performing the query?
> >> > thanks "in advance"- Hide quoted text -
> - Show quoted text -