sql record counts: table

Showing posts with label table. Show all posts

Thursday, March 29, 2012

Difference between the estimated size and size given by sp_spaceus

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

Difference between the estimated size and size given by sp_spaceus

difference between table relations and foreign keys?

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

difference between table relations and foreign keys?

difference between SYSTEM_USER and USER

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

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

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

IM: mike@.epprecht.net

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

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

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

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

Depends on your business requirements.

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

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

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

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

Tuesday, March 27, 2012

Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference Between Physical IOs and Read-Ahead

Hello ...

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

Hi Umachandar ...

Sorry for the delay in responding to this...

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

Difference between Matrix and Table

Hello.
I'm fairly new to Reporting Services, so my question might seem really
simple to you guys. But what is the difference between a matrix and a table?
When should I use what? Are there any articles on the web that explain this?
The best I've found is in this article:
http://www.ftponline.com/vsm/2004_09/magazine/columns/databasedesign/default_pf.aspx
[Quote]
The report type defines the structure, or data region, of the data returned
by your query. The Report Wizard lets you present this information as either
a table or a matrix, though you have more options outside the wizard. The
main difference between these two types of data regions is the number of
columns. A table has a fixed number of columns; a matrix has a variable
number determined by the query results.
[/quote]
- But I'd like to see a more practical explanation.
Any input appreciated!
Kaisa M. LindahlI use matrix when I have both row and column groupings. What I mean is if I
have row groupings as Dept, Group, User (drill down) and in the column I have
year, quarter, month drilldowns, u have to use a matrix. If you just have row
groupings I would suggest use the table, because a matrix is quite buggy when
it comes to exporting to excel.
Hope this helps
"Kaisa M. Lindahl" wrote:
> Hello.
> I'm fairly new to Reporting Services, so my question might seem really
> simple to you guys. But what is the difference between a matrix and a table?
> When should I use what? Are there any articles on the web that explain this?
> The best I've found is in this article:
> http://www.ftponline.com/vsm/2004_09/magazine/columns/databasedesign/default_pf.aspx
> [Quote]
> The report type defines the structure, or data region, of the data returned
> by your query. The Report Wizard lets you present this information as either
> a table or a matrix, though you have more options outside the wizard. The
> main difference between these two types of data regions is the number of
> columns. A table has a fixed number of columns; a matrix has a variable
> number determined by the query results.
> [/quote]
> - But I'd like to see a more practical explanation.
> Any input appreciated!
> Kaisa M. Lindahl
>
>

Sunday, March 25, 2012

difference between FOR INSERT and AFTER INSERT triggers

I've been reading the docs and playing around, but I'm still not
getting the difference. For instance,

create table a(i int check(i>0))
create table a_src(i int)
go
create unique index ai on a(i) with IGNORE_DUP_KEY
go
insert into a_src values(1)
insert into a_src values(1)
insert into a_src values(2)
--insert into a_src values(-1)
go
create trigger a4ins on a
for insert
as
select * from inserted
go
create trigger afterins on a
after insert
as
select * from inserted
go
insert into a select * from a_src
go
drop table a
drop table a_src

I'm getting

i
----
1
2

(2 row(s) affected)

Server: Msg 3604, Level 16, State 1, Procedure a4ins, Line 4
Duplicate key was ignored.
i
----
1
2

(2 row(s) affected)

even the inserted quasi tables are identical.
If I uncomment insert into a_src values(-1), I'm getting

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint
'CK__a__i__58FC18A6'. The conflict occurred in database 'ABC_1COMPEE',
table 'a', column 'i'.
The statement has been terminated.

without any output from either trigger.
So,
in which situations will FOR INSERT be useful while AFTER INSERT won't
do?
in which situations will AFTER INSERT be useful while FOR INSERT won't
do?Ford Desperado (ford_desperado@.yahoo.com) writes:
> I've been reading the docs and playing around, but I'm still not
> getting the difference.

That is because there isn't any!

If memory servers, FOR was the original syntax. I suspect that AFTER
has been added to align with ANSI standards. FOR is not very precise,
where as AFTER tells us that the trigger fires after the statement.
That in difference to BEFORE and INSTEAD OF triggers. (Of which SQL
Server has the latter, but not the former.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks Erlandsql

difference between dbreindex and drop existing clause of create index

Hi all,
Could any one of you tell me if there is any difference
between dbcc dbreindex and create index with drop_existing
option for a table with one clustered index and multiple
non clustered indexes..?
Also, please let me know what is the best way to reduce
fragmentation in tables that do not have non-clustered
indexes...?bharath,
A light view of the differences can be found at:
Rebuilding an Index
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/createdb/cm_8_des_05_271x.asp
For rebuilding several indexes I would stick with DBCC as a general rule.
The create index ... drop_existing requires that you have all the metadata
for the indexes in your script. The DBCC requires you to know nothing more
than the table that you are concerned with. As I understand it, both should
use parallel processing if available.
Russell Fields
"bharath" <barathsing@.hotmail.com> wrote in message
news:88ef01c3b57b$fdcc37d0$a601280a@.phx.gbl...
> Hi all,
> Could any one of you tell me if there is any difference
> between dbcc dbreindex and create index with drop_existing
> option for a table with one clustered index and multiple
> non clustered indexes..?
> Also, please let me know what is the best way to reduce
> fragmentation in tables that do not have non-clustered
> indexes...?

Difference between dates in different rows...

Hi all,

I have a table named Orders and this table has two relevant fields: CustomerId and OrderDate. I am trying to construct a query that will give me the difference, in days, between each customer's order so that the results would be something like: (using Northwind as the example)

...
ALFKI 25/08/1997 03/10/1997 39
ALFKI 03/10/1997 13/10/1997 10
ALFKI 13/10/1997 15/01/1998 94
ALFKI 15/01/1998 16/03/1998 60
ALFKI 16/03/1998 09/04/1998 24
...

At the moment, I have the following query that I think is on the right track:
…
SELECT dbo.Orders.CustomerID, dbo.Orders.OrderDate AS LowDate, Orders_1.OrderDate AS HighDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) AS Difference FROM dbo.Orders INNER JOIN dbo.Orders Orders_1 ON dbo.Orders.CustomerID = Orders_1.CustomerID AND dbo.Orders.OrderDate < Orders_1.OrderDate GROUP BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) ORDER BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate
…

However, this gives me too much data:
…
ALFKI 25/08/1997 03/10/1997 39
ALFKI 25/08/1997 13/10/1997 49
ALFKI 25/08/1997 15/01/1998 143
ALFKI 25/08/1997 16/03/1998 203
ALFKI 25/08/1997 09/04/1998 227
ALFKI 03/10/1997 13/10/1997 10
ALFKI 03/10/1997 15/01/1998 104
ALFKI 03/10/1997 16/03/1998 164
ALFKI 03/10/1997 09/04/1998 188
ALFKI 13/10/1997 15/01/1998 94
ALFKI 13/10/1997 16/03/1998 154
ALFKI 13/10/1997 09/04/1998 178
ALFKI 15/01/1998 16/03/1998 60
ALFKI 15/01/1998 09/04/1998 84
…

So, do any of you have any ideas how I might achieve this? I know how to do it using a stored procedure, but I am trying to avoid that; I’d like to do this in a single query.

Thanks for any help you have to offer,

Regards,

Stephen.

SQL Server 2005:

SELECT a.CustomerID, a.OrderDate as Highdate, b.OrderDate as LowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs

FROM (SELECT CustomerID, OrderDate, ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) as RowNum FROM dbo.Orders) a

INNER JOIN (SELECT CustomerID, OrderDate, (ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) -1)as RowNumMinusOne

FROM dbo.Orders) b ON a.CustomerID=b.CustomerId AND a.RowNum=b.RownumMinusOne

|||

SQL Server 2000:

SELECT a.CustomerID, a.OrderDate as HighDate, b.OrderDate as lowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs FROM (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T.CustomerID and OrderDate < T.OrderDate ) + 1 as Rank1

from Orders as T ) a INNER JOIN (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T1.CustomerID and OrderDate < T1.OrderDate ) as Rank2

from Orders as T1 ) b ON b.CustomerID=a.CustomerID and a.Rank1=b.Rank2

ORDER BY a.CustomerID, a.OrderDate

|||

You're an absolute star! Just what I was after. My head was starting to spin trying to figure this one out.

Thank you for your help!

Regards,

Stephen.

sql

Thursday, March 22, 2012

Difference between 2000 and 2005. Maybe a bug?

I’m having a problem with a piece of sql and the way it is interpreted in SQL
Server 2000 and SQL Server 2005. Here is a table definition:
if not exists
(select 1 from sysobjects where uid = user_id() and type = 'U' and name
= 'rpt_Parameter')
CREATE TABLE rpt_Parameter
(
idrpt_Parameter integer IDENTITY,
idrpt_UserReport integer NOT NULL,
szParameterName nvarchar(255) NOT NULL,
szParameterValue nvarchar(2000) NOT NULL
)
go
if not exists
(select 1 from sysobjects o, information_schema.constraint_table_usage c
where o.uid = user_id() and o.type = 'K' and o.name = 'rpt_Parameter_KEY'
and o.name = c.constraint_name and c.table_name = 'rpt_Parameter' and
c.table_schema = user_name())
ALTER TABLE rpt_Parameter ADD
CONSTRAINT rpt_Parameter_KEY
PRIMARY KEY CLUSTERED (idrpt_Parameter)
Go
As part of a software upgrade we execute the following SQL
Update rpt_parameter
Set szParameterValue = '999999999999'
Where szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
And convert(decimal(16,4),szParameterValue) > 999999999999
GO
In SQL Server 2000 this executed fine. In SQL Server 2005 I get the
following error:
“Error converting data type nvarchar to numeric”
Everywhere in the table where the szParameterName meets the values in the
where clause the values are indeed numeric but there are other entries where
they are not. It seems like the DB engine is not performing that part of the
where before doing the convert which results in the error. I rechecked BOL
and the precedence should be left to right. I’ve tried reworking the
statement several ways even adding an extra IsNumeric check and enclosing the
entire where clause in parenthesis but the result is always the error. I
have been able to get around it by first selecting the correct rows based on
the szParameterName value into an inline table and then joining on that for
the convert but it seems really clumsy to do that.
Could this be a bug? I searched the forum for similar things and did find
one post about a date conversion problem but didn’t find anything else?
Thanks in advance for any help.
Wayne
On Thu, 19 Jan 2006 13:09:04 -0800, Wayne wrote:
(snip)
>As part of a software upgrade we execute the following SQL
>Update rpt_parameter
>Set szParameterValue = '999999999999'
>Where szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
>And convert(decimal(16,4),szParameterValue) > 999999999999
>GO
>In SQL Server 2000 this executed fine. In SQL Server 2005 I get the
>following error:
>Error converting data type nvarchar to numeric
Hi Wayne,
This is not a bug. The optimizer is free to evaluate the conditions in
any order it sees fit, just as long as it doesn't affect the output it
produces. Now you may say that in this case it does affect the output,
but situations like this (with data that may throw errors) are
explicitly exempted from that rule.
A possible workaround is to use a CASE, where the order of evaluation is
guaranteed:
WHERE CASE WHEN szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
THEN convert(decimal(16,4),szParameterValue)
ELSE 0.0
END > 999999999999.0
Hugo Kornelis, SQL Server MVP
|||Hi Hugo,
Thank you very much. I tried you example and it works, but I bet you knew
that :-) I guess I'll have to remember to think about it in that way when
making a where clause that could have invalid output when evaluating it. But
the behavior change between 2000 and 2005 was sure a surprise. Thanks again
for your help.
Wayne
"Hugo Kornelis" wrote:

> On Thu, 19 Jan 2006 13:09:04 -0800, Wayne wrote:
> (snip)
> Hi Wayne,
> This is not a bug. The optimizer is free to evaluate the conditions in
> any order it sees fit, just as long as it doesn't affect the output it
> produces. Now you may say that in this case it does affect the output,
> but situations like this (with data that may throw errors) are
> explicitly exempted from that rule.
> A possible workaround is to use a CASE, where the order of evaluation is
> guaranteed:
> WHERE CASE WHEN szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
> THEN convert(decimal(16,4),szParameterValue)
> ELSE 0.0
> END > 999999999999.0
> --
> Hugo Kornelis, SQL Server MVP
>
sql

Difference between 2000 and 2005. Maybe a bug?

Iâ'm having a problem with a piece of sql and the way it is interpreted in SQL
Server 2000 and SQL Server 2005. Here is a table definition:
if not exists
(select 1 from sysobjects where uid = user_id() and type = 'U' and name
= 'rpt_Parameter')
CREATE TABLE rpt_Parameter
(
idrpt_Parameter integer IDENTITY,
idrpt_UserReport integer NOT NULL,
szParameterName nvarchar(255) NOT NULL,
szParameterValue nvarchar(2000) NOT NULL
)
go
if not exists
(select 1 from sysobjects o, information_schema.constraint_table_usage c
where o.uid = user_id() and o.type = 'K' and o.name = 'rpt_Parameter_KEY'
and o.name = c.constraint_name and c.table_name = 'rpt_Parameter' and
c.table_schema = user_name())
ALTER TABLE rpt_Parameter ADD
CONSTRAINT rpt_Parameter_KEY
PRIMARY KEY CLUSTERED (idrpt_Parameter)
Go
As part of a software upgrade we execute the following SQL
Update rpt_parameter
Set szParameterValue = '999999999999'
Where szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
And convert(decimal(16,4),szParameterValue) > 999999999999
GO
In SQL Server 2000 this executed fine. In SQL Server 2005 I get the
following error:
â'Error converting data type nvarchar to numericâ'
Everywhere in the table where the szParameterName meets the values in the
where clause the values are indeed numeric but there are other entries where
they are not. It seems like the DB engine is not performing that part of the
where before doing the convert which results in the error. I rechecked BOL
and the precedence should be left to right. Iâ've tried reworking the
statement several ways even adding an extra IsNumeric check and enclosing the
entire where clause in parenthesis but the result is always the error. I
have been able to get around it by first selecting the correct rows based on
the szParameterName value into an inline table and then joining on that for
the convert but it seems really clumsy to do that.
Could this be a bug? I searched the forum for similar things and did find
one post about a date conversion problem but didnâ't find anything else?
Thanks in advance for any help.
WayneOn Thu, 19 Jan 2006 13:09:04 -0800, Wayne wrote:
(snip)
>As part of a software upgrade we execute the following SQL
>Update rpt_parameter
>Set szParameterValue = '999999999999'
>Where szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
>And convert(decimal(16,4),szParameterValue) > 999999999999
>GO
>In SQL Server 2000 this executed fine. In SQL Server 2005 I get the
>following error:
>?Error converting data type nvarchar to numeric?
Hi Wayne,
This is not a bug. The optimizer is free to evaluate the conditions in
any order it sees fit, just as long as it doesn't affect the output it
produces. Now you may say that in this case it does affect the output,
but situations like this (with data that may throw errors) are
explicitly exempted from that rule.
A possible workaround is to use a CASE, where the order of evaluation is
guaranteed:
WHERE CASE WHEN szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
THEN convert(decimal(16,4),szParameterValue)
ELSE 0.0
END > 999999999999.0
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thank you very much. I tried you example and it works, but I bet you knew
that :-) I guess I'll have to remember to think about it in that way when
making a where clause that could have invalid output when evaluating it. But
the behavior change between 2000 and 2005 was sure a surprise. Thanks again
for your help.
Wayne
"Hugo Kornelis" wrote:
> On Thu, 19 Jan 2006 13:09:04 -0800, Wayne wrote:
> (snip)
> >As part of a software upgrade we execute the following SQL
> >
> >Update rpt_parameter
> >Set szParameterValue = '999999999999'
> >Where szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
> >And convert(decimal(16,4),szParameterValue) > 999999999999
> >GO
> >
> >In SQL Server 2000 this executed fine. In SQL Server 2005 I get the
> >following error:
> >â'Error converting data type nvarchar to numericâ'
> Hi Wayne,
> This is not a bug. The optimizer is free to evaluate the conditions in
> any order it sees fit, just as long as it doesn't affect the output it
> produces. Now you may say that in this case it does affect the output,
> but situations like this (with data that may throw errors) are
> explicitly exempted from that rule.
> A possible workaround is to use a CASE, where the order of evaluation is
> guaranteed:
> WHERE CASE WHEN szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
> THEN convert(decimal(16,4),szParameterValue)
> ELSE 0.0
> END > 999999999999.0
> --
> Hugo Kornelis, SQL Server MVP
>

Difference between 2000 and 2005. Maybe a bug?

I’m having a problem with a piece of sql and the way it is interpreted in
SQL
Server 2000 and SQL Server 2005. Here is a table definition:
if not exists
(select 1 from sysobjects where uid = user_id() and type = 'U' and name
= 'rpt_Parameter')
CREATE TABLE rpt_Parameter
(
idrpt_Parameter integer IDENTITY,
idrpt_UserReport integer NOT NULL,
szParameterName nvarchar(255) NOT NULL,
szParameterValue nvarchar(2000) NOT NULL
)
go
if not exists
(select 1 from sysobjects o, information_schema.constraint_table_usage c
where o.uid = user_id() and o.type = 'K' and o.name = 'rpt_Parameter_KEY'
and o.name = c.constraint_name and c.table_name = 'rpt_Parameter' and
c.table_schema = user_name())
ALTER TABLE rpt_Parameter ADD
CONSTRAINT rpt_Parameter_KEY
PRIMARY KEY CLUSTERED (idrpt_Parameter)
Go
As part of a software upgrade we execute the following SQL
Update rpt_parameter
Set szParameterValue = '999999999999'
Where szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
And convert(decimal(16,4),szParameterValue) > 999999999999
GO
In SQL Server 2000 this executed fine. In SQL Server 2005 I get the
following error:
“Error converting data type nvarchar to numeric”
Everywhere in the table where the szParameterName meets the values in the
where clause the values are indeed numeric but there are other entries where
they are not. It seems like the DB engine is not performing that part of th
e
where before doing the convert which results in the error. I rechecked BOL
and the precedence should be left to right. I’ve tried reworking the
statement several ways even adding an extra IsNumeric check and enclosing th
e
entire where clause in parenthesis but the result is always the error. I
have been able to get around it by first selecting the correct rows based on
the szParameterName value into an inline table and then joining on that for
the convert but it seems really clumsy to do that.
Could this be a bug? I searched the forum for similar things and did find
one post about a date conversion problem but didn’t find anything else?
Thanks in advance for any help.
WayneOn Thu, 19 Jan 2006 13:09:04 -0800, Wayne wrote:
(snip)
>As part of a software upgrade we execute the following SQL
>Update rpt_parameter
>Set szParameterValue = '999999999999'
>Where szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
>And convert(decimal(16,4),szParameterValue) > 999999999999
>GO
>In SQL Server 2000 this executed fine. In SQL Server 2005 I get the
>following error:
>Error converting data type nvarchar to numeric
Hi Wayne,
This is not a bug. The optimizer is free to evaluate the conditions in
any order it sees fit, just as long as it doesn't affect the output it
produces. Now you may say that in this case it does affect the output,
but situations like this (with data that may throw errors) are
explicitly exempted from that rule.
A possible workaround is to use a CASE, where the order of evaluation is
guaranteed:
WHERE CASE WHEN szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
THEN convert(decimal(16,4),szParameterValue)
ELSE 0.0
END > 999999999999.0
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thank you very much. I tried you example and it works, but I bet you knew
that :-) I guess I'll have to remember to think about it in that way when
making a where clause that could have invalid output when evaluating it. Bu
t
the behavior change between 2000 and 2005 was sure a surprise. Thanks again
for your help.
Wayne
"Hugo Kornelis" wrote:

> On Thu, 19 Jan 2006 13:09:04 -0800, Wayne wrote:
> (snip)
> Hi Wayne,
> This is not a bug. The optimizer is free to evaluate the conditions in
> any order it sees fit, just as long as it doesn't affect the output it
> produces. Now you may say that in this case it does affect the output,
> but situations like this (with data that may throw errors) are
> explicitly exempted from that rule.
> A possible workaround is to use a CASE, where the order of evaluation is
> guaranteed:
> WHERE CASE WHEN szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
> THEN convert(decimal(16,4),szParameterValue)
> ELSE 0.0
> END > 999999999999.0
> --
> Hugo Kornelis, SQL Server MVP
>

difference between 2 columns key and a third-party column with Identity

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,

iTaY.

Natural Key should always be prefered. Assuming that OrderId is the ID which is printed or somewhere used in your order process, this represents the natural key. CustorderID is a artificial key which is not used any further in your process and just there to identify the unique row.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens comments, I would highly discourage allowing a key field to be updated.

|||

Thanks for your time guys, very helpful !

|||Well, expanding Arnie while Contradicting Jens, I'd say that using an Identity column as pk does naturally prevents a key change on DB level.

From indexing point of view, it's likely that scanning a one-column index would be faster, as more index nodes can fit in a page, thus less disk reads should be performed.

From application point of view - it's a lot easier to use an Id column for almost any operation. It allows you to use natural comparing and serializing (ToString) rather than the need to serialize the key somehow, and implement HashCode (which many implement badly) thus simplifying the code, thus, again, increasing maintainability.

just my 0.02￡|||

I do not see the point contradicting me ? I just said, that you should prefer having a "natural" key like the Invoice number rather than an identity column.

difference between 2 columns key and a third-party column with Identity

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,

iTaY.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens comments, I would highly discourage allowing a key field to be updated.

|||

Thanks for your time guys, very helpful !

|||Well, expanding Arnie while Contradicting Jens, I'd say that using an Identity column as pk does naturally prevents a key change on DB level.

From indexing point of view, it's likely that scanning a one-column index would be faster, as more index nodes can fit in a page, thus less disk reads should be performed.

just my 0.02￡|||

I do not see the point contradicting me ? I just said, that you should prefer having a "natural" key like the Invoice number rather than an identity column.

Difference between #table and # # Table?

If i create a ##table using my stored procedure, I can do the select * from ##table

but when i created the #table using the stored procedure and tried to do the select * from #table

I am getting the error message invalid object #table

that means it is going out of scope.

But if i create the ##table, will that table going to remain in the database even until i drop the ##table or it gets dropped when i close the database connection.

Thank you very much.
-RajFrom SQL Server Books OnLine:

There are two types of temporary tables:

Local temporary tables
The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.

Global temporary tables
The names of these tables begin with two number signs (##). These tables are visible to all connections. If the tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects. The association between a task and a table is always dropped when the current statement completes executing; therefore, global temporary tables are usually dropped soon after the connection that created them disconnects.

difference

create table #temp(id int,col1 int,col2 int)
go
select id,col1[Field1],col2[Field2] from #temp
select id,col1 as [Field1],col2 as [Field2] from #temp
go
drop table #temp
go
Is there a difference between these 2 select statements in terms of the
column names and results returned to ado.net client.?
thxAFAIK, no.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||There is no difference. The client will see "id, Filed1, Filed2".
Perayu
"skg" <skg@.yahoo.com> wrote in message
news:uECDpaDTGHA.776@.TK2MSFTNGP09.phx.gbl...
>
> create table #temp(id int,col1 int,col2 int)
> go
> select id,col1[Field1],col2[Field2] from #temp
> select id,col1 as [Field1],col2 as [Field2] from #temp
> go
> drop table #temp
> go
> Is there a difference between these 2 select statements in terms of the
> column names and results returned to ado.net client.?
> thx
>|||Thanks all !!. We have an ado.net application which is working when we use
the As and not [] in the select statement.
I will investigate further and update you if i find something
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uMmP7jDTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> There is no difference. The client will see "id, Filed1, Filed2".
> Perayu
> "skg" <skg@.yahoo.com> wrote in message
> news:uECDpaDTGHA.776@.TK2MSFTNGP09.phx.gbl...
>sql

Thursday, March 29, 2012

Difference between the estimated size and size given by sp_spaceus

Difference between the estimated size and size given by sp_spaceus

Difference between the estimated size and size given by sp_spaceus

difference between table relations and foreign keys?

difference between table relations and foreign keys?

difference between table relations and foreign keys?

difference between SYSTEM_USER and USER

Tuesday, March 27, 2012

Difference Between Physical IOs and Read-Ahead

Difference Between Physical IOs and Read-Ahead

Difference between Matrix and Table

Sunday, March 25, 2012

difference between FOR INSERT and AFTER INSERT triggers

difference between dbreindex and drop existing clause of create index

Difference between dates in different rows...

Thursday, March 22, 2012

Difference between 2000 and 2005. Maybe a bug?

Difference between 2000 and 2005. Maybe a bug?

Difference between 2000 and 2005. Maybe a bug?

difference between 2 columns key and a third-party column with Identity

difference between 2 columns key and a third-party column with Identity

Difference between #table and # # Table?

difference

sql record counts

Blog Archive

About Me