Solved

SQL Index on part of a field

Posted on 2007-04-11
9
919 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 69

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

808 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