function index

I have implemented function based indexes on tables in Oracle database. I want to implement same in SQL Server. Is there something similar to function index in SQL Server also? If yes, please explain as how I can do this.
Thank,
dcExplain more clearly what you want to do.|||You have to create a computed column with the function you want indexed, then index the computed column. Then you have to change all of your applications to query the new column. Simple, eh?

In short, Function Based Indexes don't really exist in SQL Server, but they are pretty nice things to have.

create index ind on table (upper(name))|||Function based indicies (where the indexed value is the result of one or more function calls) were the most dearly missed feature of Clipper when I converted to SQL Server. They are lovely things when you don't have to deal with most of the complexities that a real relational database engine is intended to solve!

Unfortunately, they don't scale, introduce all kinds of "functional dependancies", lead to really interesting collation questions, etc. A Function Based Index is certainly convenient in many ways, but it is a scaling/supportability nightmare in search of its next victim!

-PatP

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
Tom
It would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement
.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
--
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Full-Text Results to MS Access

Greetings!
I have a requirement to set up several full-text indexes on our SQL Server
2000 database. My problem/question is how to get the results to show up in a
form or datasheet in our MS Access XP front end? I'm planning to be able to
allow the user to enter search criteria via an Access form, and get the
results back by populating a subform with the returned data. But since I'm
fairly new to using SQL Server, I'm not sure how to set up the recordset in a
form (or report) in Access. Also, I was thinking that the "best" way to deal
with this is to pass the user's search criteria to a stored procedure in SQL
Server. Is this the best way to do it?
Thanks very much for your help!
PaulJS
PaulJS,
The problem should be no different than getting any normal (non-FTS) SQL
Server backend database ad hoc or stored procedure query results returned to
your MS Access XP front end. For example, using the pubs database and the
authors table, you can execute:
select * from authors where au_lname = 'white'
Once, you've FT-enable the authors table in the pubs database, and run a
Full Population via the FT Indexing Wizard, you can issue the following
query:
select * from authors where contains(au_lname, 'white')
As for the best way to deal with the users search criteria (and to avoid the
infamous "ignore word" error), you may want to parse out the noise words
first, and/or enclose the search criteria within the appropriate quotes. For
more details on this, see KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at
http://support.microsoft.com//defaul...;EN-US;246800. You can
find more SQL Server 2000 Full-Text Search Resources and Links at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"PaulJS" <PaulJS@.discussions.microsoft.com> wrote in message
news:C42FDBE2-6120-44FB-8553-CE36A977315C@.microsoft.com...
> Greetings!
> I have a requirement to set up several full-text indexes on our SQL Server
> 2000 database. My problem/question is how to get the results to show up
in a
> form or datasheet in our MS Access XP front end? I'm planning to be able
to
> allow the user to enter search criteria via an Access form, and get the
> results back by populating a subform with the returned data. But since
I'm
> fairly new to using SQL Server, I'm not sure how to set up the recordset
in a
> form (or report) in Access. Also, I was thinking that the "best" way to
deal
> with this is to pass the user's search criteria to a stored procedure in
SQL
> Server. Is this the best way to do it?
> Thanks very much for your help!
> --
> PaulJS
|||John:
Thanks for the info! After reading your response, I think my problem is
more of an elemental one: Being new to SQL Server and ADO, how can I bind the
results of my full-text query to, say, an Access form or datasheet (datagrid)
with the results? (My database was started in pre-ADO days so the Access
front end uses DAO on recordset operations.) Although I'm using ADO more and
more, I use it mostly to perform some operation on data and return a result
like a true/false flag, a value, etc., but if the result is a recordset, I
don't know how to bind (display) those records to the form. Access makes it
easy to use an Access query to be bound to the form as the form's record
source. My problem (or weakness), is not knowing how to get recordset data
results back from a stored procedure and display those results in a form. I
want to be able to let users enter search criteria, then get a list of
results, and let the user select one of those records to do some other
operation. For instance, a user wants to search a table named "tblProject"
based on some full-text search criteria. The criteria is sent to a stored
procedure, and then the Project records matching the criteria are displayed
in an Access datagrid or form; the user selects one of the records, then does
some operation based on the selected Project record.
Did that make sense? If not, let me know.
Thanks again!
"John Kane" wrote:

> PaulJS,
> The problem should be no different than getting any normal (non-FTS) SQL
> Server backend database ad hoc or stored procedure query results returned to
> your MS Access XP front end. For example, using the pubs database and the
> authors table, you can execute:
> select * from authors where au_lname = 'white'
> Once, you've FT-enable the authors table in the pubs database, and run a
> Full Population via the FT Indexing Wizard, you can issue the following
> query:
> select * from authors where contains(au_lname, 'white')
> As for the best way to deal with the users search criteria (and to avoid the
> infamous "ignore word" error), you may want to parse out the noise words
> first, and/or enclose the search criteria within the appropriate quotes. For
> more details on this, see KB article 246800 (Q246800) "INF: Correctly
> Parsing Quotation Marks in FTS Queries" at
> http://support.microsoft.com//defaul...;EN-US;246800. You can
> find more SQL Server 2000 Full-Text Search Resources and Links at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "PaulJS" <PaulJS@.discussions.microsoft.com> wrote in message
> news:C42FDBE2-6120-44FB-8553-CE36A977315C@.microsoft.com...
> in a
> to
> I'm
> in a
> deal
> SQL
>
>