sql record counts: article

Showing posts with label article. Show all posts

Thursday, March 29, 2012

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,
VaibhavVaibhav
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|||Thanks for the response.
I don't have any variable lenght fields. And yes I have also calculated
estimated size for clustered index separately. And this table does not have
any non-clustered index. To tell you something more about this comparision,
if you add the values for all the tables from field 'data' (in the recordset
returned by sp_spaceused) you get a figure which is approx. equals an MDF
file size. So what about the size occupied by other database objects. I would
want to know what this 'data' field value comrpises of...
"Aaron [SQL Server MVP]" wrote:
> 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
>
>|||The difference is of 53 MB or 37%
"Aaron [SQL Server MVP]" wrote:
> 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
>
>|||I have no idea what you're talking about here. But if you want an accurate
judgement of a table size you can do something like:
SELECT SUM(DATALENGTH(col1)) + SUM(DATALENGTH(col2)) + ... FROM table
Then add the overhead associated with nullable columns, and the overhead
associated with the clustered index.
The *estimate* in Books Online is just that, an estimate. It is meant to
give a rough guideline for how much space your table might occupy, it is not
meant to serve as a means to determine the exact number of bytes in an
existing table. So use it for what it is...
--
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:98CF4A5C-5C3B-44DE-BD73-9E97D7F53A3D@.microsoft.com...
> Thanks for the response.
> I don't have any variable lenght fields. And yes I have also calculated
> estimated size for clustered index separately. And this table does not
have
> any non-clustered index. To tell you something more about this
comparision,
> if you add the values for all the tables from field 'data' (in the
recordset
> returned by sp_spaceused) you get a figure which is approx. equals an MDF
> file size. So what about the size occupied by other database objects. I
would
> want to know what this 'data' field value comrpises of...
> "Aaron [SQL Server MVP]" wrote:
> > 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
> >
> >
> >|||Please find the numbers used in the estimation process -
Number of rows 2245536
Number of columns 11
Sum of bytes in all fixed-length columns 33
Number of variable-length columns 0
Maximum size of all variable-length columns 0
Null Bitmap 4
Total size of variable-length columns 0
Total row size 41
Number of rows per page 189
Fill Factor 100
Number of free rows per page 0
Number of pages 11882
Table size (MB) 92.83
And that given by sp_spaceused -
rows 2245536
reserved 150152 KB
data 149200 KB
index_size 896 KB
unused 56 KB
"Dejan Sarka" wrote:
> > 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
>
>|||Hi,
After following method given in the Books Online article, I got following
results for a table -
Number of rows 2245536
Number of columns 11
Sum of bytes in all fixed-length columns 33
Number of variable-length columns 0
Maximum size of all variable-length columns 0
Null Bitmap 4
Total size of variable-length columns 0
Total row size 41
Number of rows per page 189
Fill Factor 100
Number of free rows per page 0
Number of pages 11882
Table size (MB) 92.83
And the result given by sp_spaceused is as follows -
rows 2245536
reserved 150152 KB
data 149200 KB
index_size 896 KB
unused 56 KB
After comparing these two resulrs one can see the difference of 57 MB. Could
you please tell me why this much difference is observed. Do I need to
consider any other factors while calculating estimated size for a table.
Again the estimated size of clustered index on this tables is 0.45 MB. Also
can you please tell me how to estimate the size of a database. As the same
knd of difference would be shown in the estimated and actual figures if one
calculates estimated size for a database using Books Online metod?
"Aaron [SQL Server MVP]" wrote:
> I have no idea what you're talking about here. But if you want an accurate
> judgement of a table size you can do something like:
> SELECT SUM(DATALENGTH(col1)) + SUM(DATALENGTH(col2)) + ... FROM table
> Then add the overhead associated with nullable columns, and the overhead
> associated with the clustered index.
> The *estimate* in Books Online is just that, an estimate. It is meant to
> give a rough guideline for how much space your table might occupy, it is not
> meant to serve as a means to determine the exact number of bytes in an
> existing table. So use it for what it is...
> --
> 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:98CF4A5C-5C3B-44DE-BD73-9E97D7F53A3D@.microsoft.com...
> > Thanks for the response.
> >
> > I don't have any variable lenght fields. And yes I have also calculated
> > estimated size for clustered index separately. And this table does not
> have
> > any non-clustered index. To tell you something more about this
> comparision,
> > if you add the values for all the tables from field 'data' (in the
> recordset
> > returned by sp_spaceused) you get a figure which is approx. equals an MDF
> > file size. So what about the size occupied by other database objects. I
> would
> > want to know what this 'data' field value comrpises of...
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > 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
> > >
> > >
> > >
>
>|||> After comparing these two resulrs one can see the difference of 57 MB.
Could
> you please tell me why this much difference is observed.
All those starts are fabulous but I still have no idea what kind of table we
are talking about here. Can you please generate the CREATE TABLE statement
(see my signature for details) and make sure to include all keys, indexes
and constraints.
And please, please, please remember (once again) that the method in books
online is a *rough estimate*... 57 MB over more than 2 million rows is
really not that alarming to me, and I'm amazed this is turning into a
two-day discussion.
--
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.|||Hi,
Thanks for the quick response and I apologize to test your patience. I
assumed that the estimated figure that I would get would be the closest one.
Are there any other methods with wich one can get more closer estimates? If
yes than can you give pointers to those. If not then can I have some pointers
to resources which would expain as to why such difference is seen between the
estimated figures and actual figures?
PS: I would be able to send you the DDL and sample data tomorrow as I would
need some more time to generate it.
Thanks once again...
Regards,
Vaibhav
"Aaron [SQL Server MVP]" wrote:
> > After comparing these two resulrs one can see the difference of 57 MB.
> Could
> > you please tell me why this much difference is observed.
> All those starts are fabulous but I still have no idea what kind of table we
> are talking about here. Can you please generate the CREATE TABLE statement
> (see my signature for details) and make sure to include all keys, indexes
> and constraints.
> And please, please, please remember (once again) that the method in books
> online is a *rough estimate*... 57 MB over more than 2 million rows is
> really not that alarming to me, and I'm amazed this is turning into a
> two-day discussion.
> --
> 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.
>
>|||The methods you're already using have worked fine for me. Again, 57 MB is
really not that big of a difference to me. When we are buying and
configuring disks we round to the terabyte, not the MB...
As for generating DDL, if you look at http://www.aspfaq.com/5006 you will
see that all you have to do is right-click the table in Enterprise Manager
and hit "All Tasks > Generate SQL Script"... you shouldn't have to write it
from scratch and if it takes more than two minutes there's something
seriously wrong.
--
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:D16F7B3C-0E86-4C41-B6BD-F90031E36A6C@.microsoft.com...
> Hi,
> Thanks for the quick response and I apologize to test your patience. I
> assumed that the estimated figure that I would get would be the closest
one.
> Are there any other methods with wich one can get more closer estimates?
If
> yes than can you give pointers to those. If not then can I have some
pointers
> to resources which would expain as to why such difference is seen between
the
> estimated figures and actual figures?
> PS: I would be able to send you the DDL and sample data tomorrow as I
would
> need some more time to generate it.
> Thanks once again...
> Regards,
> Vaibhav|||Yes Aron I know that it would take less than 2 minutes to generate a script
of a table . Actually yesterday when I read your last reply it was alredy the
time to leave from the office, hence I could not send you the required
details. It seems I am disturbing you too much.
Thanks a lot mate for all the help provided till now.
Bye for now...
"Aaron [SQL Server MVP]" wrote:
> The methods you're already using have worked fine for me. Again, 57 MB is
> really not that big of a difference to me. When we are buying and
> configuring disks we round to the terabyte, not the MB...
> As for generating DDL, if you look at http://www.aspfaq.com/5006 you will
> see that all you have to do is right-click the table in Enterprise Manager
> and hit "All Tasks > Generate SQL Script"... you shouldn't have to write it
> from scratch and if it takes more than two minutes there's something
> seriously wrong.
> --
> 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:D16F7B3C-0E86-4C41-B6BD-F90031E36A6C@.microsoft.com...
> > Hi,
> >
> > Thanks for the quick response and I apologize to test your patience. I
> > assumed that the estimated figure that I would get would be the closest
> one.
> > Are there any other methods with wich one can get more closer estimates?
> If
> > yes than can you give pointers to those. If not then can I have some
> pointers
> > to resources which would expain as to why such difference is seen between
> the
> > estimated figures and actual figures?
> >
> > PS: I would be able to send you the DDL and sample data tomorrow as I
> would
> > need some more time to generate it.
> >
> > Thanks once again...
> >
> > Regards,
> > Vaibhav
>
>

Difference between the estimated size and size given by sp_spaceus

difference between sql2k and sql2005

is there a good article or book or faq which highlights the
differences between sql2k and sql2005?
tia,
dkYes. 2005 Books Online details the differences pretty explicitly. Be
prepared to spend several hours, because the information is several hundred
pages deep.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"dk" <dk@.discussions.microsoft.com> wrote in message
news:215A1B80-81D1-4788-96A2-A63153EEB5C1@.microsoft.com...
> is there a good article or book or faq which highlights the
> differences between sql2k and sql2005?
> tia,
> dk|||There is no simple answer as 2005 is a major upgrade and there are lots of
new features. One of the most interesting features involves CLR integration,
which lets you can code your stored procedures, functions, and triggers in
the .NET Framework language of your choice. Microsoft Visual Basic .NET and
the C#. Another major new feature is Database Mirroring and a complete
rewrite of DTS. DTS is now called SSIS (SQL Server Integration Services )
Lots of new features in reporting services and Business Intelligence. See
www.sqlservercentral for a bunch of great articles on 2005.
For the Microsoft perspective see
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
"dk" wrote:
> is there a good article or book or faq which highlights the
> differences between sql2k and sql2005?
> tia,
> dk|||This is a multi-part message in MIME format.
--020109090507080508080807
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
In addition, Michael Otey (the Technical Director for SQLMag) wrote a
book about a year & a half ago (so it will be based on a Beta version I
think) published by Osborne called Microsoft SQL Server 2005 New
Features
<http://www.amazon.com/gp/product/0072227761/sr=1-1/qid=1138921308/ref=sr_1_1/102-3661316-4174542?%5Fencoding=UTF8>.
I haven't read it myself but it sounds like it might be right up your alley.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Michael Hotek wrote:
>Yes. 2005 Books Online details the differences pretty explicitly. Be
>prepared to spend several hours, because the information is several hundred
>pages deep.
>
>
--020109090507080508080807
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>In addition, Michael Otey (the Technical Director for </tt><tt>SQLMag</tt><tt>)
wrote a book about a year & a half ago (so it will be based on a
Beta version I think) published by Osborne called <a
href="http://links.10026.com/?link=Microsoft">http://www.amazon.com/gp/product/0072227761/sr=1-1/qid=1138921308/ref=sr_1_1/102-3661316-4174542?%5Fencoding=UTF8">Microsoft
SQL Server 2005 New Features</a>. I haven't read it myself but it
sounds like it might be right up your alley.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Michael Hotek wrote:
<blockquote cite="midesC9ngBKGHA.3728@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">Yes. 2005 Books Online details the differences pretty explicitly. Be
prepared to spend several hours, because the information is several hundred
pages deep.
</pre>
</blockquote>
</body>
</html>
--020109090507080508080807--

difference between sql2k and sql2005

Wednesday, March 21, 2012

Diff betw Clustered and Non-Clustered Index and their Applicat

Hi,
Thanks for the reply. One of the article
(http://www.sql-server-performance.c...red_indexes.asp) was the exact
one that confuses me.
It mentions...
"As a rule of thumb, every table should have a clustered index. Generally,
but not always, the clustered index should be on a column that monotonically
increases--such as an identity column, or some other column where the value
is increasing--and is unique. In many cases, the primary key is the ideal
column for a clustered index."
Which in my understanding basically says... Primary key should use clustered
index.
But in another paragraph...
"The primary key you select for your table should not always be a clustered
index. If you create the primary key and don't specify otherwise, this is th
e
default. Only make the primary key a clustered index if you will be regularl
y
performing range queries on the primary key or need your results sorted by
the primary key. [6.5, 7.0, 2000] Updated 3-5-2004"
They sound contradictory to me.
And on (http://www.sql-server-performance.c...red_indexes.asp), it
states
"If a column in a table is not at least 95% unique, then most likely the
query optimizer will not use a non-clustered index based on that column.
Because of this, don't add non-clustered indexes to columns that aren't at
least 95% unique. For example, a column with "yes" or "no" as the data won't
be at least 95% unique. [6.5, 7.0, 2000] Updated 3-4-2004"
Which sounds more logical to me.
1) Do you have general rule of thumb on this matter:
- clustered or non-clustered index for primary key?
- clustered or non-clustered index for foreign key?
2) Also does my understanding of the functions of clustered and
non-clustered index correct?
Could u please kindly advise. TQ again.
"Uri Dimant" wrote:

> Hi
> This is very,very big and important issue.
> Start with
> http://www.sql-server-performance.c...red_indexes.asp
> http://www.sql-server-performance.c...red_indexes.asp
> http://www.sql-server-performance.c...ing_indexes.asp
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:BABFB6D4-09F7-482B-89F9-DF10DFCF1242@.microsoft.com...
> the
> to
> clustered
> So
> column.
>
>1) Do you have general rule of thumb on this matter:
- clustered or non-clustered index for primary key?
- clustered or non-clustered index for foreign key?
It depends on your tables, data, and business requirements. In my opinion CI
is more efficient on a range queries as BETWEEN and greater,lower that
returns a set of data.NCI is more effcient to retrieve a few rows or single
row from the table.
You have to choose what column to be defined as primary key and on what
column create CI.
Yes, it is considered a good practice to create a NCI on foreign key to
improve JOIN performance.
2) Also does my understanding of the functions of clustered and
non-clustered index correct?

> Which in my understanding basically says... Primary key should use
clustered
> index.
Up to you.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:05A5C898-E9AD-4B0F-9F43-E0E0BA75A9EA@.microsoft.com...
> Hi,
> Thanks for the reply. One of the article
> (http://www.sql-server-performance.c...red_indexes.asp) was the
exact
> one that confuses me.
> It mentions...
> "As a rule of thumb, every table should have a clustered index. Generally,
> but not always, the clustered index should be on a column that
monotonically
> increases--such as an identity column, or some other column where the
value
> is increasing--and is unique. In many cases, the primary key is the ideal
> column for a clustered index."
> Which in my understanding basically says... Primary key should use
clustered
> index.
> But in another paragraph...
> "The primary key you select for your table should not always be a
clustered
> index. If you create the primary key and don't specify otherwise, this is
the
> default. Only make the primary key a clustered index if you will be
regularly
> performing range queries on the primary key or need your results sorted by
> the primary key. [6.5, 7.0, 2000] Updated 3-5-2004"
> They sound contradictory to me.
> And on (http://www.sql-server-performance.c...red_indexes.asp),
it
> states
> "If a column in a table is not at least 95% unique, then most likely the
> query optimizer will not use a non-clustered index based on that column.
> Because of this, don't add non-clustered indexes to columns that aren't at
> least 95% unique. For example, a column with "yes" or "no" as the data
won't
> be at least 95% unique. [6.5, 7.0, 2000] Updated 3-4-2004"
> Which sounds more logical to me.
> 1) Do you have general rule of thumb on this matter:
> - clustered or non-clustered index for primary key?
> - clustered or non-clustered index for foreign key?
> 2) Also does my understanding of the functions of clustered and
> non-clustered index correct?
> Could u please kindly advise. TQ again.
> "Uri Dimant" wrote:
>
a
However
or
value
index
directly
I
clustered
index.
key,
child
clustered|||Watch out for rule-of-thumbs!
You have to weigh in both retrieval and inserting of data. The part about cr
eating cl ix on a column
which is monotonically increasing is all about inserting data. This is so th
at all data goes to the
end of the table and you don't get page split in your data (remember that cl
ix = data). But it
doesn't address how you search data. Fine, cluster on your identify PK, but
perhaps it is more
important to support range queries on a datetime or lastname column? Also, r
emember that page splits
can be handled using index defragmentation, and perhaps the table isn't that
insert intensitive in
the first place?
I don't know where they get the 95% unique number. It is all about selectivi
ty. Understand how SQL
Server can use a clustered index and also non-clustered indexes (navigate th
e index then bookmark
lookups). Also remember that the optimizer is cost based.
Based on above, you should be able to make informed decisions of what to clu
ster on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:05A5C898-E9AD-4B0F-9F43-E0E0BA75A9EA@.microsoft.com...
> Hi,
> Thanks for the reply. One of the article
> (http://www.sql-server-performance.c...red_indexes.asp) was the exac
t
> one that confuses me.
> It mentions...
> "As a rule of thumb, every table should have a clustered index. Generally,
> but not always, the clustered index should be on a column that monotonical
ly
> increases--such as an identity column, or some other column where the valu
e
> is increasing--and is unique. In many cases, the primary key is the ideal
> column for a clustered index."
> Which in my understanding basically says... Primary key should use cluster
ed
> index.
> But in another paragraph...
> "The primary key you select for your table should not always be a clustere
d
> index. If you create the primary key and don't specify otherwise, this is
the
> default. Only make the primary key a clustered index if you will be regula
rly
> performing range queries on the primary key or need your results sorted by
> the primary key. [6.5, 7.0, 2000] Updated 3-5-2004"
> They sound contradictory to me.
> And on (http://www.sql-server-performance.c...red_indexes.asp), i
t
> states
> "If a column in a table is not at least 95% unique, then most likely the
> query optimizer will not use a non-clustered index based on that column.
> Because of this, don't add non-clustered indexes to columns that aren't at
> least 95% unique. For example, a column with "yes" or "no" as the data won
't
> be at least 95% unique. [6.5, 7.0, 2000] Updated 3-4-2004"
> Which sounds more logical to me.
> 1) Do you have general rule of thumb on this matter:
> - clustered or non-clustered index for primary key?
> - clustered or non-clustered index for foreign key?
> 2) Also does my understanding of the functions of clustered and
> non-clustered index correct?
> Could u please kindly advise. TQ again.
> "Uri Dimant" wrote:
>|||On Wed, 20 Jul 2005 11:57:06 +0300, "Uri Dimant" <urid@.iscar.co.il> wrote:
in <uqvI#jQjFHA.4000@.TK2MSFTNGP12.phx.gbl>

>Yes, it is considered a good practice to create a NCI on foreign key to
>improve JOIN performance.
Is it better, worse, or no difference if the foreign key is also a CI on its
own
PK?
Consider the following:
CREATE TABLE ShowTime.dbo.Countries (
CountryID TINYINT NOT NULL CONSTRAINT PK_Countries PRIMARY KEY,
CountryCode CHAR(3) NOT NULL,
Country VARCHAR(45) NOT NULL)
CREATE TABLE ShowTime.dbo.States (
StateID TINYINT NOT NULL CONSTRAINT PK_States PRIMARY KEY,
CountryID TINYINT NOT NULL CONSTRAINT FK_States_Country FOREIGN KEY
(CountryID) REFERENCES ShowTime.dbo.Countries(CountryID),
State CHAR(2) NOT NULL)
I'm interpreting your remark to mean in the absence of an index on the forei
gn
key it would be considered to be good practice to create a NCI?
Stefan Berglund|||Tibor Karaszi wrote:
> Watch out for rule-of-thumbs!
> You have to weigh in both retrieval and inserting of data. The part
> about creating cl ix on a column which is monotonically increasing is
> all about inserting data. This is so that all data goes to the end of
> the table and you don't get page split in your data (remember that cl
> ix = data). But it doesn't address how you search data. Fine, cluster
> on your identify PK, but perhaps it is more important to support
> range queries on a datetime or lastname column? Also, remember that
> page splits can be handled using index defragmentation, and perhaps
> the table isn't that insert intensitive in the first place?
> I don't know where they get the 95% unique number. It is all about
> selectivity. Understand how SQL Server can use a clustered index and
> also non-clustered indexes (navigate the index then bookmark
> lookups). Also remember that the optimizer is cost based.
> Based on above, you should be able to make informed decisions of what
> to cluster on.
I would add that clustered index keys are stored internally by SQL
Server as unique values and the clustered index key values are the
pointers in all non-clustered indexes. This has a couple implications:
1- Your clustered index key should be small if your table contains
non-clustered indexes. If you cluster on a large key (say 100 bytes),
then you've added 100 bytes to each key value in every non-clustered
index.
2- If you cluster on a non-unique column, SQL Server will make it
unique internally. While this is transparent to database users, it
further increases the size of the index and all non-clustered indexes.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"Tibor Karaszi" wrote:

> Watch out for rule-of-thumbs!
>
1) TQ for ur reply.
Yes I understand that is no one-size-fits-all approach to this. Which is why
I am trying to understand from a conceptual point of view, difference betwee
n
clustered and non-clustered index and their application, before I can make
informed decisions on how they fit into my application.
However I don't believe anyone has given a clear answer on whether my
understanding on them is correct. I.e. essentially, a clustered index is a
sorted group of rows under a common index value (1 index value = M rows).
Whereas a non-clustered index is, basically 1 index value = 1 row. Could u
pls kindly advise, essentially, is my understanding correct?
2) My database has plenty of parent-and-child tables, e.g. Invoice and
Invoice Item. So the child table [InvoiceItem] has a foreign key
[InvoiceCode] referring back to the primary key [Code] of [Invoice] table
(1-M relationship).
So when query the database to populate my biz reports, a very common
requirement is to join the parent and child tables along their keys.
Also in a typical requirement, I will need join the results from the
parent-child join, with other tables, such as [Item] which contains details
on the [InvoiceItem], E.g. [Item].[Code] = [InvoiceItem].[ItemCode].
Therefore there is a lot of 1-M joins.
So based on my understanding of clustered and non-clustered indexes and how
SQL Server uses them to search for data, at least in the case of parent-chil
d
relationship, it makes more sense to set a clustered index at child's foreig
n
key column, i.e. [InvoiceItem].[InvoiceCode], and non-clustered index at the
parent's primary key column, i.e. [Invoice].[Code]. Do u agree?

> You have to weigh in both retrieval and inserting of data. The part about
creating cl ix on a column
> which is monotonically increasing is all about inserting data. This is so
that all data goes to the
> end of the table and you don't get page split in your data (remember that
cl ix = data). But it
> doesn't address how you search data. Fine, cluster on your identify PK, bu
t perhaps it is more
> important to support range queries on a datetime or lastname column?
3) Based on ur explaination, the majority of range queries that I do is
along the date fields of various transaction tables, e.g. finding the
invoices for this mth, last mth, last year, 10 years ago, etc. So it is also
POSSIBLE to consider [TransactionDate] as a candidate for cluster index? Do u
agree?

> I don't know where they get the 95% unique number. It is all about selecti
vity. Understand how SQL
> Server can use a clustered index and also non-clustered indexes (navigate
the index then bookmark
> lookups). Also remember that the optimizer is cost based.
3) My understanding of selectivity means how fast data can be uniquely
identified. So it implies a preference towards a field with 95% unqiue
values. Hence the 95% unique number thingy. Is that true?

> Based on above, you should be able to make informed decisions of what to c
luster on.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:05A5C898-E9AD-4B0F-9F43-E0E0BA75A9EA@.microsoft.com...
>|||Inline...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:D5D39394-6183-46E5-B6FA-40B5957CB882@.microsoft.com...
> "Tibor Karaszi" wrote:
>
> 1) TQ for ur reply.
You're welcome :-)

> Yes I understand that is no one-size-fits-all approach to this. Which is w
hy
> I am trying to understand from a conceptual point of view, difference betw
een
> clustered and non-clustered index and their application, before I can make
> informed decisions on how they fit into my application.
> However I don't believe anyone has given a clear answer on whether my
> understanding on them is correct. I.e. essentially, a clustered index is a
> sorted group of rows under a common index value (1 index value = M rows).
> Whereas a non-clustered index is, basically 1 index value = 1 row. Could u
> pls kindly advise, essentially, is my understanding correct?
SQL Server does not store the value once and the number of occurances as a n
umber, if that is what
you mean. Bot cl and nc indexes are b-trees (there are good drawings on this
in Books Online).
For a cl ix, the leaf level *is the data*. I.e., it is sorted (the linked li
st) according to the
index key and the pages contains all the columns.
An nc ix leaf level only contains the index key and a "row locator"/bookmark
. This is used to
navigate to the corresponding page and row number on that pag.

>
> 2) My database has plenty of parent-and-child tables, e.g. Invoice and
> Invoice Item. So the child table [InvoiceItem] has a foreign key
> [InvoiceCode] referring back to the primary key [Code] of [Invoice] table
> (1-M relationship).
> So when query the database to populate my biz reports, a very common
> requirement is to join the parent and child tables along their keys.
> Also in a typical requirement, I will need join the results from the
> parent-child join, with other tables, such as [Item] which contains details
> on the [InvoiceItem], E.g. [Item].[Code] = [InvoiceItem].[ItemCode].
> Therefore there is a lot of 1-M joins.
> So based on my understanding of clustered and non-clustered indexes and ho
w
> SQL Server uses them to search for data, at least in the case of parent-ch
ild
> relationship, it makes more sense to set a clustered index at child's fore
ign
> key column, i.e. [InvoiceItem].[InvoiceCode], and non-clustered index at the
> parent's primary key column, i.e. [Invoice].[Code]. Do u agree?
Yes, cl on fk can often ba a good candidate. You still have to weigh in othe
r candidates as well as
consider fragmentation aspects.

>
> 3) Based on ur explaination, the majority of range queries that I do is
> along the date fields of various transaction tables, e.g. finding the
> invoices for this mth, last mth, last year, 10 years ago, etc. So it is al
so
> POSSIBLE to consider [TransactionDate] as a candidate for cluster index? Do u
> agree?
Yes.

>
> 3) My understanding of selectivity means how fast data can be uniquely
> identified. So it implies a preference towards a field with 95% unqiue
> values. Hence the 95% unique number thingy. Is that true?
Selectivity is quite simply for a certain table and a certain restriction (c
ondition in the WHERE
clause), how many percent of the total number of rows. 1000 rows in table, c
ondition returns 100
rows = 10% selectivity.
Density is another term we use. Here we *do not* involve a query or search c
ondition. We only look
at the data. Say you have a column named gender. Say we only have two possib
le values (male and
female). Say that both are represented in the table. We now have a density o
f 1/2 = 0.5. Or say that
we do business with 20 other companies. These 10 companies are all represent
ed in the table. For the
"company column", we have a density of 0.05 (1/20). When we calculatye densi
ty, we do not care about
the number of rows in the table.
>

Friday, February 17, 2012

Dev Edition to Retail.

Want to know if there are any pitfalls for upgrading (bad choice of words)
the Dev Edition of SQL 2000 to the Retail Version. Found a KB article about
upgrading the 120 Day Eval to the Full retail but nothing about the dev
edition to retail. Thank you and have a good day.
-JeffThere should be no problems...!
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Jeff Spicuzza" <jeff.spicuzza@.spectrumsciences.com> wrote in message
news:#j2Z7IrWDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Want to know if there are any pitfalls for upgrading (bad choice of words)
> the Dev Edition of SQL 2000 to the Retail Version. Found a KB article
about
> upgrading the 120 Day Eval to the Full retail but nothing about the dev
> edition to retail. Thank you and have a good day.
> -Jeff
>|||If you are worried(I personally do not trust the upgrades), try just
detaching the DBs and reattaching them to the server or Backup and Restore.
Just backup/detach DBs uninstall SQL and install retail version.
"Jeff Spicuzza" <jeff.spicuzza@.spectrumsciences.com> wrote in message
news:#j2Z7IrWDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Want to know if there are any pitfalls for upgrading (bad choice of words)
> the Dev Edition of SQL 2000 to the Retail Version. Found a KB article
about
> upgrading the 120 Day Eval to the Full retail but nothing about the dev
> edition to retail. Thank you and have a good day.
> -Jeff
>

Thursday, March 29, 2012

Difference between the estimated size and size given by sp_spaceus

Difference between the estimated size and size given by sp_spaceus

Difference between the estimated size and size given by sp_spaceus

difference between sql2k and sql2005

difference between sql2k and sql2005

difference between sql2k and sql2005

Wednesday, March 21, 2012

Diff betw Clustered and Non-Clustered Index and their Applicat

Friday, February 17, 2012

Dev Edition to Retail.

sql record counts

Blog Archive

About Me