• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

SQL Index on part of a field

I have a character field in my database that contains 2 separate pieces of information, work order and job. Both fields are character and are separated by a - (dash) in the field.  I created a view that has taken the field and broken it into it's components for easy reporting, but am concerned that I do not have an index over the Work Order field.  Is there a way to create an index on a subset of data in a field?  
Also, the work order field does vary in size from 5 to 7 characters.  I would like to utilize the following code to break the field into it's subcomponents...

case when charindex('-',b.paprojnumber)>1 then left(b.paprojnumber,charindex('-',b.paprojnumber)-1)
else b.paprojnumber end
How can I do this?
0
jvwiv
Asked:
jvwiv
1 Solution
 
dreadyCommented:
My first reaction:
split the data into two columns.  It will keep giving you trouble, one column should hold one peace of information.

In s
0
 
dreadyCommented:
ooops, not finished:

in sql server there is something like a indexed view:
http://www.sqlteam.com/item.asp?ItemID=1015

Maybe that could help you. But, View definition cannot contain the following, so i dont know if any of that applies to your view.

TOP

Text, ntext or image columns

DISTINCT

MIN, MAX, COUNT, STDEV, VARIANCE, AVG

SUM on a nullable expression

A derived table

Rowset function

Another view

UNION

Subqueries, outer joins, self joins

Full-text predicates like CONTAIN or FREETEXT

COMPUTE or COMPUTE BY

Cannot include order by in view definition
0
 
jvwivAuthor Commented:
Dready, Thanks for the response.  A few thoughts.  I am not running SQL2000 Enterprise, so I believe from the attachment that I can't utilize the indexed view, right?  I do plan on moving to SQL2005 in the near future, so I may have to wait on that one.
Splitting the field would be awesome except that it would require a ton of rewrites.  This field is used by Microsoft in their Great Plains product.  Changing it would cause them to have to change their app and I think we all know what their response will be.
I am not real strong with SQL keywords, but when I look at the charindex and the left command, I do not see them in the list of no-no's.  Am I missing something?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Christopher KileCommented:
By definition, an index is composed of one or more columns of a table or view.  If the view contains a column computed as a SUBSTRING of a column from one of its base tables, you can index on that.  Also, you can create computed columns on the table that contain the appropriate substrings of the WorkOrder-Job field then use those computed columns in indices.
0
 
dreadyCommented:
well, that sounds like you could use an indexed view, then.
Which version are you running? I am not sure wether all version support it or not, just try...
0
 
Scott PletcherSenior DBACommented:
> This field is used by Microsoft in their Great Plains product.  Changing it would cause them to have to change their app <

Hmm, maybe.  Perhaps you could split the "paprojnumber" column into two new, separate columns -- say workOrder and job -- but add the original column as a computed column that matches the format their software expects.  I haven't gone thru it thoroughly, but the only part of the existing app that should have to change then is inserting of rows.
0
 
jvwivAuthor Commented:
So it sounds like I need to read up on Indexing the view and give that a shot.  I'll let y'all know if that works.  As noted, I'm moving to 2k5 soon, so perhaps waiting will be acceptable.
0
 
Computer101Commented:
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now