Computed column

I'm a newbie to SQL Server. I'm having a philosophical debate with myself
about the virtues/pitfalls of a computed column.
I was always under the impression you should not have a calculated field in
a table. Yet here I can enter a formula to create a client "FullName" field
with the formula ([InsAnnLastName] + ', ' + [InsAnnFirstName]) in the table.
Does this mean this is now acceptable practice? Any gotchas lurking around
with this?
Consider it a "feature", to be used if you wish. You still have the option to present calculated
values through views, for instance. For special cases, calculated columns can be interesting, but
generally, I don't like to "litter" my table structures with calculated information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dkline" <Dkline001@.comcast.net> wrote in message news:%23598C$bGFHA.3112@.tk2msftngp13.phx.gbl...
> I'm a newbie to SQL Server. I'm having a philosophical debate with myself about the
> virtues/pitfalls of a computed column.
> I was always under the impression you should not have a calculated field in a table. Yet here I
> can enter a formula to create a client "FullName" field with the formula ([InsAnnLastName] + ', '
> + [InsAnnFirstName]) in the table.
> Does this mean this is now acceptable practice? Any gotchas lurking around with this?
>
|||That denormalizes your database, and isn't considered good practice. You're
storing the same data twice, taking up twice the space... what advantage
are you trying to gain by doing this?
"Dkline" <Dkline001@.comcast.net> wrote in message
news:%23598C$bGFHA.3112@.tk2msftngp13.phx.gbl...
> I'm a newbie to SQL Server. I'm having a philosophical debate with myself
> about the virtues/pitfalls of a computed column.
> I was always under the impression you should not have a calculated field
> in a table. Yet here I can enter a formula to create a client "FullName"
> field with the formula ([InsAnnLastName] + ', ' + [InsAnnFirstName]) in
> the table.
> Does this mean this is now acceptable practice? Any gotchas lurking around
> with this?
>
|||FWIW,

> ... You're storing the same data twice, taking up twice the space...
A calculated column doesn't consume disk space unless you explicitly create an index over the
calculated column. Unless of course you actually store the calculated *value* in a regular column
instead of defining a formula for the column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael C#" <xyz@.yomomma.com> wrote in message news:uHGO2LcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> That denormalizes your database, and isn't considered good practice. You're storing the same data
> twice, taking up twice the space... what advantage are you trying to gain by doing this?
> "Dkline" <Dkline001@.comcast.net> wrote in message news:%23598C$bGFHA.3112@.tk2msftngp13.phx.gbl...
>
|||At least it is a "documented" feature. It's those "undocumented features"
that drive me crazy.
To me a table stores data. What you do with the data belongs in Views,
Reports, etc.
However my coworkers wanted a way where we could have the client's full name
in the table. Ultimately it was wanted for use in Crystal Reports. So they
are happy campers now.
I'm a little less than happy as I fear I may have created a monster.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usEebJcGFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Consider it a "feature", to be used if you wish. You still have the option
> to present calculated values through views, for instance. For special
> cases, calculated columns can be interesting, but generally, I don't like
> to "litter" my table structures with calculated information.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dkline" <Dkline001@.comcast.net> wrote in message
> news:%23598C$bGFHA.3112@.tk2msftngp13.phx.gbl...
>
|||You're right. I mis-read and thought he was storing the results of the
calculation. I'm still trying to see the advantage of concatenating the
last and first names on the server in this fashion. It doesn't seem like it
would be all that useful...
Thx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uNhu$OcGFHA.2132@.TK2MSFTNGP14.phx.gbl...
> FWIW,
>
> A calculated column doesn't consume disk space unless you explicitly
> create an index over the calculated column. Unless of course you actually
> store the calculated *value* in a regular column instead of defining a
> formula for the column.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:uHGO2LcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
>
|||Never mind, I just saw his follow-up message on why he's doing it that way.
Thx
"Michael C#" <xyz@.yomomma.com> wrote in message
news:%233$aSwcGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> You're right. I mis-read and thought he was storing the results of the
> calculation. I'm still trying to see the advantage of concatenating the
> last and first names on the server in this fashion. It doesn't seem like
> it would be all that useful...
> Thx
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uNhu$OcGFHA.2132@.TK2MSFTNGP14.phx.gbl...
>
|||Why not have your coworkers use a view instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dkline" <Dkline001@.comcast.net> wrote in message news:%23uz%23yQcGFHA.2524@.TK2MSFTNGP15.phx.gbl...
> At least it is a "documented" feature. It's those "undocumented features" that drive me crazy.
> To me a table stores data. What you do with the data belongs in Views, Reports, etc.
> However my coworkers wanted a way where we could have the client's full name in the table.
> Ultimately it was wanted for use in Crystal Reports. So they are happy campers now.
> I'm a little less than happy as I fear I may have created a monster.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:usEebJcGFHA.2936@.TK2MSFTNGP15.phx.gbl...
>
|||P.S. - If one of your values in the computation is NULL, for instance if
[InsAnnLastName] for one record is NULL, the computed value of
([InsAnnLastName] + ', ' + [InsAnnFirstName]) will be NULL for that record.
"Dkline" <Dkline001@.comcast.net> wrote in message
news:%23uz%23yQcGFHA.2524@.TK2MSFTNGP15.phx.gbl...
> At least it is a "documented" feature. It's those "undocumented features"
> that drive me crazy.
> To me a table stores data. What you do with the data belongs in Views,
> Reports, etc.
> However my coworkers wanted a way where we could have the client's full
> name in the table. Ultimately it was wanted for use in Crystal Reports. So
> they are happy campers now.
> I'm a little less than happy as I fear I may have created a monster.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:usEebJcGFHA.2936@.TK2MSFTNGP15.phx.gbl...
>