Difference between the estimated size and size given by sp_spaceus

Hi,
The Books Online article "Estimating the Size of a Table" gives you the
method to estimate the size of a table. Using this method I estimated the
size for one table. However after populating that table with data, I used
sp_spaceused to find out the space used by this particular table, but to my
surprise I found big difference between the value given by method "Estimating
the Size of a Table" and sp_spaceused stored procedure. I also passed
updateusage parameter as true. Can anybody throw some light on why such
difference is found between these values?
Regards,
Vaibhav
Vaibhav
Try UPDATE STATISTICS and then run sp_spaceused.
"Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
news:310A97C9-498D-4C12-B92A-F8C53814F4FF@.microsoft.com...
> Hi,
> The Books Online article "Estimating the Size of a Table" gives you the
> method to estimate the size of a table. Using this method I estimated the
> size for one table. However after populating that table with data, I used
> sp_spaceused to find out the space used by this particular table, but to
my
> surprise I found big difference between the value given by method
"Estimating
> the Size of a Table" and sp_spaceused stored procedure. I also passed
> updateusage parameter as true. Can anybody throw some light on why such
> difference is found between these values?
> Regards,
> Vaibhav
|||> The Books Online article "Estimating the Size of a Table" gives you the
> method to estimate the size of a table. Using this method I estimated the
> size for one table. However after populating that table with data, I used
> sp_spaceused to find out the space used by this particular table, but to
my
> surprise I found big difference between the value given by method
"Estimating
> the Size of a Table" and sp_spaceused stored procedure. I also passed
> updateusage parameter as true. Can anybody throw some light on why such
> difference is found between these values?
Have you counted the indexes? Do you have variable length data types?
What are actal numbers?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Well, do you have any variable-length data (VARCHAR, VARBINARY, TEXT,
IMAGE)? How many of them are populated to the maximum capacity, how many
are NULL (or empty string), how many have valid data? What kind of indexes
do you have? What is the "big difference", 10%, 50%?
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
news:310A97C9-498D-4C12-B92A-F8C53814F4FF@.microsoft.com...
> Hi,
> The Books Online article "Estimating the Size of a Table" gives you the
> method to estimate the size of a table. Using this method I estimated the
> size for one table. However after populating that table with data, I used
> sp_spaceused to find out the space used by this particular table, but to
my
> surprise I found big difference between the value given by method
"Estimating
> the Size of a Table" and sp_spaceused stored procedure. I also passed
> updateusage parameter as true. Can anybody throw some light on why such
> difference is found between these values?
> Regards,
> Vaibhav

difference between table relations and foreign keys?

I've bought a book about ASP.NET with SQL Server 2000.
In one chapter, they talk about setting relations between tables, and in
another, about setting foreign keys.
But I can't understand the difference, as the purpose seems to be the same:
navigating from a parent table to a child one, and vice-versa.
Am I missing something?
Thanks!
HenriUnless you can show more detailed context, I think they are talking about
the exact same thing. A relationship is a bit more abstract; a foreign key
represents the implementation-specific way that a child row is connected to
a parent row.
http://www.aspfaq.com/
(Reverse address to reply.)
"Henri" <hmfireball@.hotmail.com> wrote in message
news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> I've bought a book about ASP.NET with SQL Server 2000.
> In one chapter, they talk about setting relations between tables, and in
> another, about setting foreign keys.
> But I can't understand the difference, as the purpose seems to be the
> same:
> navigating from a parent table to a child one, and vice-versa.
> Am I missing something?
> Thanks!
> Henri
>
>|||Actually, the first chapter is about the DataRelation class, and
DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
The second chapter is about ForeignKeyConstraint Class
The only difference I see is that a foreign key allows to define cascade
update and delete rules.
Actually, ASP.NET permits to define many things like indexes, primary keys,
relations, etc. but I thought that this had to be done on SQL Server, not
from ASP.NET.
That's why I'm a bit lost :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le messag
e de
news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
> Unless you can show more detailed context, I think they are talking about
> the exact same thing. A relationship is a bit more abstract; a foreign
key
> represents the implementation-specific way that a child row is connected
to
> a parent row.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
>
>|||What you are reading about is Data Sets in ADO.Net. The
relations and constraints you define in a data set are
different and separate from relations and constraints in the
database. In ADO.Net, you can create something similar to a
database - tables (data tables), relationships (using the
DataRelation class), constraints (such as with the
ForeignKeyConstraint class). It's not the same thing as the
database you may be accessing for the data in the data sets.
For ADO.Net, you can find more information on how relations
and constraints work together, differently in .Net Framework
documentation. Check the remarks section of the definition
of the DataRelation class.
-Sue
On Fri, 11 Jun 2004 03:40:00 +0200, "Henri"
<hmfireball@.hotmail.com> wrote:

>Actually, the first chapter is about the DataRelation class, and
>DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
>The second chapter is about ForeignKeyConstraint Class
>The only difference I see is that a foreign key allows to define cascade
>update and delete rules.
>Actually, ASP.NET permits to define many things like indexes, primary keys,
>relations, etc. but I thought that this had to be done on SQL Server, not
>from ASP.NET.
>That's why I'm a bit lost :-)
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le messa
ge de
>news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
>key
>to
>|||Hi,
Within ADO.Net, a Foreign Key Constaint is a subtype of the Data Relation. T
he FKC in ADO.Net behaves in a similar manner to a FKC within SQL Server, bu
t it is a constraint on the Data Set, not the database, which can be an impo
rtant consideration.
A Data Relation has additonal functionality that is not provided by the FKC,
( I think one is providing the Child Rows in the Data Set to a particular M
aster row.) I suggest you have a look at the class definitions and examine t
he different methods and pr
operties each suppiles

difference between table relations and foreign keys?

I've bought a book about ASP.NET with SQL Server 2000.
In one chapter, they talk about setting relations between tables, and in
another, about setting foreign keys.
But I can't understand the difference, as the purpose seems to be the same:
navigating from a parent table to a child one, and vice-versa.
Am I missing something?
Thanks!
HenriUnless you can show more detailed context, I think they are talking about
the exact same thing. A relationship is a bit more abstract; a foreign key
represents the implementation-specific way that a child row is connected to
a parent row.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Henri" <hmfireball@.hotmail.com> wrote in message
news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> I've bought a book about ASP.NET with SQL Server 2000.
> In one chapter, they talk about setting relations between tables, and in
> another, about setting foreign keys.
> But I can't understand the difference, as the purpose seems to be the
> same:
> navigating from a parent table to a child one, and vice-versa.
> Am I missing something?
> Thanks!
> Henri
>
>|||Actually, the first chapter is about the DataRelation class, and
DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
The second chapter is about ForeignKeyConstraint Class
The only difference I see is that a foreign key allows to define cascade
update and delete rules.
Actually, ASP.NET permits to define many things like indexes, primary keys,
relations, etc. but I thought that this had to be done on SQL Server, not
from ASP.NET.
That's why I'm a bit lost :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a écrit dans le message de
news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
> Unless you can show more detailed context, I think they are talking about
> the exact same thing. A relationship is a bit more abstract; a foreign
key
> represents the implementation-specific way that a child row is connected
to
> a parent row.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> > I've bought a book about ASP.NET with SQL Server 2000.
> > In one chapter, they talk about setting relations between tables, and in
> > another, about setting foreign keys.
> > But I can't understand the difference, as the purpose seems to be the
> > same:
> > navigating from a parent table to a child one, and vice-versa.
> > Am I missing something?
> > Thanks!
> > Henri
> >
> >
> >
>
>|||What you are reading about is Data Sets in ADO.Net. The
relations and constraints you define in a data set are
different and separate from relations and constraints in the
database. In ADO.Net, you can create something similar to a
database - tables (data tables), relationships (using the
DataRelation class), constraints (such as with the
ForeignKeyConstraint class). It's not the same thing as the
database you may be accessing for the data in the data sets.
For ADO.Net, you can find more information on how relations
and constraints work together, differently in .Net Framework
documentation. Check the remarks section of the definition
of the DataRelation class.
-Sue
On Fri, 11 Jun 2004 03:40:00 +0200, "Henri"
<hmfireball@.hotmail.com> wrote:
>Actually, the first chapter is about the DataRelation class, and
>DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
>The second chapter is about ForeignKeyConstraint Class
>The only difference I see is that a foreign key allows to define cascade
>update and delete rules.
>Actually, ASP.NET permits to define many things like indexes, primary keys,
>relations, etc. but I thought that this had to be done on SQL Server, not
>from ASP.NET.
>That's why I'm a bit lost :-)
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a écrit dans le message de
>news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
>> Unless you can show more detailed context, I think they are talking about
>> the exact same thing. A relationship is a bit more abstract; a foreign
>key
>> represents the implementation-specific way that a child row is connected
>to
>> a parent row.
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Henri" <hmfireball@.hotmail.com> wrote in message
>> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
>> > I've bought a book about ASP.NET with SQL Server 2000.
>> > In one chapter, they talk about setting relations between tables, and in
>> > another, about setting foreign keys.
>> > But I can't understand the difference, as the purpose seems to be the
>> > same:
>> > navigating from a parent table to a child one, and vice-versa.
>> > Am I missing something?
>> > Thanks!
>> > Henri
>> >
>> >
>> >
>>
>|||Hi
Within ADO.Net, a Foreign Key Constaint is a subtype of the Data Relation. The FKC in ADO.Net behaves in a similar manner to a FKC within SQL Server, but it is a constraint on the Data Set, not the database, which can be an important consideration
A Data Relation has additonal functionality that is not provided by the FKC, ( I think one is providing the Child Rows in the Data Set to a particular Master row.) I suggest you have a look at the class definitions and examine the different methods and properties each suppiles|||Thanks for your help to both of you :-)
Just one last question:
If I define constraints and relations(diagrams) within SQL Server, can
ADO.NET retrieve them, so that I can use them without having to redefine
them when declaring the DataSet?
I'm currently checking the.NET doc and might find the answer by myself, but
if you know it, thank you for telling me :-)
Henri
"Henri" <hmfireball@.hotmail.com> a écrit dans le message de
news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> I've bought a book about ASP.NET with SQL Server 2000.
> In one chapter, they talk about setting relations between tables, and in
> another, about setting foreign keys.
> But I can't understand the difference, as the purpose seems to be the
same:
> navigating from a parent table to a child one, and vice-versa.
> Am I missing something?
> Thanks!
> Henri
>
>|||AFAIK, not by itself. I'd be surprised if there weren't utilities available out there to generate this stuff
for you, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Henri" <hmfireball@.hotmail.com> wrote in message news:uvGyEt5TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> Thanks for your help to both of you :-)
> Just one last question:
> If I define constraints and relations(diagrams) within SQL Server, can
> ADO.NET retrieve them, so that I can use them without having to redefine
> them when declaring the DataSet?
> I'm currently checking the.NET doc and might find the answer by myself, but
> if you know it, thank you for telling me :-)
> Henri
> "Henri" <hmfireball@.hotmail.com> a écrit dans le message de
> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> > I've bought a book about ASP.NET with SQL Server 2000.
> > In one chapter, they talk about setting relations between tables, and in
> > another, about setting foreign keys.
> > But I can't understand the difference, as the purpose seems to be the
> same:
> > navigating from a parent table to a child one, and vice-versa.
> > Am I missing something?
> > Thanks!
> > Henri
> >
> >
> >
> >
>
>|||Thanks :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a écrit
dans le message de news:eljMa15TEHA.1012@.TK2MSFTNGP09.phx.gbl...
> AFAIK, not by itself. I'd be surprised if there weren't utilities
available out there to generate this stuff
> for you, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Henri" <hmfireball@.hotmail.com> wrote in message
news:uvGyEt5TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> > Thanks for your help to both of you :-)
> > Just one last question:
> > If I define constraints and relations(diagrams) within SQL Server, can
> > ADO.NET retrieve them, so that I can use them without having to redefine
> > them when declaring the DataSet?
> > I'm currently checking the.NET doc and might find the answer by myself,
but
> > if you know it, thank you for telling me :-)
> > Henri
> >
> > "Henri" <hmfireball@.hotmail.com> a écrit dans le message de
> > news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> > > I've bought a book about ASP.NET with SQL Server 2000.
> > > In one chapter, they talk about setting relations between tables, and
in
> > > another, about setting foreign keys.
> > > But I can't understand the difference, as the purpose seems to be the
> > same:
> > > navigating from a parent table to a child one, and vice-versa.
> > > Am I missing something?
> > > Thanks!
> > > Henri
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
>

difference between table relations and foreign keys?

I've bought a book about ASP.NET with SQL Server 2000.
In one chapter, they talk about setting relations between tables, and in
another, about setting foreign keys.
But I can't understand the difference, as the purpose seems to be the same:
navigating from a parent table to a child one, and vice-versa.
Am I missing something?
Thanks!
Henri
Unless you can show more detailed context, I think they are talking about
the exact same thing. A relationship is a bit more abstract; a foreign key
represents the implementation-specific way that a child row is connected to
a parent row.
http://www.aspfaq.com/
(Reverse address to reply.)
"Henri" <hmfireball@.hotmail.com> wrote in message
news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> I've bought a book about ASP.NET with SQL Server 2000.
> In one chapter, they talk about setting relations between tables, and in
> another, about setting foreign keys.
> But I can't understand the difference, as the purpose seems to be the
> same:
> navigating from a parent table to a child one, and vice-versa.
> Am I missing something?
> Thanks!
> Henri
>
>
|||Actually, the first chapter is about the DataRelation class, and
DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
The second chapter is about ForeignKeyConstraint Class
The only difference I see is that a foreign key allows to define cascade
update and delete rules.
Actually, ASP.NET permits to define many things like indexes, primary keys,
relations, etc. but I thought that this had to be done on SQL Server, not
from ASP.NET.
That's why I'm a bit lost :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le message de
news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
> Unless you can show more detailed context, I think they are talking about
> the exact same thing. A relationship is a bit more abstract; a foreign
key
> represents the implementation-specific way that a child row is connected
to
> a parent row.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
>
>
|||What you are reading about is Data Sets in ADO.Net. The
relations and constraints you define in a data set are
different and separate from relations and constraints in the
database. In ADO.Net, you can create something similar to a
database - tables (data tables), relationships (using the
DataRelation class), constraints (such as with the
ForeignKeyConstraint class). It's not the same thing as the
database you may be accessing for the data in the data sets.
For ADO.Net, you can find more information on how relations
and constraints work together, differently in .Net Framework
documentation. Check the remarks section of the definition
of the DataRelation class.
-Sue
On Fri, 11 Jun 2004 03:40:00 +0200, "Henri"
<hmfireball@.hotmail.com> wrote:

>Actually, the first chapter is about the DataRelation class, and
>DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
>The second chapter is about ForeignKeyConstraint Class
>The only difference I see is that a foreign key allows to define cascade
>update and delete rules.
>Actually, ASP.NET permits to define many things like indexes, primary keys,
>relations, etc. but I thought that this had to be done on SQL Server, not
>from ASP.NET.
>That's why I'm a bit lost :-)
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le message de
>news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
>key
>to
>
|||Hi,
Within ADO.Net, a Foreign Key Constaint is a subtype of the Data Relation. The FKC in ADO.Net behaves in a similar manner to a FKC within SQL Server, but it is a constraint on the Data Set, not the database, which can be an important consideration.
A Data Relation has additonal functionality that is not provided by the FKC, ( I think one is providing the Child Rows in the Data Set to a particular Master row.) I suggest you have a look at the class definitions and examine the different methods and pr
operties each suppiles

difference between SYSTEM_USER and USER

Excuse me, what is the difference between user and system_user and which
function should be used in audit trail table ?
Thanks.Hi

From BOL:
"If the current user is logged in to Microsoft SQL ServerT using Windows
Authentication, SYSTEM_USER returns the Windows 2000 or Windows NT 4.0 login
identification name, for example, DOMAIN\user_login_name. However, if the
current user is logged in to SQL Server using SQL Server Authentication,
SYSTEM_USER returns the SQL Server login identification name, for example,
sa for a user logged in as sa."

USER only works with SQL Security.
--
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Zlatko Mati" <zlatko.matic1@.sb.t-com.hr> wrote in message
news:d1i9uh$j37$1@.ls219.htnet.hr...
> Excuse me, what is the difference between user and system_user and which
> function should be used in audit trail table ?
> Thanks.|||I have noticed that system_user returns "sa" while user returns "dbo". What
is the difference and which to use in audit trail table?
Thanks.

"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> je napisao u poruci interesnoj
grupi:423cc1f1_3@.news.bluewin.ch...
> Hi
> From BOL:
> "If the current user is logged in to Microsoft SQL ServerT using Windows
> Authentication, SYSTEM_USER returns the Windows 2000 or Windows NT 4.0
> login
> identification name, for example, DOMAIN\user_login_name. However, if the
> current user is logged in to SQL Server using SQL Server Authentication,
> SYSTEM_USER returns the SQL Server login identification name, for example,
> sa for a user logged in as sa."
> USER only works with SQL Security.
> --
> ----------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Zlatko Mati" <zlatko.matic1@.sb.t-com.hr> wrote in message
> news:d1i9uh$j37$1@.ls219.htnet.hr...
>> Excuse me, what is the difference between user and system_user and which
>> function should be used in audit trail table ?
>> Thanks.
>>
>>
>>|||Zlatko Mati (zlatko.matic1@.sb.t-com.hr) writes:
> Excuse me, what is the difference between user and system_user and which
> function should be used in audit trail table ?

Depends on your business requirements.

SYSTEM_USER returns the server login, USER returns the database user. Often
a login maps to a database user with the same name, but at least for SQL
logins this does not have to be the case. (And I think it can happen with
Window logins as well, if you move a database from one server to
another.)

As a general answer, I would recommend SYSTEM_USER, unless there are some
special requirements that calls for something else.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Difference between supress and hide

When we write ckick on the crystal report-insert-section we will have several option to choose but what is the difference between Hide(Driil=Down ok) and Supress(No Drill-Down)??
I tried the help but I did'nt get the idea...good question!!! I've already search that but still no answer. They are the same in function, the only difference is the group tree when you view the report.|||I thought the help was quite clear - suppress if you don't want it at all, hide if you don't want it displayed initially but do want to be able to click on the item in the group tree and see the data therein.|||Ok,Igot the point:
if ur secton detail is between a group header and a group footer ,when u hide the section detail at run time if u double-click a ligne u will have the detail but if u supressed u will not have the detail,,try it u will understand better....

Difference between STDEV & STDEVP

Can somebody please compare these 2 functions for me and explain with an example?

Thank You

You can find detailed description here (the second article has an example):

http://office.microsoft.com/en-us/assistance/HP010322691033.aspx

http://www.beyondtechnology.com/tips016.shtml

Quote:

STDEV is used when the group of numbers being evaluated are only a partial sampling of the whole population. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased."
STDEVP is used when the group of numbers being evaluated is complete - it's the entire population of values. In this case, the 1 is NOT subtracted and the denominator for dividing the sum of squared deviations is simply N itself, the number of observations ( a count of items in the data set ). Technically, this is referred to as "biased." Remembering that the P in STDEVP stands for "population" may be helpful. Since the data set is not a mere sample, but constituted of ALL the actual values, this standard deviation function can return a more precise result.