Solved

SQL Index on part of a field

Posted on 2007-04-11
9
898 Views
Last Modified: 2008-06-28
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
Comment
Question by:jvwiv
9 Comments
 
LVL 11

Expert Comment

by:dready
Comment Utility
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
 
LVL 11

Expert Comment

by:dready
Comment Utility
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
 

Author Comment

by:jvwiv
Comment Utility
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
 
LVL 23

Expert Comment

by:Christopher Kile
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 11

Expert Comment

by:dready
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
> 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
 

Author Comment

by:jvwiv
Comment Utility
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now