Calculated field

can I create a field whose values will be derived from
other fields in the table without writing a stored proc or
script? For example if I have a table called Salary with
three fields: hours, rate and GrossPay. I want the
grosspay field to be updated automatically if values have
been provided for the hours and rate fields. Is this
feasible?
Thanks for your help in advance.
This is called a computed column. The values are not stored but are
calculated when a result set is requested. The syntax is documented under
the the CREATE TABLE and ALTER TABLE commands in BOL (Books On-Line).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible?
> Thanks for your help in advance.
|||lala,
I believe you can use a trigger to accomplish what you are asking. in the
BOL navigate to 'create trigger', and you can look at some examples there.
essentially, every time someone updates those columns, the trigger should be
able to poulate the third column.
hth
"lala" wrote:

> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible?
> Thanks for your help in advance.
>
|||In addition to the other posts: consider having a view where you define the calculated columns and
use that view. This way you don't have to "litter" your tables with calculated values.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible?
> Thanks for your help in advance.

Calculated field

can I create a field whose values will be derived from
other fields in the table without writing a stored proc or
script? For example if I have a table called Salary with
three fields: hours, rate and GrossPay. I want the
grosspay field to be updated automatically if values have
been provided for the hours and rate fields. Is this
feasible'
Thanks for your help in advance.This is called a computed column. The values are not stored but are
calculated when a result set is requested. The syntax is documented under
the the CREATE TABLE and ALTER TABLE commands in BOL (Books On-Line).
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.|||lala,
I believe you can use a trigger to accomplish what you are asking. in the
BOL navigate to 'create trigger', and you can look at some examples there.
essentially, every time someone updates those columns, the trigger should be
able to poulate the third column.
hth
"lala" wrote:
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.
>|||In addition to the other posts: consider having a view where you define the calculated columns and
use that view. This way you don't have to "litter" your tables with calculated values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.sql

cacheRemove

I have a stored proc with several input parameters and it runs very efficient
(14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
when drastically changing the parameters. I run the same stored proc on my
production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up to
15 minutes. The CPU usage on the production server is very low. When we ran a
trace on production we found that it was removing the cache and then of
course the cache would be missing and it would recompile - sometimes up to 10
or 15 times through the execution of the store proc. It doesn't do the
recompiling on the dev server. We feel certain that there must be a
difference in the configuration of either SQL server between the servers or
Windows. We aren't sure where to start looking.
It might be that the connection in which you are running the sp has
different enviorment settings than the dev connection. Run a profile trace
on both with the existing connection and connection Login to see if they are
the same. The sp can be poorly written and force recompilation as well. Do
you have temp tables in it? Have a look here:
http://support.microsoft.com/default.aspx?kbid=243586
Andrew J. Kelly SQL MVP
"James" <James@.discussions.microsoft.com> wrote in message
news:76AECD82-262E-428C-B0D9-E03A9FC49BCD@.microsoft.com...
>I have a stored proc with several input parameters and it runs very
>efficient
> (14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
> when drastically changing the parameters. I run the same stored proc on my
> production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up
> to
> 15 minutes. The CPU usage on the production server is very low. When we
> ran a
> trace on production we found that it was removing the cache and then of
> course the cache would be missing and it would recompile - sometimes up to
> 10
> or 15 times through the execution of the store proc. It doesn't do the
> recompiling on the dev server. We feel certain that there must be a
> difference in the configuration of either SQL server between the servers
> or
> Windows. We aren't sure where to start looking.

cacheRemove

I have a stored proc with several input parameters and it runs very efficient
(14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
when drastically changing the parameters. I run the same stored proc on my
production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up to
15 minutes. The CPU usage on the production server is very low. When we ran a
trace on production we found that it was removing the cache and then of
course the cache would be missing and it would recompile - sometimes up to 10
or 15 times through the execution of the store proc. It doesn't do the
recompiling on the dev server. We feel certain that there must be a
difference in the configuration of either SQL server between the servers or
Windows. We aren't sure where to start looking.It might be that the connection in which you are running the sp has
different enviorment settings than the dev connection. Run a profile trace
on both with the existing connection and connection Login to see if they are
the same. The sp can be poorly written and force recompilation as well. Do
you have temp tables in it? Have a look here:
http://support.microsoft.com/default.aspx?kbid=243586
--
Andrew J. Kelly SQL MVP
"James" <James@.discussions.microsoft.com> wrote in message
news:76AECD82-262E-428C-B0D9-E03A9FC49BCD@.microsoft.com...
>I have a stored proc with several input parameters and it runs very
>efficient
> (14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
> when drastically changing the parameters. I run the same stored proc on my
> production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up
> to
> 15 minutes. The CPU usage on the production server is very low. When we
> ran a
> trace on production we found that it was removing the cache and then of
> course the cache would be missing and it would recompile - sometimes up to
> 10
> or 15 times through the execution of the store proc. It doesn't do the
> recompiling on the dev server. We feel certain that there must be a
> difference in the configuration of either SQL server between the servers
> or
> Windows. We aren't sure where to start looking.

cacheRemove

I have a stored proc with several input parameters and it runs very efficien
t
(14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
when drastically changing the parameters. I run the same stored proc on my
production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up to
15 minutes. The CPU usage on the production server is very low. When we ran
a
trace on production we found that it was removing the cache and then of
course the cache would be missing and it would recompile - sometimes up to 1
0
or 15 times through the execution of the store proc. It doesn't do the
recompiling on the dev server. We feel certain that there must be a
difference in the configuration of either SQL server between the servers or
Windows. We aren't sure where to start looking.It might be that the connection in which you are running the sp has
different enviorment settings than the dev connection. Run a profile trace
on both with the existing connection and connection Login to see if they are
the same. The sp can be poorly written and force recompilation as well. Do
you have temp tables in it? Have a look here:
http://support.microsoft.com/default.aspx?kbid=243586
Andrew J. Kelly SQL MVP
"James" <James@.discussions.microsoft.com> wrote in message
news:76AECD82-262E-428C-B0D9-E03A9FC49BCD@.microsoft.com...
>I have a stored proc with several input parameters and it runs very
>efficient
> (14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
> when drastically changing the parameters. I run the same stored proc on my
> production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up
> to
> 15 minutes. The CPU usage on the production server is very low. When we
> ran a
> trace on production we found that it was removing the cache and then of
> course the cache would be missing and it would recompile - sometimes up to
> 10
> or 15 times through the execution of the store proc. It doesn't do the
> recompiling on the dev server. We feel certain that there must be a
> difference in the configuration of either SQL server between the servers
> or
> Windows. We aren't sure where to start looking.

C# Command Timestamp Input Parameter

I have a stored proc that inserts a customer and it expects a timestamp input parameter. I dont know what a timestamp datatype is for sql 2005 and Ive tried to parse all sorts of data types but the proc errors out saying it needs "Byte[]" which Ive tried. Can anyone help me with this? Thanks

Ryan

Have you tried the plain DateTime data type?|||

Hi Ryan,

The timestamp in SQL Server maps to a Byte[] type in .NET framework. It is not used as DateTime. It is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows.

If you just need to store date/time information, try to use datetime data type, it maps to a DateTime object in .NET framework.

Please check the following link for more information:

http://msdn2.microsoft.com/en-us/library/ms182776.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconmappingnetdataproviderdatatypestonetframeworkdatatypes.asp

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

business logic in Stored Proc VS aspx.vb page

Hello,

I stuck in a delimma.
Where to put the business logic that involves only one update
but N number of selects from N tables......with N where conditionsHi

In general I would put this in the middle tier or the database. Allowing
direct access to tables from a client would raise security issues and make
it less managable.

John

"A.V.C." <yhspl_softwaregroup@.hotmail.com> wrote in message
news:d28fa5d0.0407060443.2797e77@.posting.google.co m...
> Hello,
> I stuck in a delimma.
> Where to put the business logic that involves only one update
> but N number of selects from N tables......with N where conditions|||A.V.C. (yhspl_softwaregroup@.hotmail.com) writes:
> I stuck in a delimma.
> Where to put the business logic that involves only one update
> but N number of selects from N tables......with N where conditions

I am of the school that puts as much as possible of the business logic
in the stored procedures. The idea is to put the logic where the data is.
If you put logic in the middle tier, you may have a lot network traffic.

The one case where the middle layer is a better place, is when you
have computations that are very intensive on reports. Then you can
take off load from SQL Server, and you can more easilyu scale out.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns951EF3408DA66Yazorman@.127.0.0.1...
> A.V.C. (yhspl_softwaregroup@.hotmail.com) writes:
> > I stuck in a delimma.
> > Where to put the business logic that involves only one update
> > but N number of selects from N tables......with N where conditions
> I am of the school that puts as much as possible of the business logic
> in the stored procedures. The idea is to put the logic where the data is.
> If you put logic in the middle tier, you may have a lot network traffic.

Which would not have otherwise existed if the logic is internal
to the database stored procedure.

> The one case where the middle layer is a better place, is when you
> have computations that are very intensive on reports. Then you can
> take off load from SQL Server, and you can more easilyu scale out.

Where separate pre-processing (ie: regeneration of the computed
values) into a work table is feasible, then this can allow the logic to
be retained in SQL because the realtime computations have been
reduced.|||THanx
Could you pls elaborate the scenario where middle tier is ideal for business logic ?

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns951EF3408DA66Yazorman@.127.0.0.1>...
> A.V.C. (yhspl_softwaregroup@.hotmail.com) writes:
> > I stuck in a delimma.
> > Where to put the business logic that involves only one update
> > but N number of selects from N tables......with N where conditions
> I am of the school that puts as much as possible of the business logic
> in the stored procedures. The idea is to put the logic where the data is.
> If you put logic in the middle tier, you may have a lot network traffic.
> The one case where the middle layer is a better place, is when you
> have computations that are very intensive on reports. Then you can
> take off load from SQL Server, and you can more easilyu scale out.|||A.V.C. (yhspl_softwaregroup@.hotmail.com) writes:
> Could you pls elaborate the scenario where middle tier is ideal for
> business logic ?

Permit me to take an example from the system I work with, which is abour
securities trading. When a deal comes in there are a couple of computations
to carry out give the basics: price and the quantity. Most of these
computations are simple: price * qty gives your the purchase amount, and
then you compute charges. And to compute charges you require access to
data, because the charges depends on the customer and instrument and this
is information that is in several tables.

The one exception here is when you trade with bonds, because they may be
traded on interest rather than price, in which case the price has to be
computed from the interest. You also have to compute the accrued interest
for the bond, that the seller is to pay to the buyer. These computations
are not very simple to implement in SQL. What we do is that we call a
COM object that runs on the SQL Server machine that performs the
computation, so we still have this logic on the server.

Our customers have fairly modest volume of bond deals, so this is not an
issue. But assume that you have a site that trades exclusively in bonds,
and can perform many trades a second (not very likely, I think). In this
case, having the COM object on the server would take some toll that would
be bearable. We could make the COM object remote, but it would still be
one server. If instead the middle tier would get all trades to compute,
there could be several machines that each gets their load, so you can scale
better.

It's maybe not the best example, but I think it gives you the idea that
even if you put some logic in the middle tier, it may not be all logic,
but only some specific part.

Finally one more argument for having the logic in stored procedures: you
have all the code in one place. If you use the middle tier, you will jump
forth and back in different languages and the code is more difficult to
follow, debug and maintain.

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

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