global code: table

Showing posts with label table_name. Show all posts

Wednesday, March 7, 2012

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));

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));

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...
> > Does SQL Server 2000 support function-based index?
> >
> > Such as:
> >
> > create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
>
>|||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));

Sunday, February 26, 2012

Full-text Search with CONTAINSTABLE is slow for first time

I have a Full-text Search query as mentioned below.
SELECT * FROM CONTAINSTABLE(TABLE_NAME, COL_Name,
N'ISABOUT(FORMSOF(INFLECTIONAL, TEXT_HERE))') ORDER BY Rank DESC
The table, TABLE_NAME contains around 800 rows and the column COL_Name is
data type of nvarchar(255).
The above query taking around 60 seconds when i execute it very first time.
And in next sub-sequent executions it is taking less than 1 second.
If i execute the same query after 30 minutes (without performing any thing),
in the first time it is taking again 60 seconds.
What whould be the problem here?
Thanks,
Naveen
does this apply?
http://support.microsoft.com/kb/915850
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Naveen Kumar" <Naveen Kumar@.discussions.microsoft.com> wrote in message
news:87A1EE89-C7EC-4290-9DBA-F383396AB7A0@.microsoft.com...
>I have a Full-text Search query as mentioned below.
> SELECT * FROM CONTAINSTABLE(TABLE_NAME, COL_Name,
> N'ISABOUT(FORMSOF(INFLECTIONAL, TEXT_HERE))') ORDER BY Rank DESC
> The table, TABLE_NAME contains around 800 rows and the column COL_Name is
> data type of nvarchar(255).
> The above query taking around 60 seconds when i execute it very first
> time.
> And in next sub-sequent executions it is taking less than 1 second.
> If i execute the same query after 30 minutes (without performing any
> thing),
> in the first time it is taking again 60 seconds.
> What whould be the problem here?
> Thanks,
> Naveen
>

Wednesday, March 7, 2012

function based index

function based index

function based index

Sunday, February 26, 2012

Full-text Search with CONTAINSTABLE is slow for first time

global code

Blog Archive

About Me