Easy way to find size of data returned in SQL 2005 ?

Id like to know when i have a query that returns say 12 million rows as an
example, is there an easy way to find out how much of data that is in MB or
GB ?
Thanks
Hassan
There's no automatic way. You could estimate the size of each row, and then
multiply by @.@.rowcount. Or capture the data in a temp table and use
sp_spaceused. Or find a tool that measures how much data is sent over the
network. There is a client statistics option in Query Analyzer and SSMS, but
I can't imagine you really want to send all 12 million rows to the client.
You've been asking lots of questions lately, and lots of people have been
providing answers. It would be nice to know if the answers you are getting
are useful to you.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>
|||Hello,
My suggestion will be:-
Populate a sample of 2 million row into a temp table and then use
SP_SPaceused. After that you could take an average of size per record and
multiply by total returned. There
is no direct method to know this...
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>
|||Kalen,
I know at times it may appear that I am very thankless, but if not for the
newsgroup and the responses I get that have been very positive and
satisfactory, I wouldnt have been posting time and again out here.
Id love to thank all of the users for responding to not just my questions
but to others questions as well.
Keep up the good work..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O0mQbYDGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hassan
> There's no automatic way. You could estimate the size of each row, and
> then multiply by @.@.rowcount. Or capture the data in a temp table and use
> sp_spaceused. Or find a tool that measures how much data is sent over the
> network. There is a client statistics option in Query Analyzer and SSMS,
> but I can't imagine you really want to send all 12 million rows to the
> client.
> You've been asking lots of questions lately, and lots of people have been
> providing answers. It would be nice to know if the answers you are getting
> are useful to you.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
>

Easy way to find size of data returned in SQL 2005 ?

Id like to know when i have a query that returns say 12 million rows as an
example, is there an easy way to find out how much of data that is in MB or
GB ?
ThanksHassan
There's no automatic way. You could estimate the size of each row, and then
multiply by @.@.rowcount. Or capture the data in a temp table and use
sp_spaceused. Or find a tool that measures how much data is sent over the
network. There is a client statistics option in Query Analyzer and SSMS, but
I can't imagine you really want to send all 12 million rows to the client.
You've been asking lots of questions lately, and lots of people have been
providing answers. It would be nice to know if the answers you are getting
are useful to you.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Hello,
My suggestion will be:-
Populate a sample of 2 million row into a temp table and then use
SP_SPaceused. After that you could take an average of size per record and
multiply by total returned. There
is no direct method to know this...
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Kalen,
I know at times it may appear that I am very thankless, but if not for the
newsgroup and the responses I get that have been very positive and
satisfactory, I wouldnt have been posting time and again out here.
Id love to thank all of the users for responding to not just my questions
but to others questions as well.
Keep up the good work..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O0mQbYDGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hassan
> There's no automatic way. You could estimate the size of each row, and
> then multiply by @.@.rowcount. Or capture the data in a temp table and use
> sp_spaceused. Or find a tool that measures how much data is sent over the
> network. There is a client statistics option in Query Analyzer and SSMS,
> but I can't imagine you really want to send all 12 million rows to the
> client.
> You've been asking lots of questions lately, and lots of people have been
> providing answers. It would be nice to know if the answers you are getting
> are useful to you.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
>

Easy way to find size of data returned in SQL 2005 ?

Id like to know when i have a query that returns say 12 million rows as an
example, is there an easy way to find out how much of data that is in MB or
GB ?
ThanksHassan
There's no automatic way. You could estimate the size of each row, and then
multiply by @.@.rowcount. Or capture the data in a temp table and use
sp_spaceused. Or find a tool that measures how much data is sent over the
network. There is a client statistics option in Query Analyzer and SSMS, but
I can't imagine you really want to send all 12 million rows to the client.
You've been asking lots of questions lately, and lots of people have been
providing answers. It would be nice to know if the answers you are getting
are useful to you.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Hello,
My suggestion will be:-
Populate a sample of 2 million row into a temp table and then use
SP_SPaceused. After that you could take an average of size per record and
multiply by total returned. There
is no direct method to know this...
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Kalen,
I know at times it may appear that I am very thankless, but if not for the
newsgroup and the responses I get that have been very positive and
satisfactory, I wouldnt have been posting time and again out here.
Id love to thank all of the users for responding to not just my questions
but to others questions as well.
Keep up the good work..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O0mQbYDGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hassan
> There's no automatic way. You could estimate the size of each row, and
> then multiply by @.@.rowcount. Or capture the data in a temp table and use
> sp_spaceused. Or find a tool that measures how much data is sent over the
> network. There is a client statistics option in Query Analyzer and SSMS,
> but I can't imagine you really want to send all 12 million rows to the
> client.
> You've been asking lots of questions lately, and lots of people have been
> providing answers. It would be nice to know if the answers you are getting
> are useful to you.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Id like to know when i have a query that returns say 12 million rows as
>> an example, is there an easy way to find out how much of data that is in
>> MB or GB ?
>> Thanks
>sql

Easy question - changing Visibility of textbox

I'm trying to affect the visibility of a text box based upon a field value
returned in my dataset. The field is a summed field, and I simply do NOT
want to show the field if it is 0, less than zero, or NULL.
What should my expression for the "Visbility" property look like?
TIA,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.comiif(Parameters!Fieldname.Value <= 0 OR Parameters!Fieldname.Value IS Nothing, True, False)
"G" wrote:
> I'm trying to affect the visibility of a text box based upon a field value
> returned in my dataset. The field is a summed field, and I simply do NOT
> want to show the field if it is 0, less than zero, or NULL.
> What should my expression for the "Visbility" property look like?
> TIA,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>
>|||IS Nothing
that is what was tripping me up, thanks comet.
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com
"comet61" <comet61@.discussions.microsoft.com> wrote in message
news:CFF1A78C-9DE1-475E-9BB9-5D4D6F4EAE04@.microsoft.com...
> iif(Parameters!Fieldname.Value <= 0 OR Parameters!Fieldname.Value IS
Nothing, True, False)
> "G" wrote:
> > I'm trying to affect the visibility of a text box based upon a field
value
> > returned in my dataset. The field is a summed field, and I simply do NOT
> > want to show the field if it is 0, less than zero, or NULL.
> >
> > What should my expression for the "Visbility" property look like?
> >
> > TIA,
> >
> > --
> > Brian Grant
> > Senior Programmer
> > SI International
> > www.si-intl.com
> >
> >
> >

Dyncamic SQL

Hi
I am trying to populate a Temp table with dynamic SQL but it does not seem
to work and the error message returned is that is it an invalid object,
leading me to believe that it does not get created:
-- ****************************************
********
USE Pubs
GO
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
EXEC (@.SQLString)
SELECT * FROM #TempParamFilter
-- ****************************************
********
Is there something wrong with my syntax?
Kind Regards
RickyThe problem is SCOPE. The # temp table you created goes away after the
dynamic SQL finishes executing. Based on what you posted, you don't even
need dynamic SQL to do this particular job:
USE Pubs
GO
SELECT * INTO #TempParamFilter FROM Authors
SELECT * FROM #TempParamFilter
DROP TABLE #TempParamFilter
"Ricky" <ricky@.msn.com> wrote in message
news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi
> I am trying to populate a Temp table with dynamic SQL but it does not seem
> to work and the error message returned is that is it an invalid object,
> leading me to believe that it does not get created:
> -- ****************************************
********
> USE Pubs
> GO
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
> EXEC (@.SQLString)
> SELECT * FROM #TempParamFilter
>
> -- ****************************************
********
> Is there something wrong with my syntax?
> Kind Regards
> Ricky
>|||Hi Mike
It was a simple example, I should have posted the real situation. What I am
really trying to achieve is a dynamic WHERE clause, appended to a table and
then populate a temporary table. Is this possible.
I mention Dynamic WHERE clause, since the Column may change, depending on
the parameter supplied.
e.g
@.StockParam = 'Q1HTS'
then WHERE clause would be : WHERE QStock = @.StockParam
or
@.StockParam = 'T1HTS'
then WHERE clause would be : WHERE AlphaStock = @.StockParam
so what I thought about doing, was to have my basic select statement and
then append a dynamic WHERE clause as a variable and then populate a #Table
to SELECT from , later when compiling the final recordset.
Hope this makes sense.
Kind Regards
Ricky
"Mike C#" <xyz@.xyz.com> wrote in message
news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> The problem is SCOPE. The # temp table you created goes away after the
> dynamic SQL finishes executing. Based on what you posted, you don't even
> need dynamic SQL to do this particular job:
> USE Pubs
> GO
> SELECT * INTO #TempParamFilter FROM Authors
> SELECT * FROM #TempParamFilter
> DROP TABLE #TempParamFilter
> "Ricky" <ricky@.msn.com> wrote in message
> news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
seem
>|||The problem is that the temp table is available only within the scope of
EXEC. Either use a global temp table or use the SELECT within the scope of
the EXEC like:
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
SELECT * FROM #TempParamFilter '
EXEC (@.SQLString) ;
Anith|||Probably not the most performant, but
WHERE
QStock = CASE @.StockParam
WHEN 'Q1HTS' THEN @.StockParam
ELSE QStock END
AND
AlphaStock = CASE @.StockParam
WHEN 'T1HTS' THEN @.StockParam
ELSE AlphaStock END
What do you need a #temp table for?
"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
> Hope this makes sense.
> Kind Regards
> Ricky
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> seem
>|||Hi Anith
How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Kind Regards
Ricky
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
> The problem is that the temp table is available only within the scope of
> EXEC. Either use a global temp table or use the SELECT within the scope of
> the EXEC like:
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
> SELECT * FROM #TempParamFilter '
> EXEC (@.SQLString) ;
> --
> Anith
>|||"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
It's possible if you create the temp table before executing the dynamic sql.
Here's an example to get you started. Note that the temp table is created
outside of the dynamic SQL, but the dynamic SQL has access to it. It won't
work the other way around. Also note that this example uses sp_executesql
to parameterize the query. sp_executesql requires NVARCHAR data, and helps
protect against SQL injection:
USE pubs
GO
CREATE TABLE #temp_emp(emp_id VARCHAR(9) NOT NULL PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
minit CHAR(1) NOT NULL,
lname VARCHAR(30) NOT NULL)
DECLARE @.emp_last_name NVARCHAR(30)
SELECT @.emp_last_name = N'Smith'
DECLARE @.dyn_sql NVARCHAR(512)
SELECT @.dyn_sql = N'INSERT INTO #temp_emp (emp_id, fname, minit, lname) ' +
N'SELECT emp_id, fname, minit, lname ' +
N'FROM employee ' +
N'WHERE lname = @.lname'
EXEC dbo.sp_executesql @.dyn_sql, N'@.lname NVARCHAR(30)', @.lname =
@.emp_last_name
SELECT *
FROM #temp_emp
DROP TABLE #temp_emp|||> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Because a single EXEC() call represents one 'scope' (I'm not sure if that's
a valid noun there, but oh well). The second SELECT is occuring in the same
scope as that which created the #temp table.|||Another way to think about the scope of something like EXEC() is a typical
popup window in a browser (the good kind, not the annoying advertisements).
In most cases, the popup window could jump through several different pages
and do all kinds of things, and the window that opened it couldn't care less
and usually doesn't have any knowledge of what is going on in the popup.
"Ricky" <ricky@.msn.com> wrote in message
news:OPO%23KpVkGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi Anith
> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
> Kind Regards
> Ricky
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u4VydtVkGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Because a single EXEC() call represents one 'scope' (I'm not sure if
> that's a valid noun there, but oh well). The second SELECT is occuring in
> the same scope as that which created the #temp table.
You know, I always hated the phrase "well-defined scope" for table
variables, etc. It implies that everything else has "poorly-defined scope"
:) But from a marketing perspective I guess "well-defined scope" sounds
better than "limited scope" or "extremely tight scope" :)