Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cardgunner
326 Posts |
 Posted - 2009-07-07 : 11:27:03
|
| If my field,aa.modl, is 'anymodel mfg' and I need to make it 'anymodel' how is the best way to do it.I am using rtrim(substring(aa.modl,1,len(aa.modl)-3)) and it works but is there anything less wordy or faster.The ending is always 3 letters. The length differs from 43 and 42. Table aa has 43201 records. I need to use field in almost every query I write and use. So it would be great to know that I'm using the best function or set of functions.CardGunner |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-07 : 12:40:45
|
quote:
Originally posted by cardgunner
I need to use field in almost every query I write and use. So it would be great to know that I'm using the best function or set of functions.
What you need is to break out the data you need as a persisted computed column.That way you can index the column and your query performance will faster. Much faster.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
cardgunner
326 Posts |
Posted - 2009-07-07 : 15:33:02
|
| Thanks for the suggestions. I have never heard of a computed column before and thus have never created one. From what I found online, it seems fairly straightforward. Because i would have to modify the table the chances of this getting done would be slim. Thanks again. This computed column may come in handy.Are there restrictions to what kind of formulas you can use? Meaning can you use a case statement in there? I read you can not use a number * datepart(dd,date) as a formula.CardGunner |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-07 : 15:36:49
|
Yes you can. The result must be deterministic however.It means the result must be identitical each time same input parameters are used.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
Best use of functions