I have a field that is varchar(15),

[tkr13]

I have a field that is varchar(15), that contains last names. There is a query that will be searching on last names, so I am putting an index on that field. In order to save some space, I was hoping to only index the first 7 or 8 characters of the field. Is that possible in SQL Server 7 or SQL Server 2000? I am hoping to avoid doing it a a full text search.

Thanks

 

[tlbroadbent]

I'm not aware of any indexing on partial columns in SQL Server. However, I've not used Full-Test search so I don't know how it handles such things.

Do you have a clustered index on the table? If not, create a clustered index on LastName. Clustered indexes do not require additional space because the data is physically sorted in the order of the index and the index is on the data page. In many instances clustered indexes are also faster than non-clustered indexes. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tkr13]

Thanks for the reply, there is a clustered index, on a different column. The table is hit by a couple of ASP pages, that search on a couple of different columns, but the one column that is always in any of the Where clauses is the one we have a clustered index on. So I'm hoping to create a nonclustered on last name. I know sql server 2000 is capable of indexes on views, so may create a view of the table that has first 7 or 8 characters of that field, and index that (if and when we upgrade to 2000), in meantime I guess just index that column.

 

[tlbroadbent]

You may already be aware of this. Indexed views are only available with SQL 2000 Enterprise Edition. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.