Hi folks,

I have data partitioning setup in place (1 table, divided to 16 partitions),
and I noticed that new partitions are much larger GB wise than the old ones
(switched out). However, number of rows in all partitions is pretty much the
same. I suspect this might be because XML column has much more data now, than
it had before. Does anybody know how can I calculate actual XML data type
column size?


Thanks in advance

"Pedja" <Pedja@discussions.microsoft.com> wrote in message
news:2AA95A16-44C6-40A0-A261-DBC434496762@microsoft.com...
> Hi folks,
>
> I have data partitioning setup in place (1 table, divided to 16
> partitions),
> and I noticed that new partitions are much larger GB wise than the old
> ones
> (switched out). However, number of rows in all partitions is pretty much
> the
> same. I suspect this might be because XML column has much more data now,
> than
> it had before. Does anybody know how can I calculate actual XML data type
> column size?
>
>
> Thanks in advance

You can use DATALENGTH function to return the size of each XML instance in
the column, on a row-by-row basis. The XML data type is an LOB type, so it
can hold up to 2.1 GB of data. If you have XML indexes set up on these
columns that could be consuming a good amount of space as well.

--

========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server...dp/1590599837/

thanks!

"Mike C#" wrote:

>
> "Pedja" <Pedja@discussions.microsoft.com> wrote in message
> news:2AA95A16-44C6-40A0-A261-DBC434496762@microsoft.com...
> > Hi folks,
> >
> > I have data partitioning setup in place (1 table, divided to 16
> > partitions),
> > and I noticed that new partitions are much larger GB wise than the old
> > ones
> > (switched out). However, number of rows in all partitions is pretty much
> > the
> > same. I suspect this might be because XML column has much more data now,
> > than
> > it had before. Does anybody know how can I calculate actual XML data type
> > column size?
> >
> >
> > Thanks in advance
>
> You can use DATALENGTH function to return the size of each XML instance in
> the column, on a row-by-row basis. The XML data type is an LOB type, so it
> can hold up to 2.1 GB of data. If you have XML indexes set up on these
> columns that could be consuming a good amount of space as well.
>
> --
>
> ========
> Michael Coles
> "Pro SQL Server 2008 XML"
> http://www.amazon.com/Pro-SQL-Server...dp/1590599837/
>
>
>