473,587 Members | 2,579 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Computed Column calculations

If i want to split a computed column into two or more columns based on
the the length (its a varchar) of the computed column, how often will
sql server determine what the computed column is? roughly along the
lines of

select comp_col,'comp_ col 2'=
case when datalength(comp _col)<=100 then comp_col
else left(comp_col,9 9) + '~' end,
datalength(comp _col)
from aTable

As you can see, in this scenario we use the computed coulumn,
comp_col, in a few places, so does SQL server need to calculate this
each time? I'm playing with this on the basis that it does and thus
trying to shift the computed column out to a variable and then
manipulte and return from their, but that has its own problems when
you throw in additional parameters (trying to join table udf's) so if
SQL server is smart enough to not calculate the column each time I
would save a lot of hassle?

Cheers Dave
Jul 23 '05 #1
9 3673
DMAC (dr***@drmcl.fr ee-online.co.uk) writes:
If i want to split a computed column into two or more columns based on
the the length (its a varchar) of the computed column, how often will
sql server determine what the computed column is? roughly along the
lines of

select comp_col,'comp_ col 2'=
case when datalength(comp _col)<=100 then comp_col
else left(comp_col,9 9) + '~' end,
datalength(comp _col)
from aTable

As you can see, in this scenario we use the computed coulumn,
comp_col, in a few places, so does SQL server need to calculate this
each time? I'm playing with this on the basis that it does and thus
trying to shift the computed column out to a variable and then
manipulte and return from their, but that has its own problems when
you throw in additional parameters (trying to join table udf's) so if
SQL server is smart enough to not calculate the column each time I
would save a lot of hassle?


Unless you are calling a scalar UDF in the expression for the computed
column, I would not be too worried. While there probably is some overhead,
it pales in comparison with time for disk access etc.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Adding storage for a computed column requires negligible resources,
especially wrt disk access. However, SQL Server imposes restrictions
on how computed columns can be used. Thus, I don't see the value of
using computed columns - you are accepting denormalization by creating
the computed column in the first place, why not take it one efficient
step further?

Jul 23 '05 #3
(ma**@msn.com) writes:
Adding storage for a computed column requires negligible resources,
especially wrt disk access. However, SQL Server imposes restrictions
on how computed columns can be used. Thus, I don't see the value of
using computed columns - you are accepting denormalization by creating
the computed column in the first place, why not take it one efficient
step further?


You can have it both ways: you can materialize your computed column
by adding an index on it.

If you stay with plain vanilla columns, and have a column of which the
value is derived from other columns, you will have to compute that
value in a trigger. This can have some overhead when inserting data.
(More overhead than of an indexed computed columns.)

Myself, I have used computed columns only very occassionally. Once
it was a fairly complex expression for an important test. Another case
it was a simple forumula, but the value in question is essential and
queried in several places. And before I reworked this data, there was
such a column - non-computed - in another table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
Unfortunatley, in this instance I do use UDF's in the computed column,
Actually I use one which calls three more, the third of which uses a
cursor to make have a dozen logical if's on some data to see wether the
data should be in front of or after another value, be prefixed by or
suffixed by something else which makes it dead slow anyway. The problem
is obviously the business logic that says a bunch of rules needs to be
applied to generate an items description. This description can have from
1 to 80 values which need all the rules applied to each one, hence the
cursor, hence the UDF, hence the headache. Now I need to substitute some
replacements to words inside this finished description which comes from
, yup another cursor to rattle thru a few thousand 'potential'
replacement to check for a match. Eventually I want to know if the
computed description, after the replacements have been applied, is
bigger than 100 chars and get a list. So far I've narrowed it down to 20
hours for 125,000 line items. But the potential replacemens are growing
rapidly and hurting so I'm not too happy a bunny right now.

Dave

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 23 '05 #5
an*******@devde x.com (an*******@devd ex.com) writes:
Unfortunatley, in this instance I do use UDF's in the computed column,
Actually I use one which calls three more, the third of which uses a
cursor to make have a dozen logical if's on some data to see wether the
data should be in front of or after another value, be prefixed by or
suffixed by something else which makes it dead slow anyway. The problem
is obviously the business logic that says a bunch of rules needs to be
applied to generate an items description. This description can have from
1 to 80 values which need all the rules applied to each one, hence the
cursor, hence the UDF, hence the headache. Now I need to substitute some
replacements to words inside this finished description which comes from
, yup another cursor to rattle thru a few thousand 'potential'
replacement to check for a match. Eventually I want to know if the
computed description, after the replacements have been applied, is
bigger than 100 chars and get a list. So far I've narrowed it down to 20
hours for 125,000 line items. But the potential replacemens are growing
rapidly and hurting so I'm not too happy a bunny right now.


Now, does that sound ugly or what?

In this case, I would consider materializing the column. It doesn't sound
like this would be possible by indexing the column, so you would have a
plain normal column, and the update from a trigger.

Of course, that would incur an overhead when inserting rows to the
table. And that the components that make up this ugly beast are somewhat
static.

In the short term, this may not be possible. But it may be a good idea
to get all the values that you need to work with into a temp table,
so that the column is at least computed not more than once for each row.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
In other words, store the values.

Jul 23 '05 #7
Obviously the values are stored, but they are used to create the
description so the description itself is never stored as there is no
point to store it when it can chabge all the time either as it grows or
as it is ammended.
Think of it this way, people are made up of characteristics which have
values eg
hair colour - grey, blonde, brunette,
eye colour - green, blue
country of origin - british,america n,
sex - male,female etc
thus, the description of one person is, 'female, blonde, green eyes,
british born'. The description includes the stored values, but is itself
derived, that way, as the subject gains more values to the
characteristics , or is edited, or the rules change (ie we applied a born
label to the country characteristic to make the value read british born
which may change to born in britain ie the value is now prefixed instead
of being suffixed by new text, thus the description is never stored
always computed becaue you do not want to store with a value every
label, just store the label once and apply the current one to the
description as it is derived, obvious really.. And now somebody wants
'born' to be 'brn' or british to be gb etc which is applied after the
description is computed, but the list of abbreviations is prohibitive
and NOT related to anything, just a big list of word replacements.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 23 '05 #8
an*******@devde x.com (an*******@devd ex.com) writes:
And now somebody wants 'born' to be 'brn' or british to be gb etc which
is applied after the description is computed, but the list of
abbreviations is prohibitive and NOT related to anything, just a big
list of word replacements.


Could you stash those abbreviate into a table? And then the user-defined
functions that builds the Description string, could take an argument
on whether to look up that table, and then you would make the abbrivated
string a second computed column?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9

Thanks all for your contributions. I do indeed have the abbreviated
description as a computed column based on the results of the
replacements to the computed descriptions after having looped thru the
abbreviations table looking for matches. As this is the bit that takes
the most ammount of time this is where i can save the most time which I
propose to do by simply calcualting the abbreviated description once and
using the stored value for as many retrievals as I can get away with.
That way all I need to track ('all' he says in an almost casual way) are
any changes that might result in changes to the computed column and then
re-calculate only those, which I can schedule as a job. The only real
time access to users for the abbreviated description (other than
exports and prints) is via a button so this is always calculated in real
time and so will always be accurate (about 1.5 secs per click) and if
the user only reads the descriptions I have nothing to re-calculate. The
only pain with this method are those changes to the abbreviations table
itself, given that we do not know which descriptions are affected by new
or updated abbreviations we need to re-calculate them all which is the
big pain (handled by a simple trigger from the abbreviations table to
set a flag for an overnight run to pick up on) The core problem is
obviously the lack of a relationship between the abbreviations and the
various pieces of the computed descriptions to which they apply.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
7227
by: Paulo Andre Ortega Ribeiro | last post by:
I have a table with fields called fname (First Name) and lname (Last Name). I need the user´s email thai is compose from lname and fname: LOWER(LEFT (fname,1) + lname) Is there any difference between creatig this computed column ia a table or in a view in SQL Server 2000? I can do: 1. CREATE TABLE Users(
2
11290
by: tperovic | last post by:
Using SS2K, I'm getting the following error while bulk inserting: Column 'warranty_expiration_date' cannot be modified because it is a computed column. Here is my bulk insert statement: BULK INSERT dbo.TestData FROM 'TestData.dat' WITH (CHECK_CONSTRAINTS,
2
2005
by: BCR | last post by:
I created a index on a computed column. I did not see any improvement in performance with a join to this column and also my inserts and updates to this table are failing. Any ideas? Chender
0
1687
by: Jim Heavey | last post by:
Hello, I have created a computed column which concatenates a name and date. My problem is that if the 10 is not 10 characters, I get an extra character placed into the computed column. Here is the code that I have for the expersion of the computed column... dtCompetition.Columns.Expression= ("trim (CompetitionName) + ' ' +...
3
1971
by: Raymond Du | last post by:
Hi, Can I have computed columns in a datagrid? If yes, how? TIA
1
1914
by: Dave | last post by:
I am relativly new to visual basic, so this may be a no- brainer. I am attempting to use a computed column in a VB dataset defined as followe: ' 'dcCost ' Me.dcCost.ColumnName = "Cost"
6
2073
by: jim_geissman | last post by:
Can I create an index on a variation of a column that isn't actually in the table? I have a ParcelNumber column, with values like 123 AB-670 12345ABC 000-00-040 12-345-67 AP34567890
2
19371
by: Dot Net Daddy | last post by:
Hello, I want to assign a column a computed value, which is the multiplication of a value from the table within and a value from another table. How can I do that?
7
5775
by: Aamir Mahmood | last post by:
Hi All I have DataTable object. Is there a way that I can know which fields (columns) in the table are computed. Apparantly the DataTable.Columns returns all columns both computed and other. Any help would be appreciated.
0
7918
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7843
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8340
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8220
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5392
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3840
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2353
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1452
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1185
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.