Hi,
Is there a way to calculate the Default Value of a column with other data
columns?  Using "date_of_birth" column and "screen_date" columns, I would
like to calculate the "age" automatically with the Default Value definition.
Please let me know if it's possible.

YC

Why would you need to store this data?  If you have 2 in one column and 2 in
another column, do you really need to store 4 in a "sum" column, when you
can do this in a view or procedure?

Anyway, I think you want to use a computed column, not a default value.
E.g.:

CREATE TABLE dbo.MyTable
(
    db SMALLDATETIME,
    sd SMALLDATETIME,
    age_in_days AS CONVERT(INT, DATEDIFF(DAY, db, sd))
)
GO

SET NOCOUNT ON
INSERT dbo.MyTable(db, sd) SELECT '20050101', GETDATE())
INSERT dbo.MyTable(db, sd) SELECT '20050501', '20050505')
SELECT * FROM dbo.MyTable
GO

DROP TABLE dbo.MyTable
GO

Aaron,

sometimes we do that to speed queries up.
For instance, if there are columns order_date and shipment_date,
turnaround (days between ordering and shipment) and shipment_month can
be calculated no problem. But if there are many frequently running
queries involving these expressions, such as

select ...
where turnaround>10

select sum(amount), shipment_month, turnaround
from orders
group by shipment_month, turnaround

it really helps to create an index on (turnaround, shipment_month,
amount)

Makes sense?

Yes, of course.  I was asking if the OP really needed to do it, I don't
recall saying, "don't do that, it's stupid and doesn't make sense."

I agree that this is not the way to go.  It would be best to calculate
it in a view.  BTW, sometimes age is static.  For example a patient's
age is that at the time of admission.  If you really need a separate
field for this, you could create a simple trigger.

Hi

It is not clear why you would want to store the age in this way. Current Age
would be SELECT DATEDIFF(yy,dob, getdate()) which could be added to an
INSTEAD OF  trigger. If you want to have a more up-to-date age you can use
the function in a view e.g.

CREATE VIEW vw_Users AS
SELECT Name, dob, DATEDIFF(yy,dob, getdate()) AS Age
FROM tbl_users

SELECT Name, dob, age
FROM vw_Users

John
Show quoteHide quote

John's formula does not break on the birthday.  It will tell you how old
someone will be at the end of a year.

Aaron's formula can be tweaked to provide a person's age as of today.  It
can be put into a computed column as Aaron has suggested or in a user-defined
function or stored procedure.

CONVERT(INT, DATEDIFF(DAY, dob, getdate()) / 365.25) as age

Personlly, I would not store the age in a table because it's subject to
change each day.  Or, if you need what year it is for the person (John's
formula) then you have to refresh the table data once per year.

Hope that helps,
Joe

Show quoteHide quote

Joe

As people are born on different days, you will need to refresh it every day
(just in case!)!

John

Show quoteHide quote

I might have misread these posts.

A computed column is persisted only when required, for instance at the time
of use on the select clause.

Its not calculated and stored, unless its value is deterministic in which
case you would index and only then is the value persisted.

Its a lot more efficient and easier to do these age things based on timenow
using a computed column rather than a view.

Show quoteHide quote

not necessarily. For instance, Age of suspect _at the time of arrest_
does not change ever. I'm not maikng it up, I've worked on such a
system.

Hi

There are several things here that would be alot clearer if the OP would
have given more details. Age_on_date_off_arrest would not need to be
refreshed at all as it is for a fixed point in time (regardless of the method
used), current_age would need to be refreshed daily if it was materialise
(e.g calculated in a trigger inserted into the table), Tony's suggestion of a
computed column or if a view was used would not need a refresh for either
type of age.

John

Show quoteHide quote

Age changes from day to day, so put the calculation in a VIEW.  Do not
store  computed data.

You can use a computed column which would save all the hassle of a view, the
development, maintanence of it - remembering to update the view when the
number of columns on the table changes etc...

The computed column is not persisted until its required via the query - go
read bol.

Show quoteHide quote

Hi There,

Very informative!
So does this help!

SCHEMABINDING

Binds the view to the schema. When SCHEMABINDING is specified, the
select_statement must include the two-part names (owner.object) of
tables, views, or user-defined functions referenced.

Views or tables participating in a view created with the schema binding
clause cannot be dropped unless that view is dropped or changed so that
it no longer has schema binding. Otherwise, SQL Server raises an error.
In addition, ALTER TABLE statements on tables that participate in views
having schema binding will fail if these statements affect the view
definition.


With Warm regards
Jatinder Singh

My point was not to use a view because of the additional work and
maintanence required; using schema binding adds even more maintanence to
your database.

It's really best to just use a computed column when you require this type of
thing - its in one place, less maintanence and less complexity.

Tony

Show quoteHide quote

I know about the proprietary and limited computed column syntax.  I
simply prefer the standard, portable and more powerful VIEW syntax.

Not everyone writes "job security" code.  A few keystrokes and a cut &
paste are not that big a hassle.

That just goes to show how out of touch you really are.

Computed columns offer a very powerful and less complex approach to
encapsulating data, using views often leads to confusion and logic
problems - with schema changes you have to remember to change the view, you
need to consider more complex permissioning; there is more testing required
because of the extra components.

Very few people write portable code, people want to get the most out of
their investment and if that means using efficient, scalable fast
properitetary extensions then so be it.

Consider the FOR XML extensions that now allow you do concatenate a set of
rows in a single statement very easily and concisely, no longer with the
need for cursors or complex joins - would you really tell your client to use
the old, out-dated, slow standard approach or give them an efficient
scalable workable solution?

Show quoteHide quote

encapsulating data, using views often leads to confusion and logic
problems <<

Actually, it is weaker than VIEWs -- I cannot use a JOIN or a WITH CHECK
OPTION on a computed column. 

need to consider more complex permissioning; there is more testing
required because of the extra components. <<

Sounds like the sort of things I would like to validate in my code when
I have a base table change.  But we seem to have very different
programming styles..

of their investment and if that means using efficient, scalable fast
properitetary extensions then so be it. <<

Again, only "cowboy coders" looking for job security and/or the
programmres who only know one dialect that they are ignorant of the
language. 

Also "scalable" and "properitetary" do not go together becuase you are
limited to one product's niche. That applies all directions -- VLDB to
OLTP to OLAP to streaming data.  But Standard code runs across them all.

set of rows in a single statement very easily and concisely, no longer
with the need for cursors or complex joins - would you really tell your
client to use
the old, out-dated, slow standard approach or give them an efficient
scalable workable solution? <<

What a wonderful way to do reports in the database!  What a wonderful
way to keep Bill Gates rich because you will be locked into SQL Server
and can never move to Open Source, Linux, mainframes, Z/os, etc.

"Caesar: Pardon him, Theodotus.  He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898.

--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications. 


*** Sent via Developersdex http://www.developersdex.com ***

--CELKO-- (remove.jcelko***@earthlink.net) writes:
You can use a computed column in a join alright.

And WITH CHECK OPTION is not applicable here. WITH CHECK OPTION says
that you cannot modify a row out of a view from the view. Since a
computed column is only part of a row, you can modify away the column
itself.

Which for most serious system is an extremely large investment anyway.
Certainly not a Codd. But a herring. Red.

If I have a WITH CHECK OPTION or a CHECK() in a VIEW, what is supposed
to happen when one fails?

It gets complicated fast!!

--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications. 


*** Sent via Developersdex http://www.developersdex.com ***

Why on earth would you want to put a check constraint in a view - not that
you can.

Exactly a reason for using a computed column - doh!

Show quoteHide quote

You've completely missed the point and what a computed column is - go back
and read the product specification (books online).

The CHECK constraint is on the real column.

You can use computed columns in a join - again, go back and rtfm.

We definitely have different styles, I go for simple concise programming
that is easily maintained.

You can validate a computed column a lot easier - if you add a new column
that does not affect the computed column you don't need to go revisit a ton
of existing code that you then have to go retest.

Again you show a complete lack of experience of business and industrial
application of Information Technology.

"Cowboy coders" spin out their contract by extending what actually really
needs to be done, its like going into a garage and asking for a tyre change
and then coming out with a tyre change, sun-roof and different coloured car.

Scalable and propriterary don't go together, but scalable and portable
defeinitely do not go together, in fact its worse - in order to get
portabilty you have to write standard sql and you have to modify each
product solution according to the product behaviour, for instance default
locking isolation - consider oracle and sql server differences.

Microsoft have listened to their customers and implemented propertierary
features that those customers need to gain a more efficienent deevelopment
experience or solution - I cannot say the same for the standards committee,
mind you it has got a lot better in the past 5 years. Perhaps a couple of
personalities hell bent on holding the industry back had too much
influence - they seem to have gone now thank goodness.

FOR XML is not a reporting language - I'm embarassed you even think that -
go catch up on XML and what the rest of the industry has been doing in the
past 10 years.

If you have made your investment in SQL Server why on earth would you want
to move to another product? As a buiness you must get as much value for
money from your investment as you can, that is especially true in the
current commercial environment - just check the retail sector, engineering
etc... Your ideas are just so out-of-date, do yourself a favour - do some
research and update your canned answers.

Are you really telling us that instead of writing 5 lines of code to do a
set based concatenation you'd write cursors and dozens?

Show quoteHide quote

Deadlock updating different rows
No retrieving dupicate rows
Service Broker - BEGIN DIALOG CONVERSATION @dialog_handle
Performance
Would like an Exists in a Case statement