Solved

SQL Index on part of a field

Posted on 2007-04-11
9
927 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

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.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

821 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