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
>
>
Showing posts with label online. Show all posts
Showing posts with label online. 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 "Estimatin
g
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,
> Vaibhavsql
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 "Estimatin
g
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,
> Vaibhavsql
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
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
Sunday, March 25, 2012
Difference between creating a Unique Constraint and a Unique Index
Hi,
I've looked at the online documentation and encountered this piece of
text
"If uniqueness must be enforced to ensure data integrity, create a
UNIQUE or PRIMARY KEY constraint on the column rather than a unique
index"
I'm baffled; I understand a unique contraint, and personally do not
care how it is physically enforced (using an index to speed up the
process) as long as it is enforced. However what is the deal with
unique indexes? The piece of documentation above seems to suggest that
a unique index will not enforce uniqueness.
The reason I ask is that I am working against a legacy system where
unique indexes are used for each table. Each table, besides a PK, has
a column that stores a GUID. Personally I would use a Unique
Contraint, but they have used a unique index on this column. What
would be the advantages and disadvantages of using a unique index vs a
unique constraint.'
regards
Paul SjoerdsmaYes, a unique index enforces uniqueness. A unique index is created in the
background when you specify a PK or Unique constraint.
The difference is more semantic, but also tactical... Placing the constraint
indicates a design requirement. When you create the index directly, how can
you know IF the index was created for performance, or if the index is
intended to enforce some design constraint... The fact is that you can
not... So use PK and Uniques to enforce design requirements...Indexes are
created for 2 main reasons, to enforce integrity and speed up queries.The
indexes used to speed up queries( or other statements) may need to change
over time as business priorities change, but we would still need to maintain
the indexes created to enforce data integrity.
Indexes created as a by-product of PK or unique constraints can NOT be
dropped using DROP index, so you are protected from inadvertently dropping
an index which is used for data integrity. FOr this you must use alter table
drop constraint, alter table add constraint... Any index that goes away
using DROP index, therefore, would have been created for performance, and is
a candidate for re-assessment as needs change
Additionally FKs must be placed on columns with PKs or Unique
constraints.(from BOL). HOwever the actual code allows you to put an FK on
any column which has a unique index ( although I beleive this should NOT be
allowed)..
Hope this helps/
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
"Paul Sjoerdsma" <paul@.pasoftware.nl> wrote in message
news:2j8ko0pdll18amito60gcqne5g58en6otj@.
4ax.com...
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||In addition to the other respondents reply, implicit in your question is
really two seperate questions:
What is the difference between the use of PK and Unique Constraints and
Unique Indexes? The was answered by the other respondent. Except, so you
know, there is no such thing as a PK index, only a constraint, which is also
enforced through a Unique Index.
Second, you made mention of PK and GUID, which I am assuming you implied
what is typically used some sort of INT IDENTITY. Both of these are unique
by design, one locally to the table, the other across all tables, databases,
and time. However, these are mearly mechanics. They do not garauntee
uniqueness of the data, which is the sole purpose of KEYS, Primary or
Alternate. Moreover, PKs and Unique Constraints are not limited to IDENTITY
nor GUID. For example:
EmployeeID LastName FirstName
1 Smith John
2 Jones Tom
3 Marshall Eric
4 Smith John
Although EmployeeID is unique, because it is IDENTITY and is so by
construction, for this table, the DATA is NOT unique: John Smith is repeated
.
Therefore, this table is NOT even a table by relational design--it is not in
1NF (First Normal Form).
The real key for this entity is the Logical Name attribute, which is
composite upon FirstName and LastName. Since that is pretty much all of the
data, it is also the Primary Key candidate. However, if this table were
JOINed to some other not only would you have to populate both attributes to
that table, you would have to populate string types (data storage
inefficient) and would have to include both attributes in any Foreign Key or
JOIN declaration, also highly inefficient.
So, oftentimes, one creates the IDENTITY or GUID attributes to simplify
these requirements. What happens, TOO OFTEN, is the PK is defined on
EmployeeID and no Unique Constraint is ever defined on the Candidate Key
combination of First and Last Names, which is the true key of this table, bu
t
should be and MUST be for this implementation to be in at least 1NF.
Moreover, while I am on it, I see far too often that such an IDENTITY or
GUID PK is also defined as the Clustered Index, if there is one at all. Tha
t
is a lousy choice. Not only should every table have a KEY (Primary or
Otherwise)--this is a logical or design requirement--every table should also
have a WELL CHOSEN Clustered Index or Key defined--this is a physical
recommendation.
A well chosen Clustered Index or Key will affect the construction of
statistics and other indexes on the table. It should be nearly unique, if
not a unique key. It should not change often. And, it should be considered
for RANGE type query requirements. In our example, not only do we need to
create a UNIQUE CONSTRAINT for the Name attribute, the Last Name or the Last
Name + First Name combination should be considered for the Clustered Index.
I hope I have not gone to Academic, or Theoretical--some might say, Cultish.
Nevertheless, these point go far too unnoticed by the mass of database
designers and users and needs to be said more often.
Sincerely,
Anthony Thomas
"Paul Sjoerdsma" wrote:
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||Anthony and Wayne,
Thanks for your answers. Although I would consider myself to be
experienced in database design (with the necessary theoretical
background) I still do not grok the full idea behind the concept of a
unique index; even after your elaborate explanations.
To me it seems to have raised it's head out of some murky swamp. There
are nicer places to visit, so a Unique Key constraint is what it will
be.
However if someone could give me a clear example where I would use a
unique index instead of a unique key I would be very happy,
thanks,
regards
Paul
On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
<AnthonyThomas@.discussions.microsoft.com> wrote:
>In addition to the other respondents reply, implicit in your question is
>really two seperate questions:
>|||> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
Very good question. IMO, there aren't any (*). Some say that you should use
an index directly when
the uniqueness isn't a property of the data model per se, but IMO, I have a
hard time finding such
an example. If I want to enforce uniqueness, I surely want to expose this at
the logical level
(constraint) as well, right?
(*) Here's an exception. There are at least one attribute you can define for
a unique index which
isn't available through a unique constraint. If this index attribute is impo
rtant for you, define an
index directly and not though a unique constraint. In SQL Server 2005, this
will be available
through a constraint...
Be prepared that different people have different viewpoints about this. It i
s a bit like the age-old
discussion "natural keys vs. surrogate keys".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Sjoerdsma" <paul.nospam.@.pasoftware.nl> wrote in message
news:h9dmo0dce287fe5gdjoo0ro4mvgqrsggb5@.
4ax.com...
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
>
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
>
> thanks,
> regards
> Paul
>
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
>
>|||I have two real life examples where I would use a unique index as
opposed to a unique constraint.
1. When you have a relation table that contains the keys of two
referenced tables. For example the table StudentCourses which lists the
courses for each student. It might be defined as:
CREATE TABLE StudentCourses
(StudentID int not null REFERENCES Students
,CourseID int not null REFERENCES Courses
,CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID)
)
In this case there already is a unique constraint on (StudentID,
CourseID). For performance purposes I would add a unique index on
(CourseID, StudentID). This would benefit query like:
SELECT Course, COUNT(*) AS StudentCount
FROM Courses C1
INNER JOIN StudentCourses CS1
ON CS1.CourseID = C1.CourseID
GROUP BY Course
2. When a (compound) covering index is needed, and one of its columns is
by itself unique. When I have the choice between a nonunique and a
unique index, I choose a unique index. But the columns of the covering
index do not constitute a new or different "unique relation", so a
unique constraint would be inappropriate.
The rule I use for unique constraints is that you should be able to
remove all user created unique indexes, and the data model would still
be consistent and enforced.
Hope this helps,
Gert-Jan
Paul Sjoerdsma wrote:[vbcol=seagreen]
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
> thanks,
> regards
> Paul
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
>
I've looked at the online documentation and encountered this piece of
text
"If uniqueness must be enforced to ensure data integrity, create a
UNIQUE or PRIMARY KEY constraint on the column rather than a unique
index"
I'm baffled; I understand a unique contraint, and personally do not
care how it is physically enforced (using an index to speed up the
process) as long as it is enforced. However what is the deal with
unique indexes? The piece of documentation above seems to suggest that
a unique index will not enforce uniqueness.
The reason I ask is that I am working against a legacy system where
unique indexes are used for each table. Each table, besides a PK, has
a column that stores a GUID. Personally I would use a Unique
Contraint, but they have used a unique index on this column. What
would be the advantages and disadvantages of using a unique index vs a
unique constraint.'
regards
Paul SjoerdsmaYes, a unique index enforces uniqueness. A unique index is created in the
background when you specify a PK or Unique constraint.
The difference is more semantic, but also tactical... Placing the constraint
indicates a design requirement. When you create the index directly, how can
you know IF the index was created for performance, or if the index is
intended to enforce some design constraint... The fact is that you can
not... So use PK and Uniques to enforce design requirements...Indexes are
created for 2 main reasons, to enforce integrity and speed up queries.The
indexes used to speed up queries( or other statements) may need to change
over time as business priorities change, but we would still need to maintain
the indexes created to enforce data integrity.
Indexes created as a by-product of PK or unique constraints can NOT be
dropped using DROP index, so you are protected from inadvertently dropping
an index which is used for data integrity. FOr this you must use alter table
drop constraint, alter table add constraint... Any index that goes away
using DROP index, therefore, would have been created for performance, and is
a candidate for re-assessment as needs change
Additionally FKs must be placed on columns with PKs or Unique
constraints.(from BOL). HOwever the actual code allows you to put an FK on
any column which has a unique index ( although I beleive this should NOT be
allowed)..
Hope this helps/
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
"Paul Sjoerdsma" <paul@.pasoftware.nl> wrote in message
news:2j8ko0pdll18amito60gcqne5g58en6otj@.
4ax.com...
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||In addition to the other respondents reply, implicit in your question is
really two seperate questions:
What is the difference between the use of PK and Unique Constraints and
Unique Indexes? The was answered by the other respondent. Except, so you
know, there is no such thing as a PK index, only a constraint, which is also
enforced through a Unique Index.
Second, you made mention of PK and GUID, which I am assuming you implied
what is typically used some sort of INT IDENTITY. Both of these are unique
by design, one locally to the table, the other across all tables, databases,
and time. However, these are mearly mechanics. They do not garauntee
uniqueness of the data, which is the sole purpose of KEYS, Primary or
Alternate. Moreover, PKs and Unique Constraints are not limited to IDENTITY
nor GUID. For example:
EmployeeID LastName FirstName
1 Smith John
2 Jones Tom
3 Marshall Eric
4 Smith John
Although EmployeeID is unique, because it is IDENTITY and is so by
construction, for this table, the DATA is NOT unique: John Smith is repeated
.
Therefore, this table is NOT even a table by relational design--it is not in
1NF (First Normal Form).
The real key for this entity is the Logical Name attribute, which is
composite upon FirstName and LastName. Since that is pretty much all of the
data, it is also the Primary Key candidate. However, if this table were
JOINed to some other not only would you have to populate both attributes to
that table, you would have to populate string types (data storage
inefficient) and would have to include both attributes in any Foreign Key or
JOIN declaration, also highly inefficient.
So, oftentimes, one creates the IDENTITY or GUID attributes to simplify
these requirements. What happens, TOO OFTEN, is the PK is defined on
EmployeeID and no Unique Constraint is ever defined on the Candidate Key
combination of First and Last Names, which is the true key of this table, bu
t
should be and MUST be for this implementation to be in at least 1NF.
Moreover, while I am on it, I see far too often that such an IDENTITY or
GUID PK is also defined as the Clustered Index, if there is one at all. Tha
t
is a lousy choice. Not only should every table have a KEY (Primary or
Otherwise)--this is a logical or design requirement--every table should also
have a WELL CHOSEN Clustered Index or Key defined--this is a physical
recommendation.
A well chosen Clustered Index or Key will affect the construction of
statistics and other indexes on the table. It should be nearly unique, if
not a unique key. It should not change often. And, it should be considered
for RANGE type query requirements. In our example, not only do we need to
create a UNIQUE CONSTRAINT for the Name attribute, the Last Name or the Last
Name + First Name combination should be considered for the Clustered Index.
I hope I have not gone to Academic, or Theoretical--some might say, Cultish.
Nevertheless, these point go far too unnoticed by the mass of database
designers and users and needs to be said more often.
Sincerely,
Anthony Thomas
"Paul Sjoerdsma" wrote:
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||Anthony and Wayne,
Thanks for your answers. Although I would consider myself to be
experienced in database design (with the necessary theoretical
background) I still do not grok the full idea behind the concept of a
unique index; even after your elaborate explanations.
To me it seems to have raised it's head out of some murky swamp. There
are nicer places to visit, so a Unique Key constraint is what it will
be.
However if someone could give me a clear example where I would use a
unique index instead of a unique key I would be very happy,
thanks,
regards
Paul
On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
<AnthonyThomas@.discussions.microsoft.com> wrote:
>In addition to the other respondents reply, implicit in your question is
>really two seperate questions:
>|||> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
Very good question. IMO, there aren't any (*). Some say that you should use
an index directly when
the uniqueness isn't a property of the data model per se, but IMO, I have a
hard time finding such
an example. If I want to enforce uniqueness, I surely want to expose this at
the logical level
(constraint) as well, right?
(*) Here's an exception. There are at least one attribute you can define for
a unique index which
isn't available through a unique constraint. If this index attribute is impo
rtant for you, define an
index directly and not though a unique constraint. In SQL Server 2005, this
will be available
through a constraint...
Be prepared that different people have different viewpoints about this. It i
s a bit like the age-old
discussion "natural keys vs. surrogate keys".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Sjoerdsma" <paul.nospam.@.pasoftware.nl> wrote in message
news:h9dmo0dce287fe5gdjoo0ro4mvgqrsggb5@.
4ax.com...
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
>
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
>
> thanks,
> regards
> Paul
>
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
>
>|||I have two real life examples where I would use a unique index as
opposed to a unique constraint.
1. When you have a relation table that contains the keys of two
referenced tables. For example the table StudentCourses which lists the
courses for each student. It might be defined as:
CREATE TABLE StudentCourses
(StudentID int not null REFERENCES Students
,CourseID int not null REFERENCES Courses
,CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID)
)
In this case there already is a unique constraint on (StudentID,
CourseID). For performance purposes I would add a unique index on
(CourseID, StudentID). This would benefit query like:
SELECT Course, COUNT(*) AS StudentCount
FROM Courses C1
INNER JOIN StudentCourses CS1
ON CS1.CourseID = C1.CourseID
GROUP BY Course
2. When a (compound) covering index is needed, and one of its columns is
by itself unique. When I have the choice between a nonunique and a
unique index, I choose a unique index. But the columns of the covering
index do not constitute a new or different "unique relation", so a
unique constraint would be inappropriate.
The rule I use for unique constraints is that you should be able to
remove all user created unique indexes, and the data model would still
be consistent and enforced.
Hope this helps,
Gert-Jan
Paul Sjoerdsma wrote:[vbcol=seagreen]
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
> thanks,
> regards
> Paul
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
>
Difference between creating a Unique Constraint and a Unique Index
Hi,
I've looked at the online documentation and encountered this piece of
text
"If uniqueness must be enforced to ensure data integrity, create a
UNIQUE or PRIMARY KEY constraint on the column rather than a unique
index"
I'm baffled; I understand a unique contraint, and personally do not
care how it is physically enforced (using an index to speed up the
process) as long as it is enforced. However what is the deal with
unique indexes? The piece of documentation above seems to suggest that
a unique index will not enforce uniqueness.
The reason I ask is that I am working against a legacy system where
unique indexes are used for each table. Each table, besides a PK, has
a column that stores a GUID. Personally I would use a Unique
Contraint, but they have used a unique index on this column. What
would be the advantages and disadvantages of using a unique index vs a
unique constraint.'
regards
Paul SjoerdsmaYes, a unique index enforces uniqueness. A unique index is created in the
background when you specify a PK or Unique constraint.
The difference is more semantic, but also tactical... Placing the constraint
indicates a design requirement. When you create the index directly, how can
you know IF the index was created for performance, or if the index is
intended to enforce some design constraint... The fact is that you can
not... So use PK and Uniques to enforce design requirements...Indexes are
created for 2 main reasons, to enforce integrity and speed up queries.The
indexes used to speed up queries( or other statements) may need to change
over time as business priorities change, but we would still need to maintain
the indexes created to enforce data integrity.
Indexes created as a by-product of PK or unique constraints can NOT be
dropped using DROP index, so you are protected from inadvertently dropping
an index which is used for data integrity. FOr this you must use alter table
drop constraint, alter table add constraint... Any index that goes away
using DROP index, therefore, would have been created for performance, and is
a candidate for re-assessment as needs change
Additionally FKs must be placed on columns with PKs or Unique
constraints.(from BOL). HOwever the actual code allows you to put an FK on
any column which has a unique index ( although I beleive this should NOT be
allowed)..
Hope this helps/
--
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
"Paul Sjoerdsma" <paul@.pasoftware.nl> wrote in message
news:2j8ko0pdll18amito60gcqne5g58en6otj@.4ax.com...
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||In addition to the other respondents reply, implicit in your question is
really two seperate questions:
What is the difference between the use of PK and Unique Constraints and
Unique Indexes? The was answered by the other respondent. Except, so you
know, there is no such thing as a PK index, only a constraint, which is also
enforced through a Unique Index.
Second, you made mention of PK and GUID, which I am assuming you implied
what is typically used some sort of INT IDENTITY. Both of these are unique
by design, one locally to the table, the other across all tables, databases,
and time. However, these are mearly mechanics. They do not garauntee
uniqueness of the data, which is the sole purpose of KEYS, Primary or
Alternate. Moreover, PKs and Unique Constraints are not limited to IDENTITY
nor GUID. For example:
EmployeeID LastName FirstName
1 Smith John
2 Jones Tom
3 Marshall Eric
4 Smith John
Although EmployeeID is unique, because it is IDENTITY and is so by
construction, for this table, the DATA is NOT unique: John Smith is repeated.
Therefore, this table is NOT even a table by relational design--it is not in
1NF (First Normal Form).
The real key for this entity is the Logical Name attribute, which is
composite upon FirstName and LastName. Since that is pretty much all of the
data, it is also the Primary Key candidate. However, if this table were
JOINed to some other not only would you have to populate both attributes to
that table, you would have to populate string types (data storage
inefficient) and would have to include both attributes in any Foreign Key or
JOIN declaration, also highly inefficient.
So, oftentimes, one creates the IDENTITY or GUID attributes to simplify
these requirements. What happens, TOO OFTEN, is the PK is defined on
EmployeeID and no Unique Constraint is ever defined on the Candidate Key
combination of First and Last Names, which is the true key of this table, but
should be and MUST be for this implementation to be in at least 1NF.
Moreover, while I am on it, I see far too often that such an IDENTITY or
GUID PK is also defined as the Clustered Index, if there is one at all. That
is a lousy choice. Not only should every table have a KEY (Primary or
Otherwise)--this is a logical or design requirement--every table should also
have a WELL CHOSEN Clustered Index or Key defined--this is a physical
recommendation.
A well chosen Clustered Index or Key will affect the construction of
statistics and other indexes on the table. It should be nearly unique, if
not a unique key. It should not change often. And, it should be considered
for RANGE type query requirements. In our example, not only do we need to
create a UNIQUE CONSTRAINT for the Name attribute, the Last Name or the Last
Name + First Name combination should be considered for the Clustered Index.
I hope I have not gone to Academic, or Theoretical--some might say, Cultish.
Nevertheless, these point go far too unnoticed by the mass of database
designers and users and needs to be said more often.
Sincerely,
Anthony Thomas
"Paul Sjoerdsma" wrote:
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||Anthony and Wayne,
Thanks for your answers. Although I would consider myself to be
experienced in database design (with the necessary theoretical
background) I still do not grok the full idea behind the concept of a
unique index; even after your elaborate explanations.
To me it seems to have raised it's head out of some murky swamp. There
are nicer places to visit, so a Unique Key constraint is what it will
be.
However if someone could give me a clear example where I would use a
unique index instead of a unique key I would be very happy,
thanks,
regards
Paul
On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
<AnthonyThomas@.discussions.microsoft.com> wrote:
>In addition to the other respondents reply, implicit in your question is
>really two seperate questions:
>|||> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
Very good question. IMO, there aren't any (*). Some say that you should use an index directly when
the uniqueness isn't a property of the data model per se, but IMO, I have a hard time finding such
an example. If I want to enforce uniqueness, I surely want to expose this at the logical level
(constraint) as well, right?
(*) Here's an exception. There are at least one attribute you can define for a unique index which
isn't available through a unique constraint. If this index attribute is important for you, define an
index directly and not though a unique constraint. In SQL Server 2005, this will be available
through a constraint...
Be prepared that different people have different viewpoints about this. It is a bit like the age-old
discussion "natural keys vs. surrogate keys".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Sjoerdsma" <paul.nospam.@.pasoftware.nl> wrote in message
news:h9dmo0dce287fe5gdjoo0ro4mvgqrsggb5@.4ax.com...
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
>
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
>
> thanks,
> regards
> Paul
>
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >
>|||I have two real life examples where I would use a unique index as
opposed to a unique constraint.
1. When you have a relation table that contains the keys of two
referenced tables. For example the table StudentCourses which lists the
courses for each student. It might be defined as:
CREATE TABLE StudentCourses
(StudentID int not null REFERENCES Students
,CourseID int not null REFERENCES Courses
,CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID)
)
In this case there already is a unique constraint on (StudentID,
CourseID). For performance purposes I would add a unique index on
(CourseID, StudentID). This would benefit query like:
SELECT Course, COUNT(*) AS StudentCount
FROM Courses C1
INNER JOIN StudentCourses CS1
ON CS1.CourseID = C1.CourseID
GROUP BY Course
2. When a (compound) covering index is needed, and one of its columns is
by itself unique. When I have the choice between a nonunique and a
unique index, I choose a unique index. But the columns of the covering
index do not constitute a new or different "unique relation", so a
unique constraint would be inappropriate.
The rule I use for unique constraints is that you should be able to
remove all user created unique indexes, and the data model would still
be consistent and enforced.
Hope this helps,
Gert-Jan
Paul Sjoerdsma wrote:
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
> thanks,
> regards
> Paul
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >
I've looked at the online documentation and encountered this piece of
text
"If uniqueness must be enforced to ensure data integrity, create a
UNIQUE or PRIMARY KEY constraint on the column rather than a unique
index"
I'm baffled; I understand a unique contraint, and personally do not
care how it is physically enforced (using an index to speed up the
process) as long as it is enforced. However what is the deal with
unique indexes? The piece of documentation above seems to suggest that
a unique index will not enforce uniqueness.
The reason I ask is that I am working against a legacy system where
unique indexes are used for each table. Each table, besides a PK, has
a column that stores a GUID. Personally I would use a Unique
Contraint, but they have used a unique index on this column. What
would be the advantages and disadvantages of using a unique index vs a
unique constraint.'
regards
Paul SjoerdsmaYes, a unique index enforces uniqueness. A unique index is created in the
background when you specify a PK or Unique constraint.
The difference is more semantic, but also tactical... Placing the constraint
indicates a design requirement. When you create the index directly, how can
you know IF the index was created for performance, or if the index is
intended to enforce some design constraint... The fact is that you can
not... So use PK and Uniques to enforce design requirements...Indexes are
created for 2 main reasons, to enforce integrity and speed up queries.The
indexes used to speed up queries( or other statements) may need to change
over time as business priorities change, but we would still need to maintain
the indexes created to enforce data integrity.
Indexes created as a by-product of PK or unique constraints can NOT be
dropped using DROP index, so you are protected from inadvertently dropping
an index which is used for data integrity. FOr this you must use alter table
drop constraint, alter table add constraint... Any index that goes away
using DROP index, therefore, would have been created for performance, and is
a candidate for re-assessment as needs change
Additionally FKs must be placed on columns with PKs or Unique
constraints.(from BOL). HOwever the actual code allows you to put an FK on
any column which has a unique index ( although I beleive this should NOT be
allowed)..
Hope this helps/
--
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
"Paul Sjoerdsma" <paul@.pasoftware.nl> wrote in message
news:2j8ko0pdll18amito60gcqne5g58en6otj@.4ax.com...
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||In addition to the other respondents reply, implicit in your question is
really two seperate questions:
What is the difference between the use of PK and Unique Constraints and
Unique Indexes? The was answered by the other respondent. Except, so you
know, there is no such thing as a PK index, only a constraint, which is also
enforced through a Unique Index.
Second, you made mention of PK and GUID, which I am assuming you implied
what is typically used some sort of INT IDENTITY. Both of these are unique
by design, one locally to the table, the other across all tables, databases,
and time. However, these are mearly mechanics. They do not garauntee
uniqueness of the data, which is the sole purpose of KEYS, Primary or
Alternate. Moreover, PKs and Unique Constraints are not limited to IDENTITY
nor GUID. For example:
EmployeeID LastName FirstName
1 Smith John
2 Jones Tom
3 Marshall Eric
4 Smith John
Although EmployeeID is unique, because it is IDENTITY and is so by
construction, for this table, the DATA is NOT unique: John Smith is repeated.
Therefore, this table is NOT even a table by relational design--it is not in
1NF (First Normal Form).
The real key for this entity is the Logical Name attribute, which is
composite upon FirstName and LastName. Since that is pretty much all of the
data, it is also the Primary Key candidate. However, if this table were
JOINed to some other not only would you have to populate both attributes to
that table, you would have to populate string types (data storage
inefficient) and would have to include both attributes in any Foreign Key or
JOIN declaration, also highly inefficient.
So, oftentimes, one creates the IDENTITY or GUID attributes to simplify
these requirements. What happens, TOO OFTEN, is the PK is defined on
EmployeeID and no Unique Constraint is ever defined on the Candidate Key
combination of First and Last Names, which is the true key of this table, but
should be and MUST be for this implementation to be in at least 1NF.
Moreover, while I am on it, I see far too often that such an IDENTITY or
GUID PK is also defined as the Clustered Index, if there is one at all. That
is a lousy choice. Not only should every table have a KEY (Primary or
Otherwise)--this is a logical or design requirement--every table should also
have a WELL CHOSEN Clustered Index or Key defined--this is a physical
recommendation.
A well chosen Clustered Index or Key will affect the construction of
statistics and other indexes on the table. It should be nearly unique, if
not a unique key. It should not change often. And, it should be considered
for RANGE type query requirements. In our example, not only do we need to
create a UNIQUE CONSTRAINT for the Name attribute, the Last Name or the Last
Name + First Name combination should be considered for the Clustered Index.
I hope I have not gone to Academic, or Theoretical--some might say, Cultish.
Nevertheless, these point go far too unnoticed by the mass of database
designers and users and needs to be said more often.
Sincerely,
Anthony Thomas
"Paul Sjoerdsma" wrote:
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||Anthony and Wayne,
Thanks for your answers. Although I would consider myself to be
experienced in database design (with the necessary theoretical
background) I still do not grok the full idea behind the concept of a
unique index; even after your elaborate explanations.
To me it seems to have raised it's head out of some murky swamp. There
are nicer places to visit, so a Unique Key constraint is what it will
be.
However if someone could give me a clear example where I would use a
unique index instead of a unique key I would be very happy,
thanks,
regards
Paul
On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
<AnthonyThomas@.discussions.microsoft.com> wrote:
>In addition to the other respondents reply, implicit in your question is
>really two seperate questions:
>|||> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
Very good question. IMO, there aren't any (*). Some say that you should use an index directly when
the uniqueness isn't a property of the data model per se, but IMO, I have a hard time finding such
an example. If I want to enforce uniqueness, I surely want to expose this at the logical level
(constraint) as well, right?
(*) Here's an exception. There are at least one attribute you can define for a unique index which
isn't available through a unique constraint. If this index attribute is important for you, define an
index directly and not though a unique constraint. In SQL Server 2005, this will be available
through a constraint...
Be prepared that different people have different viewpoints about this. It is a bit like the age-old
discussion "natural keys vs. surrogate keys".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Sjoerdsma" <paul.nospam.@.pasoftware.nl> wrote in message
news:h9dmo0dce287fe5gdjoo0ro4mvgqrsggb5@.4ax.com...
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
>
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
>
> thanks,
> regards
> Paul
>
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >
>|||I have two real life examples where I would use a unique index as
opposed to a unique constraint.
1. When you have a relation table that contains the keys of two
referenced tables. For example the table StudentCourses which lists the
courses for each student. It might be defined as:
CREATE TABLE StudentCourses
(StudentID int not null REFERENCES Students
,CourseID int not null REFERENCES Courses
,CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID)
)
In this case there already is a unique constraint on (StudentID,
CourseID). For performance purposes I would add a unique index on
(CourseID, StudentID). This would benefit query like:
SELECT Course, COUNT(*) AS StudentCount
FROM Courses C1
INNER JOIN StudentCourses CS1
ON CS1.CourseID = C1.CourseID
GROUP BY Course
2. When a (compound) covering index is needed, and one of its columns is
by itself unique. When I have the choice between a nonunique and a
unique index, I choose a unique index. But the columns of the covering
index do not constitute a new or different "unique relation", so a
unique constraint would be inappropriate.
The rule I use for unique constraints is that you should be able to
remove all user created unique indexes, and the data model would still
be consistent and enforced.
Hope this helps,
Gert-Jan
Paul Sjoerdsma wrote:
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
> thanks,
> regards
> Paul
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >
Friday, February 24, 2012
Developer Edition- is that enough?
I am working for a university and we received funding to create an online
searchable database for Company A. Company A has SQL Server 2005 and uses it
within its company. We would like to create a database of articles and
research for them, test it, post it on the Web, see if it works and then give
the database to Company A. Can we do that with only the Developer Edition, or
do we need to buy the SQL Server 2005? I know the only difference between the
Developer Edition and the Enterprise edition is the license, what does that
mean'
Thank you so much for any advice you have."lana1916" <lana1916@.discussions.microsoft.com> wrote in message
news:30191BB1-2DBD-4A67-BC8C-9AC8DB11649C@.microsoft.com...
>I am working for a university and we received funding to create an online
> searchable database for Company A. Company A has SQL Server 2005 and uses
> it
> within its company. We would like to create a database of articles and
> research for them, test it, post it on the Web, see if it works and then
> give
> the database to Company A. Can we do that with only the Developer Edition,
> or
> do we need to buy the SQL Server 2005? I know the only difference between
> the
> Developer Edition and the Enterprise edition is the license, what does
> that
> mean'
> Thank you so much for any advice you have
Here's the operative language from the SQL Server 2005 Developer Edition
license
--
If you comply with these license terms, you have the rights below for each
license you acquire.
1. OVERVIEW.
a. Software. The software includes development tools, software programs
and documentation.
b. License Model. The software is licensed on a per user basis.
2. INSTALLATION AND USE RIGHTS.
a. General. One user may install and use copies of the software to
design, develop, test and demonstrate your programs. Testing does not
include staging on a server in a production environment, such as loading
content prior to production use.
b. Included Microsoft Programs. These license terms apply to all
Microsoft programs included with the software. If the license terms with
any of those programs give you other rights that do not expressly conflict
with these license terms, you also have those rights.
3. ADDITIONAL LICENSING REQUIREMENTS AND/OR USE RIGHTS.
a. User Testing. Your end users may access the software to perform
acceptance tests on your programs.
b. Demonstration. Any person that has access to your internal network
may install and use copies of the software to demonstrate use of your
programs with the software. Those copies may not be used for any other
purpose.
--
If this doesn't answer your question, you might want to check with your
Microsoft representitive. Note that any number of developers, each of whom
owns a seperate Developer Edition license, can share one server instance of
SQL Server Developer edition.
To move from development to production an instance of SQL Server Developer
Edition can be upgraded to SQL Server Enterprise Edition, or the database
can be moved to a server running any other edition of SQL Server.
David.|||The fact that you want to post it on the Web is usually considered by
Microsoft as a production database; so the developer edition is not
sufficient. However, as this is only for tests, SQL-Server Express 2005
should be sufficient as the backend for this web thing; so you will be OK if
you use the Express edition as your backend database.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"lana1916" <lana1916@.discussions.microsoft.com> wrote in message
news:30191BB1-2DBD-4A67-BC8C-9AC8DB11649C@.microsoft.com...
>I am working for a university and we received funding to create an online
> searchable database for Company A. Company A has SQL Server 2005 and uses
> it
> within its company. We would like to create a database of articles and
> research for them, test it, post it on the Web, see if it works and then
> give
> the database to Company A. Can we do that with only the Developer Edition,
> or
> do we need to buy the SQL Server 2005? I know the only difference between
> the
> Developer Edition and the Enterprise edition is the license, what does
> that
> mean'
> Thank you so much for any advice you have.
searchable database for Company A. Company A has SQL Server 2005 and uses it
within its company. We would like to create a database of articles and
research for them, test it, post it on the Web, see if it works and then give
the database to Company A. Can we do that with only the Developer Edition, or
do we need to buy the SQL Server 2005? I know the only difference between the
Developer Edition and the Enterprise edition is the license, what does that
mean'
Thank you so much for any advice you have."lana1916" <lana1916@.discussions.microsoft.com> wrote in message
news:30191BB1-2DBD-4A67-BC8C-9AC8DB11649C@.microsoft.com...
>I am working for a university and we received funding to create an online
> searchable database for Company A. Company A has SQL Server 2005 and uses
> it
> within its company. We would like to create a database of articles and
> research for them, test it, post it on the Web, see if it works and then
> give
> the database to Company A. Can we do that with only the Developer Edition,
> or
> do we need to buy the SQL Server 2005? I know the only difference between
> the
> Developer Edition and the Enterprise edition is the license, what does
> that
> mean'
> Thank you so much for any advice you have
Here's the operative language from the SQL Server 2005 Developer Edition
license
--
If you comply with these license terms, you have the rights below for each
license you acquire.
1. OVERVIEW.
a. Software. The software includes development tools, software programs
and documentation.
b. License Model. The software is licensed on a per user basis.
2. INSTALLATION AND USE RIGHTS.
a. General. One user may install and use copies of the software to
design, develop, test and demonstrate your programs. Testing does not
include staging on a server in a production environment, such as loading
content prior to production use.
b. Included Microsoft Programs. These license terms apply to all
Microsoft programs included with the software. If the license terms with
any of those programs give you other rights that do not expressly conflict
with these license terms, you also have those rights.
3. ADDITIONAL LICENSING REQUIREMENTS AND/OR USE RIGHTS.
a. User Testing. Your end users may access the software to perform
acceptance tests on your programs.
b. Demonstration. Any person that has access to your internal network
may install and use copies of the software to demonstrate use of your
programs with the software. Those copies may not be used for any other
purpose.
--
If this doesn't answer your question, you might want to check with your
Microsoft representitive. Note that any number of developers, each of whom
owns a seperate Developer Edition license, can share one server instance of
SQL Server Developer edition.
To move from development to production an instance of SQL Server Developer
Edition can be upgraded to SQL Server Enterprise Edition, or the database
can be moved to a server running any other edition of SQL Server.
David.|||The fact that you want to post it on the Web is usually considered by
Microsoft as a production database; so the developer edition is not
sufficient. However, as this is only for tests, SQL-Server Express 2005
should be sufficient as the backend for this web thing; so you will be OK if
you use the Express edition as your backend database.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"lana1916" <lana1916@.discussions.microsoft.com> wrote in message
news:30191BB1-2DBD-4A67-BC8C-9AC8DB11649C@.microsoft.com...
>I am working for a university and we received funding to create an online
> searchable database for Company A. Company A has SQL Server 2005 and uses
> it
> within its company. We would like to create a database of articles and
> research for them, test it, post it on the Web, see if it works and then
> give
> the database to Company A. Can we do that with only the Developer Edition,
> or
> do we need to buy the SQL Server 2005? I know the only difference between
> the
> Developer Edition and the Enterprise edition is the license, what does
> that
> mean'
> Thank you so much for any advice you have.
Friday, February 17, 2012
Developer 2005 Connection Limit
I have looked online but cannot find a clear answer to whether Microsoft SQL
Server 2005 Developer Edition has a maximum connection limit? I see that
SQL Express has 25 maximum connections.
If anyone knows that answer it would be greatly appreciated.
Thanks!Hi Jim
Maximum Capacity specifications are available at
http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have 32,767
user connections.
John
"Jim Norton" wrote:
> I have looked online but cannot find a clear answer to whether Microsoft SQL
> Server 2005 Developer Edition has a maximum connection limit? I see that
> SQL Express has 25 maximum connections.
> If anyone knows that answer it would be greatly appreciated.
> Thanks!
>
>|||Hi Jim
I would be interested where you got 25 connections from!
http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
states
Note: If SQL Server 2005 Express is running on Windows XP Home, it is
limited to five simultaneous connections. If it is running on Windows 2000 or
Windows XP Professional, it is limited to 10 simultaneous connections.
However, these are limitations of the operating system and not of SQL Server
2005 Express.
But nothing about any other limit!
John
"John Bell" wrote:
> Hi Jim
> Maximum Capacity specifications are available at
> http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have 32,767
> user connections.
> John
> "Jim Norton" wrote:
> > I have looked online but cannot find a clear answer to whether Microsoft SQL
> > Server 2005 Developer Edition has a maximum connection limit? I see that
> > SQL Express has 25 maximum connections.
> >
> > If anyone knows that answer it would be greatly appreciated.
> >
> > Thanks!
> >
> >
> >|||Hi John,
Funny, I can't find where I found that but it appears you are correct. I
saw some references online that the Devloper edition of SQL may or may not
have a CAL or connection limit imposed because it is intended for
development purposes only and I am trying to determine if that will impact
my test environment before I set it up. The Max Capacity specs dont really
break down the different versions so I am still a little nervous.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:81BA8EF8-E085-4377-BC85-68F5722434E7@.microsoft.com...
> Hi Jim
> I would be interested where you got 25 connections from!
> http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
> states
> Note: If SQL Server 2005 Express is running on Windows XP Home, it is
> limited to five simultaneous connections. If it is running on Windows 2000
> or
> Windows XP Professional, it is limited to 10 simultaneous connections.
> However, these are limitations of the operating system and not of SQL
> Server
> 2005 Express.
> But nothing about any other limit!
> John
> "John Bell" wrote:
>> Hi Jim
>> Maximum Capacity specifications are available at
>> http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have
>> 32,767
>> user connections.
>> John
>> "Jim Norton" wrote:
>> > I have looked online but cannot find a clear answer to whether
>> > Microsoft SQL
>> > Server 2005 Developer Edition has a maximum connection limit? I see
>> > that
>> > SQL Express has 25 maximum connections.
>> >
>> > If anyone knows that answer it would be greatly appreciated.
>> >
>> > Thanks!
>> >
>> >
>> >|||"Jim Norton" <joe@.joe.com> wrote in message
news:%23dETmctmGHA.4728@.TK2MSFTNGP05.phx.gbl...
> Hi John,
> Funny, I can't find where I found that but it appears you are correct. I
> saw some references online that the Devloper edition of SQL may or may not
> have a CAL or connection limit imposed because it is intended for
> development purposes only and I am trying to determine if that will impact
> my test environment before I set it up. The Max Capacity specs dont
really
> break down the different versions so I am still a little nervous.
The Developer edition is "basically" the Enterprise Edition, but licensed
for developer use only.
I haven't heard about SQL 2005 Express having any inherent limits itself.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:81BA8EF8-E085-4377-BC85-68F5722434E7@.microsoft.com...
> > Hi Jim
> >
> > I would be interested where you got 25 connections from!
> >
> >
http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
> > states
> >
> > Note: If SQL Server 2005 Express is running on Windows XP Home, it is
> > limited to five simultaneous connections. If it is running on Windows
2000
> > or
> > Windows XP Professional, it is limited to 10 simultaneous connections.
> > However, these are limitations of the operating system and not of SQL
> > Server
> > 2005 Express.
> >
> > But nothing about any other limit!
> >
> > John
> >
> > "John Bell" wrote:
> >
> >> Hi Jim
> >>
> >> Maximum Capacity specifications are available at
> >> http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have
> >> 32,767
> >> user connections.
> >>
> >> John
> >>
> >> "Jim Norton" wrote:
> >>
> >> > I have looked online but cannot find a clear answer to whether
> >> > Microsoft SQL
> >> > Server 2005 Developer Edition has a maximum connection limit? I see
> >> > that
> >> > SQL Express has 25 maximum connections.
> >> >
> >> > If anyone knows that answer it would be greatly appreciated.
> >> >
> >> > Thanks!
> >> >
> >> >
> >> >
>|||Hi Jim
You could look at SELECT @.@.MAX_CONNECTIONS to see what value you have! It
could be that your system is overloaded in other areas before the maximim
number of connections is reached.
John
"Jim Norton" wrote:
> Hi John,
> Funny, I can't find where I found that but it appears you are correct. I
> saw some references online that the Devloper edition of SQL may or may not
> have a CAL or connection limit imposed because it is intended for
> development purposes only and I am trying to determine if that will impact
> my test environment before I set it up. The Max Capacity specs dont really
> break down the different versions so I am still a little nervous.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:81BA8EF8-E085-4377-BC85-68F5722434E7@.microsoft.com...
> > Hi Jim
> >
> > I would be interested where you got 25 connections from!
> >
> > http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
> > states
> >
> > Note: If SQL Server 2005 Express is running on Windows XP Home, it is
> > limited to five simultaneous connections. If it is running on Windows 2000
> > or
> > Windows XP Professional, it is limited to 10 simultaneous connections.
> > However, these are limitations of the operating system and not of SQL
> > Server
> > 2005 Express.
> >
> > But nothing about any other limit!
> >
> > John
> >
> > "John Bell" wrote:
> >
> >> Hi Jim
> >>
> >> Maximum Capacity specifications are available at
> >> http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have
> >> 32,767
> >> user connections.
> >>
> >> John
> >>
> >> "Jim Norton" wrote:
> >>
> >> > I have looked online but cannot find a clear answer to whether
> >> > Microsoft SQL
> >> > Server 2005 Developer Edition has a maximum connection limit? I see
> >> > that
> >> > SQL Express has 25 maximum connections.
> >> >
> >> > If anyone knows that answer it would be greatly appreciated.
> >> >
> >> > Thanks!
> >> >
> >> >
> >> >
>
>
Server 2005 Developer Edition has a maximum connection limit? I see that
SQL Express has 25 maximum connections.
If anyone knows that answer it would be greatly appreciated.
Thanks!Hi Jim
Maximum Capacity specifications are available at
http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have 32,767
user connections.
John
"Jim Norton" wrote:
> I have looked online but cannot find a clear answer to whether Microsoft SQL
> Server 2005 Developer Edition has a maximum connection limit? I see that
> SQL Express has 25 maximum connections.
> If anyone knows that answer it would be greatly appreciated.
> Thanks!
>
>|||Hi Jim
I would be interested where you got 25 connections from!
http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
states
Note: If SQL Server 2005 Express is running on Windows XP Home, it is
limited to five simultaneous connections. If it is running on Windows 2000 or
Windows XP Professional, it is limited to 10 simultaneous connections.
However, these are limitations of the operating system and not of SQL Server
2005 Express.
But nothing about any other limit!
John
"John Bell" wrote:
> Hi Jim
> Maximum Capacity specifications are available at
> http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have 32,767
> user connections.
> John
> "Jim Norton" wrote:
> > I have looked online but cannot find a clear answer to whether Microsoft SQL
> > Server 2005 Developer Edition has a maximum connection limit? I see that
> > SQL Express has 25 maximum connections.
> >
> > If anyone knows that answer it would be greatly appreciated.
> >
> > Thanks!
> >
> >
> >|||Hi John,
Funny, I can't find where I found that but it appears you are correct. I
saw some references online that the Devloper edition of SQL may or may not
have a CAL or connection limit imposed because it is intended for
development purposes only and I am trying to determine if that will impact
my test environment before I set it up. The Max Capacity specs dont really
break down the different versions so I am still a little nervous.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:81BA8EF8-E085-4377-BC85-68F5722434E7@.microsoft.com...
> Hi Jim
> I would be interested where you got 25 connections from!
> http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
> states
> Note: If SQL Server 2005 Express is running on Windows XP Home, it is
> limited to five simultaneous connections. If it is running on Windows 2000
> or
> Windows XP Professional, it is limited to 10 simultaneous connections.
> However, these are limitations of the operating system and not of SQL
> Server
> 2005 Express.
> But nothing about any other limit!
> John
> "John Bell" wrote:
>> Hi Jim
>> Maximum Capacity specifications are available at
>> http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have
>> 32,767
>> user connections.
>> John
>> "Jim Norton" wrote:
>> > I have looked online but cannot find a clear answer to whether
>> > Microsoft SQL
>> > Server 2005 Developer Edition has a maximum connection limit? I see
>> > that
>> > SQL Express has 25 maximum connections.
>> >
>> > If anyone knows that answer it would be greatly appreciated.
>> >
>> > Thanks!
>> >
>> >
>> >|||"Jim Norton" <joe@.joe.com> wrote in message
news:%23dETmctmGHA.4728@.TK2MSFTNGP05.phx.gbl...
> Hi John,
> Funny, I can't find where I found that but it appears you are correct. I
> saw some references online that the Devloper edition of SQL may or may not
> have a CAL or connection limit imposed because it is intended for
> development purposes only and I am trying to determine if that will impact
> my test environment before I set it up. The Max Capacity specs dont
really
> break down the different versions so I am still a little nervous.
The Developer edition is "basically" the Enterprise Edition, but licensed
for developer use only.
I haven't heard about SQL 2005 Express having any inherent limits itself.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:81BA8EF8-E085-4377-BC85-68F5722434E7@.microsoft.com...
> > Hi Jim
> >
> > I would be interested where you got 25 connections from!
> >
> >
http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
> > states
> >
> > Note: If SQL Server 2005 Express is running on Windows XP Home, it is
> > limited to five simultaneous connections. If it is running on Windows
2000
> > or
> > Windows XP Professional, it is limited to 10 simultaneous connections.
> > However, these are limitations of the operating system and not of SQL
> > Server
> > 2005 Express.
> >
> > But nothing about any other limit!
> >
> > John
> >
> > "John Bell" wrote:
> >
> >> Hi Jim
> >>
> >> Maximum Capacity specifications are available at
> >> http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have
> >> 32,767
> >> user connections.
> >>
> >> John
> >>
> >> "Jim Norton" wrote:
> >>
> >> > I have looked online but cannot find a clear answer to whether
> >> > Microsoft SQL
> >> > Server 2005 Developer Edition has a maximum connection limit? I see
> >> > that
> >> > SQL Express has 25 maximum connections.
> >> >
> >> > If anyone knows that answer it would be greatly appreciated.
> >> >
> >> > Thanks!
> >> >
> >> >
> >> >
>|||Hi Jim
You could look at SELECT @.@.MAX_CONNECTIONS to see what value you have! It
could be that your system is overloaded in other areas before the maximim
number of connections is reached.
John
"Jim Norton" wrote:
> Hi John,
> Funny, I can't find where I found that but it appears you are correct. I
> saw some references online that the Devloper edition of SQL may or may not
> have a CAL or connection limit imposed because it is intended for
> development purposes only and I am trying to determine if that will impact
> my test environment before I set it up. The Max Capacity specs dont really
> break down the different versions so I am still a little nervous.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:81BA8EF8-E085-4377-BC85-68F5722434E7@.microsoft.com...
> > Hi Jim
> >
> > I would be interested where you got 25 connections from!
> >
> > http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
> > states
> >
> > Note: If SQL Server 2005 Express is running on Windows XP Home, it is
> > limited to five simultaneous connections. If it is running on Windows 2000
> > or
> > Windows XP Professional, it is limited to 10 simultaneous connections.
> > However, these are limitations of the operating system and not of SQL
> > Server
> > 2005 Express.
> >
> > But nothing about any other limit!
> >
> > John
> >
> > "John Bell" wrote:
> >
> >> Hi Jim
> >>
> >> Maximum Capacity specifications are available at
> >> http://msdn2.microsoft.com/en-us/library/ms143432.aspx You can have
> >> 32,767
> >> user connections.
> >>
> >> John
> >>
> >> "Jim Norton" wrote:
> >>
> >> > I have looked online but cannot find a clear answer to whether
> >> > Microsoft SQL
> >> > Server 2005 Developer Edition has a maximum connection limit? I see
> >> > that
> >> > SQL Express has 25 maximum connections.
> >> >
> >> > If anyone knows that answer it would be greatly appreciated.
> >> >
> >> > Thanks!
> >> >
> >> >
> >> >
>
>
Subscribe to:
Posts (Atom)