Index on Portion of a Field

Is it possible to index a portion of a field. I need to search a large table and look for the left 4 characters in two fields.

1) Does a 'normal' index on the entire fields get used in a search like this?
2) Can you build an index on just the left 4?
3) I could use an indexed view, but the table wasn't built with the right settings and I don't want to rebuild and repopulate the table if I don't have to

Thanks for the insight.

PeteNo, you cannot index a portion of a field.

If you did a search like " WHERE field LIKE 'abcd%' and field2 LIKE 'efgh%' " then I believe you will find that SQL Server will use the index (use Query Analyzer to make sure). SQL Server changes these kinds queries into SEARCHABLE ARGUMENTS (SARGS) that can use indexes (note that this is NOT true of things like " LIKE '%abcd%' )|||If this is something that you really need (indexing on only a prtion of a filed), you could create a computed field that just contains the portion you want to index on.

Index on Computed column or Indexed View

I have a large nvarchar(2000) that need to be queried on often based on a
subset of the data (first 50 characters). The application creating and using
the data cannot be modified to capture a short and long column... I was
wondering if creating a computed column with the formula being
Left(longcolumn, 50) and creating an index based on this column could be a
good option? Or would it be preferable to create an indexed view?
Any other suggestion are welcomed
Thank you for your helpI would go with a computed column to start with.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in message
news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> I have a large nvarchar(2000) that need to be queried on often based on a
> subset of the data (first 50 characters). The application creating and
using
> the data cannot be modified to capture a short and long column... I was
> wondering if creating a computed column with the formula being
> Left(longcolumn, 50) and creating an index based on this column could be a
> good option? Or would it be preferable to create an indexed view?
> Any other suggestion are welcomed
> Thank you for your help
>|||Thank you for help. It confirms my tests that I performed last night.
"Narayana Vyas Kondreddi" wrote:

> I would go with a computed column to start with.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in messag
e
> news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> using
>
>