GetBytes

I'm creating an array of SqlParameters to send to SqlCommand.

Every parameter has to have a size, or I get an error.

Would it be efficient to get the bytes of every Object (parameter value) and pass it to the array?

What about truncation?

Does someone know a better method of doing this?

You can get length of all parameters using SqlCommandBuilder.DeriveParameters function. For more information you see http://www.davidhayden.com/blog/dave/archive/2006/11/01/SqlCommandBuilderDeriveParameters.aspx

Get table row size

How can we get the newly inserted row's size from a SQL Server table?

Quote:

Originally Posted by soumyamathewmca

How can we get the newly inserted row's size from a SQL Server table?

select
sys.objects.[name],
sys.objects.[object_id],
count(sys.columns.[name]) As ColumnCount,
sum(sys.columns.max_length) As MaxLength
from
sys.objects
inner join sys.columns on sys.objects.object_id = sys.columns.object_id
where
sys.objects.[name] = Table_Name
group by
sys.objects.[name],
sys.objects.[object_id]

Get size of a table/query

Is there a way to get the size of a query of a table?

I know I can use DATALENGTH on every column in my query, but I thought there might be an easier way.what exacly do you mean by size?, number of rows?|||see sp_spaceused in BOL|||sp_spaceused will get the size of the table, but I would like to get the size of results from a query. Would I need to create a temp table from my query and then use sp_spaceused to get the size of the temp table?|||I think it is a possibility
Also you can look through the sp_apceused code to see how they do it|||Yes, it does appear to be possible. I wonder though, it seems a lot more "expensive" to create a temporary table and then get the size of it than to use SUM(DATALENGTH(mycolumn)), but I could be wrong. The reason I say that is one of the tables I'm using this on contains image(blob) data which can be very large. If I have to copy all of that data into a temp table, that could be a bit sluggish. Anyone know performance wise which is better to use?|||you are right
it is definitely non performant

why do you want to measure the query ?
may be there is another way to do what you are trying to do ?|||I have a CMS. Multiple clients use the same tables in my CMS but I need to discover how much space each individual client is taking up. The only way to do this that I've found is to measure the size of the query data.|||it is not good way.
You can just have clientid in each row in the tables (and/or separate clients table)
and count rows each client has (anyway counting rows)|||I assume you mean getting the size of the entire table and calculating client size based on their number of rows times and average size/row. I don't think this is an accurate assessment of how much space a client is consuming especially when one of the columns in the table is a blob field. One client could be consuming most of the space in the table because of a huge value for the blob field in a single record. Using the SUM of DATALENGTH of the blob field works for me. I already have each record assigned a clientID so I can get an accurate count of how much space they are taking up. I just thought there might be a better way other than using SUM of DATALENGTH.

Get Size of a dedicated table

Hi,
I'd like to analyse my db. Does anybody how to get an overview about the
table sizes?
TIA
Pete
Pete Smith
Pete Smith wrote:
> Hi,
> I'd like to analyse my db. Does anybody how to get an overview about
> the table sizes?
> TIA
> Pete
Have a look at sp_spaceused in BOL.
David Gugick
Imceda Software
www.imceda.com

Get physical size of indexes

How do I get the physical size of all individual indexes in a database.
Thanks in advance.
Hello
There is a topic in SQL Books Online titled "Estimating the Size of a Table
with a Clustered Index" which explains the calculation that you can use to
calculate the approximate size of the clustered and non-clustered index for
a table.
If you want to check the actual size of an existing index, refer to the
Books Online topic on "sysindexes" table. The "dpages", "reserved" and
"used" fields indicate the number of 8K pages as explained in the help
topic. Also please note that you may have to perform DBCC UPDATEUSAGE to
get accurate numbers in these fields.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Be aware however that the dpages, etc , and the info received from
sp_spaceused do NOT get updated with each insert, update or delete. They may
be inaccurate. They OFTEN are inaccurate...
Use DBCC Updateusage
or sp_spaceused @.updateusage = true
to force SQL to read the data and update the size information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ferde" <Ferde@.discussions.microsoft.com> wrote in message
news:3CF862DA-044A-49E8-ABE3-0F61D90D249A@.microsoft.com...
> How do I get the physical size of all individual indexes in a database.
> Thanks in advance.
>

Get physical size of indexes

How do I get the physical size of all individual indexes in a database.
Thanks in advance.Hello
There is a topic in SQL Books Online titled "Estimating the Size of a Table
with a Clustered Index" which explains the calculation that you can use to
calculate the approximate size of the clustered and non-clustered index for
a table.
If you want to check the actual size of an existing index, refer to the
Books Online topic on "sysindexes" table. The "dpages", "reserved" and
"used" fields indicate the number of 8K pages as explained in the help
topic. Also please note that you may have to perform DBCC UPDATEUSAGE to
get accurate numbers in these fields.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Be aware however that the dpages, etc , and the info received from
sp_spaceused do NOT get updated with each insert, update or delete. They may
be inaccurate. They OFTEN are inaccurate...
Use DBCC Updateusage
or sp_spaceused @.updateusage = true
to force SQL to read the data and update the size information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ferde" <Ferde@.discussions.microsoft.com> wrote in message
news:3CF862DA-044A-49E8-ABE3-0F61D90D249A@.microsoft.com...
> How do I get the physical size of all individual indexes in a database.
> Thanks in advance.
>

Get physical size of indexes

How do I get the physical size of all individual indexes in a database.
Thanks in advance.Hello
There is a topic in SQL Books Online titled "Estimating the Size of a Table
with a Clustered Index" which explains the calculation that you can use to
calculate the approximate size of the clustered and non-clustered index for
a table.
If you want to check the actual size of an existing index, refer to the
Books Online topic on "sysindexes" table. The "dpages", "reserved" and
"used" fields indicate the number of 8K pages as explained in the help
topic. Also please note that you may have to perform DBCC UPDATEUSAGE to
get accurate numbers in these fields.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Be aware however that the dpages, etc , and the info received from
sp_spaceused do NOT get updated with each insert, update or delete. They may
be inaccurate. They OFTEN are inaccurate...
Use DBCC Updateusage
or sp_spaceused @.updateusage = true
to force SQL to read the data and update the size information...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ferde" <Ferde@.discussions.microsoft.com> wrote in message
news:3CF862DA-044A-49E8-ABE3-0F61D90D249A@.microsoft.com...
> How do I get the physical size of all individual indexes in a database.
> Thanks in advance.
>

get max row size for every table

SQL Server 2000 has a max size of 8k per row limit. In an existing database,
how do I find out to get the tables that have this problem.
Is there a procedure that I can use or a sql script?
Magic
SELECT ob.name [Table Name], sum(col.length) [Max column Length]
from sysobjects ob, syscolumns col
where ob.id = col.id and ob.xtype = 'U'
group by ob.name
"Magic" <Magic@.discussions.microsoft.com> wrote in message
news:BC335A68-429E-4246-ABD9-55928A00F3AD@.microsoft.com...
> SQL Server 2000 has a max size of 8k per row limit. In an existing
database,
> how do I find out to get the tables that have this problem.
> Is there a procedure that I can use or a sql script?

get max row size for every table

SQL Server 2000 has a max size of 8k per row limit. In an existing database
,
how do I find out to get the tables that have this problem.
Is there a procedure that I can use or a sql script?Magic
SELECT ob.name [Table Name], sum(col.length) [Max column Length]
from sysobjects ob, syscolumns col
where ob.id = col.id and ob.xtype = 'U'
group by ob.name
"Magic" <Magic@.discussions.microsoft.com> wrote in message
news:BC335A68-429E-4246-ABD9-55928A00F3AD@.microsoft.com...
> SQL Server 2000 has a max size of 8k per row limit. In an existing
database,
> how do I find out to get the tables that have this problem.
> Is there a procedure that I can use or a sql script?

get max row size for every table

SQL Server 2000 has a max size of 8k per row limit. In an existing database,
how do I find out to get the tables that have this problem.
Is there a procedure that I can use or a sql script?Magic
SELECT ob.name [Table Name], sum(col.length) [Max column Length]
from sysobjects ob, syscolumns col
where ob.id = col.id and ob.xtype = 'U'
group by ob.name
"Magic" <Magic@.discussions.microsoft.com> wrote in message
news:BC335A68-429E-4246-ABD9-55928A00F3AD@.microsoft.com...
> SQL Server 2000 has a max size of 8k per row limit. In an existing
database,
> how do I find out to get the tables that have this problem.
> Is there a procedure that I can use or a sql script?