| Author |
Topic |
|
takkone
Starting Member
8 Posts |
 Posted - 2011-10-11 : 15:35:47
|
| I am developing an application in VB.NET using SQL Server 2008 back end. The application stores pricing and description data for items the business sells. There are currently over 1.5 million items in a table in the database, which will continue to grow but it took close to 10 years to accumulate to where it is today. We imported all of this historical data.The challenge is this. Each item has a ManufacturerPart# and a VendorPart#. Many part numbers contain special characters like hyphens, underscores, and blank space. We need to keep those part numbers as-is, because those are proper part numbers. But this makes it difficult to find some parts when searching because users don't always know where there are hyphens and underscores, etc...So I'd like to put an additional two fields in my table, one to mirror the MFR Part # and one to mirror the Vendor Part #, but each having those special characters stripped out. Then I can use those mirrored fields when searching, but use the true fields when displaying data.I'm thinking rather than handling all of this in my application logic, I can use a trigger on each table that will do the work of updating the mirror fields whenever the table is written to.Is this a good approach? I should add that any kind of mass-update to this table would be VERY rare. Items are generally updated and added one at a time. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-11 : 21:47:31
|
you can use a compute column for thisalter table <table name> add ManufacturerPartStripped as replace(replace(ManufacturerPart, '-', ''), ' ', '')
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 01:23:38
|
if logic to strip off special characters is too complex you can even consider putting them in a scalar udf and creating computed colmn based on thatalter table <table name> add ManufacturerPartStripped as dbo.ScalarUDFName(yourcol) also make it persisted if you want to avoid computing it on the fly while querying especially if frequency of hitting column is large------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
takkone
Starting Member
8 Posts |
Posted - 2011-10-12 : 09:54:24
|
| Computed column sounds like a great idea, but I just realized a problem with that. You see this application is not 100% custom, it is sort of an add-on to an off the shelf application and I'm adding custom tables to the database. But both apps share some of the out-of-the-box tables and I cannot alter the structure of those tables, or it might cause a problem to the main app. But the native tables do have about a dozen varchar fields which are left names as: custom01, custom02, etc... and I can fill those with any data I want, without worry of breaking something.So it looks like I might be back to a trigger. Thoughts on this? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-12 : 10:04:39
|
quote:
I cannot alter the structure of those tables
your original method is to add columns + trigger. Technically adding a column means altering the structure of the table.The suggested method only add computed column and the advantage is there isn't any trigger involved. And you are not altering the data of the table. Just the value of the computed column is stripped of those unwanted chars.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
takkone
Starting Member
8 Posts |
Posted - 2011-10-12 : 10:31:57
|
| Yes, but I think I corrected my initial description. Rather than creating NEW columns, I'll need to fill two existing fields in the table with the alternate part numbers. Since those two fields are not currently utilized, it is safe for me to use them.As a side question... does adding a persistent calculated column change the table structure? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-12 : 10:36:51
|
yes
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 11:57:15
|
| In both cases adding computed column will change table metadata. Only difference is persisted make it physically store column stored which will avoid computing the column each time------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Should I use a trigger for this?