Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Index on part of a field

Posted on 2007-04-11
9
Medium Priority
?
945 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 11

Expert Comment

by:dready
ID: 18891974
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
ID: 18891988
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
ID: 18892225
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18892378
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
 
LVL 11

Expert Comment

by:dready
ID: 18892481
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 70

Expert Comment

by:Scott Pletcher
ID: 18893169
> 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
ID: 18893471
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
ID: 21890443
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

610 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