June 5, 2003 at 1:51 am
SQL7
Trying to reduce the size of an index, I want to create an index on only the first 10 positions of a field, without duplicating the data in the original field, i.e. without actually creating an additional field. Any known way to do this? Syntax in topic header is refused, same goes for substring(MyField,1,10).
Any hlp appreciated, MvG.
Mvg, MvG.
Mvg, MvG.
June 5, 2003 at 2:44 am
I don't think you can create an index on part of field.
My only suggestion, without duplicating the first 10 characters, is to split the field into two columns - one containing the first 10 characters which you can index and the second which contains characters 11 onwards.
This might not be practical but without duplicating the data your options are limited.
Jeremy
Edited by - Jeremy Kemp on 06/05/2003 02:45:04 AM
June 5, 2003 at 9:18 am
You could try adding a computed column consisting of the first 10 characters of the index field and indexing on the computed column.
According to BOL, this is allowed as long as certain criteria are met (see Creating Indexes on Computed Columns in BOL)
Tony Bater
Tony
June 6, 2003 at 2:54 am
Nope.
USE PUBS
go
alter table authors add mvgtest as substring(au_lname,1,10)
go
create index mvgIndex on Authors (mvgtest)
go
Server: Msg 1911, Level 16, State 2, Line 1
Column name 'mvgtest' does not exist in the target table.
Mvg, MvG.
Mvg, MvG.
June 6, 2003 at 3:15 am
quote:
Nope.USE PUBS
go
alter table authors add mvgtest as substring(au_lname,1,10)
go
create index mvgIndex on Authors (mvgtest)
go
Server: Msg 1911, Level 16, State 2, Line 1
Column name 'mvgtest' does not exist in the target table.
Mvg, MvG.
Just tried this and it worked with no error messages
My set up is SQL2K Enterprise, SP3.
BOL does list some prerequisites including the following -
"The connection on which the index is created, and all connections attempting INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings.
These options must be set to ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER "
Perhaps your settings did not match.
Tony Bater
Tony
June 6, 2003 at 3:26 am
Hm, well, no....
As a matter of fact settings don't match, but more important seems to be the fact that I'm using SQL7 (as stated in original posting). I'll have a go at this in SQL2K aswell, but only as a matter of pure scientific point of view. I solved the performance issue in an alternative way. Thanks all contributors !
Mvg, MvG.
Mvg, MvG.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply