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...
>> 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?
>|||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...
>> 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?
>|||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,
>> ... 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...
>> 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?
>>
>|||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...
>> 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...
>> 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?
>>
>>
>|||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...
>> 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?
>>
>|||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...
>> 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?
>>
>|||Once again virtue is triumphant.
We settled on adding the FullName field to the table but it will not be a
computed column. Instead a small piece of VBA was added to the form and gets
the FirstName, LastName, etc., accounts for any NULLS, glues the pieces
together and puts the calculated value into the table.
It was that or do an update query. Either way - no calculated column.
Thank you both for your wisdom.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:%23zm6rycGFHA.2588@.TK2MSFTNGP09.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...
>> 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...
>> 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?
>>
>>
>>
>