estimate table spaces

hi guys,
sp_spaceused 'table1'
I have two ntext columns in 'table1',
by default, is sp_spaceused calculating space for ntext too?Hi Britney
All columns are included. You can see this for yourself:
use pubs
go
select * into newtitles from titles
go
exec sp_spaceused newtitles, @.updateusage= true
go
alter table newtitles add info ntext
go
update newtitles set info = replicate(title, 100)
go
exec sp_spaceused newtitles, @.updateusage= true
go
HTH
Kalen Delaney
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:emryCsOpFHA.1044@.tk2msftngp13.phx.gbl...
> hi guys,
>
> sp_spaceused 'table1'
> I have two ntext columns in 'table1',
> by default, is sp_spaceused calculating space for ntext too?
>
>|||When NTEXT column is NULL, how come it still takes some spaces?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODCxkYQpFHA.3828@.TK2MSFTNGP12.phx.gbl...
> Hi Britney
> All columns are included. You can see this for yourself:
> use pubs
> go
> select * into newtitles from titles
> go
> exec sp_spaceused newtitles, @.updateusage= true
> go
> alter table newtitles add info ntext
> go
> update newtitles set info = replicate(title, 100)
> go
> exec sp_spaceused newtitles, @.updateusage= true
> go
> HTH
> Kalen Delaney
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:emryCsOpFHA.1044@.tk2msftngp13.phx.gbl...
>
>|||Kevin
LOB data (type text, ntext and image) is by default stored on separate pages
outside the data rows. As soon as you update any rows with LOB data to
anything, even null, SQL Server will allocate at least 2 additional pages to
start keeping track of that data.
FYI, for ANY fixed length data column, NULLs will take space. So a char(100)
that contains NULL will take the full 100 bytes.
HTH
Kalen Delaney
www.solidqualitylearning.com
"kevin" <pearl_77@.hotmail.com> wrote in message
news:%23OP4VFYpFHA.3380@.TK2MSFTNGP12.phx.gbl...
> When NTEXT column is NULL, how come it still takes some spaces?
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ODCxkYQpFHA.3828@.TK2MSFTNGP12.phx.gbl...
>
>

Estimate storage saving of varDecimal

For an existing database is there an automated method of calculating
the storage saving of using varDecimal storage type?There is a whitepaper that was just released answering this very question:
http://msdn2.microsoft.com/en-us/library/bb508963.aspx
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:1180623179.357966.223520@.h2g2000hsg.googlegroups.com...
> For an existing database is there an automated method of calculating
> the storage saving of using varDecimal storage type?
>|||I had already seen this but after reading it again I noticed that the
stored procedures mentioned could use the existing tables and not just
the best and worst cases as in the whitepaper.
On 31 May, 15:58, "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]>
wrote:
> There is a whitepaper that was just released answering this very question:
> http://msdn2.microsoft.com/en-us/library/bb508963.aspx
> "Robin9876" <robin9...@.hotmail.com> wrote in message
> news:1180623179.357966.223520@.h2g2000hsg.googlegroups.com...
> > For an existing database is there an automated method of calculating
> > the storage saving of using varDecimal storage type?sql

Error: The MDX function ROOT failed because...

I'm having problem calculating sales for all customer minus certain occupations.

This Adventureworks query is something like what Excel 2003 produces.

Code Snippet

WITH MEMBER [Customer].[Occupation].[Exclude Occupations] AS

'AGGREGATE({

[Customer].[Occupation].&[Clerical],

[Customer].[Occupation].&[Management]

})'

MEMBER Measures.[Other Occupation Sales] as (ROOT(Customer), [Measures].[Internet Sales Amount]) - [Measures].[Internet Sales Amount]

SELECT

{Measures.[Other Occupation Sales]} ON COLUMNS,

[Product].[Category].[Category].MEMBERS ON ROWS

FROM [Adventure Works]

WHERE

([Customer].[Occupation].[Exclude Occupations])

Error: The MDX function ROOT failed because the coordinate for the 'Occupation' attribute contains a set.

If you delete this line, the query works!

[Customer].[Occupation].&[Clerical],

Does anyone know what this means? Is there another way to get Excel to formulate an equivalent query?

The problem is because there is a set in the current coordinate, and Root has problem dealing with it (although it shouldn't!). The best solution is to replace Root(Customer) with [All Customers] - which will have exactly same functionality, better performance, and won't generate the error that started this thread.|||

Yes, I replaced Root(Customer) with [Customer].[Occupation].[All Customers] and it works. The problem is that I wanted to create a general purpose calculated measure that would override every attribute in the customer dimension with "All". I thought Root did precisely that. If I can't use Root() then I will need to remember to update this calculation every time an attribute is added or removed from the customer dimension.

Thanks for the input.

|||

> The problem is that I wanted to create a general purpose calculated measure that would override every attribute in the customer dimension with "All"

[All Customers] does exactly that ! There is no need to update your calculation every time new attribute is added, [All Customers] (in any hierarchy of customers dimension) will automatically move coordinate to All member in every attribute.

|||

Try my AdventureWorks repro with [All Customers] instead of Root(). It doesn't work. It doesn't replace the aggregate set of occupations with the All tuple. I end up with zeros in the results because [All Customers] is doing absolutely nothing for me.

The only way I got it to work was with [Customer].[Occupation].[All Customers]. This member does, in fact, replace my aggregate set of occupations.

Put that in your pipe and smoke it!

I'm curious how [All Customers] is defined to work (vs. Root()).

|||Of course - you are right. I don't know what I was thinking. What I said is only true when dimension has single hierarchy which includes all attributes, which is obviously not the case for most of the dimensions, especially Customer.|||Thanks for the help. At least I have a work-around for now. It would be nice if Root(dimension) did the same thing as change coordinates to [All Customers] on every attribute. Maybe in the next version...