function based index

Does SQL Server 2000 support function-based index?
Such as:
create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
Not directly.
But if your function is deterministic, you can create a computed column:
ALTER TABLE YourTable
ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
... and then you can index it:
CREATE INDEX ComputedColumnIndex
ON YourTable(YourComputedColumn)
This won't quite be the same as a function-based index, as the index won't
be used for a query like:
SELECT *
FROM YourTable
WHERE FLOOR(DECIMAL_READING) = 10
... but you don't need to use the function at all, you can just do:
SELECT *
FROM YourTable
WHERE YourComputedColumn = 10
... And that _will_ use the index.
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
|||Bevo wrote:
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
You can create an index on a computed column, but I'm not sure you can
specify functions in the create index statement.
See the section on "Considerations when indexing computed columns and
views" in the "CREATE INDEX" page of BOL.
So you could do something like this:
create table #test (col1 char(10), col2 as left(col1, 4))
create index test2 on #test(col2)
David G.
|||Thanks for the post, but, unfortunately I cannot modify the table structure.
"Adam Machanic" wrote:

> Not directly.
> But if your function is deterministic, you can create a computed column:
> ALTER TABLE YourTable
> ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
> ... and then you can index it:
> CREATE INDEX ComputedColumnIndex
> ON YourTable(YourComputedColumn)
> This won't quite be the same as a function-based index, as the index won't
> be used for a query like:
> SELECT *
> FROM YourTable
> WHERE FLOOR(DECIMAL_READING) = 10
> ... but you don't need to use the function at all, you can just do:
> SELECT *
> FROM YourTable
> WHERE YourComputedColumn = 10
> ... And that _will_ use the index.
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
>
>
|||You might also create an indexed view and do the same thing... Search for
indexed views in books online.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));