how to create functional index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to create functional index

Author

Topic

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2010-08-07 : 03:55:38

Hi all,
how can i create function based index in T-Sql ( like in oracle).

Pankaj

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-07 : 05:11:11

Could you give more details? I'm not familiar with Oracle, have no idea what a 'function-based index' is.

--
Gail Shaw
SQL Server MVP

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2010-08-07 : 07:49:55

quote:
Originally posted by GilaMonster

Could you give more details? I'm not familiar with Oracle, have no idea what a 'function-based index' is.

--
Gail Shaw
SQL Server MVP

Thanx for reply,
actually in my where clause i m using month(mydate) and year(mydate) function.
on a mydate column but due to this my query runs slowly.
now i want to create 2 indexes which create on mydate column as

create index indx_mydatemnth on mytable(month(mydate))
create index indx_mydateyr on mytable(year(mydate))

which is normally supported in oracle.
i dont have much knowledge in sql server.

could you please help me.

Pankaj

Kristen
Test

22859 Posts

Posted - 2010-08-07 : 09:28:31

If you are querying a date range such as:

WHERE MyDate BETWEEN @MyStartDate AND @MyEndDate

then SQL Server will use an index on MyDate column very effectively

If you are looking for a specific month, such as:

WHERE MONTH(MyDate) = @MyMonth

then SQL will be slow (this is generally true of using a FUNCTION on a column in this manner).

So it depends a bit on the type of query you want to optimise. What sorts of queries on Year and Month do you need to optimise? (Some examples would help give you the best advice)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-07 : 11:13:54

Best option - change your queries as Kristen showed.

If you absolutely must imitate Oracle (which I don't recommend here), create a computed column in the table with the function you want. Persist that column then index it. By far not the best solution in this case, the best solution here is to change your queries so that they use BETWEEN and specify the start and end of the month/year

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources