I've been attempting to search for some documentation on how computer
columns are computed. Firstly, does anyone have any?
If not, can any tell me at which point to column is computed? Is it
computer at time of viewing, or is it computer as soon as the row is
created?
Thanks in advance.
JonJonathan,
A computed column is not stored on disk unless an index is created on
it. So, it will be calculated at query time if there is no index.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jonathan Martin wrote:
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>|||This is a multi-part message in MIME format.
--=_NextPart_000_001A_01C50917.F5D63080
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I have a very basic percentage calculation running, so the overhead =shouldn't be too much.
Thanks very much for the speedy help.
Jon
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message =news:uXVKWZRCFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Jonathan,
> > A computed column is not stored on disk unless an index is created on > it. So, it will be calculated at query time if there is no index.
> > -- > Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> > Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
> > > Jonathan Martin wrote:
>> Hi all,
>> >> I've been attempting to search for some documentation on how computer =
>> columns are computed. Firstly, does anyone have any?
>> >> If not, can any tell me at which point to column is computed? Is it >> computer at time of viewing, or is it computer as soon as the row is >> created?
>> >> Thanks in advance.
>> >> >> Jon
>> --=_NextPart_000_001A_01C50917.F5D63080
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I have a very basic percentage =calculation running, so the overhead shouldn't be too much.
Thanks very much for the speedy help. =
Jon
"Mark Allison"
--=_NextPart_000_001A_01C50917.F5D63080--|||Jonathan,
Have a look at the query plan for more information on what SQL Server is
doing. Try putting an index on it and see if it improves things. Just
play around if you have the time and see what works best. For large
result sets, an index can really help here.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jonathan Martin wrote:
> I have a very basic percentage calculation running, so the overhead
> shouldn't be too much.
> Thanks very much for the speedy help.
>
> Jon
> "Mark Allison" <mark@.no.tinned.meat.mvps.org
> <mailto:mark@.no.tinned.meat.mvps.org>> wrote in message
> news:uXVKWZRCFHA.3728@.TK2MSFTNGP14.phx.gbl...
> > Jonathan,
> >
> > A computed column is not stored on disk unless an index is created on
> > it. So, it will be calculated at query time if there is no index.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602m.html
> >
> >
> > Jonathan Martin wrote:
> >> Hi all,
> >>
> >> I've been attempting to search for some documentation on how computer
> >> columns are computed. Firstly, does anyone have any?
> >>
> >> If not, can any tell me at which point to column is computed? Is it
> >> computer at time of viewing, or is it computer as soon as the row is
> >> created?
> >>
> >> Thanks in advance.
> >>
> >>
> >> Jon
> >>
> >>|||If you do find that the calculation is expensive, AND not used by many
folks, either ensure people ONLY request the column when they actually need
it, (instead of Select * ) , OR put it in a view, and call the view only
when you need the extra computations.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:uqKdNCRCFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>|||A computed column would be NO DIFFERENT than a computed value in an Ad-Hoc
query, T-SQL stored procedure, or SQL Server view. The difference is where
the computed definition resides. The performance impact/gains would be
identical. What is different is that when either in a VIEW or a COMPUTED
COLUMN on base table is that you CAN put an index on it and, thus, have it
materialized. However, although this will alleviate the computation at
SELECT time, it will have overhead at INSERT and UPDATE time.
Indexes on Computed columns are a performance trade-off between SELECT and
CRUD times. So, it would depend on the primary purpose of the value. If
only queried sporatically for limited sets of rows, either a computed column
or view definition may make sense. If it covers the entire table and is
queried often, then either an INDEXED COMPUTED COLUMN or an INDEXED VIEW
with an index on the column within the VIEW may make more sense.
Sincerely,
Anthony Thomas
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eTJqArSCFHA.1404@.TK2MSFTNGP11.phx.gbl...
If you do find that the calculation is expensive, AND not used by many
folks, either ensure people ONLY request the column when they actually need
it, (instead of Select * ) , OR put it in a view, and call the view only
when you need the extra computations.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:uqKdNCRCFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've been attempting to search for some documentation on how computer
> columns are computed. Firstly, does anyone have any?
> If not, can any tell me at which point to column is computed? Is it
> computer at time of viewing, or is it computer as soon as the row is
> created?
> Thanks in advance.
>
> Jon
>
No comments:
Post a Comment