function based index - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

function based index

Author

Topic

jatashankar patel
Starting Member

3 Posts

Posted - 2011-12-22 : 01:18:09

Hi,

can you tell us how we can create function based index in ms sqlserver 2008. suppose we have a query like this --

*/
select count(opportunity_code) as opc, month(created) as mon from tbl_oppourtunities where abs(DATEDIFF(date(now()),created)) <= 365 and year(created)=year(now())
/*

so please create an index based on this query.

please reply asap.

Thanks

Best regard

Jatashankar patel

jatashankar patel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 01:55:04

you need to create a view for this query and make it to indexed known as indexed views if you want to prestore the aggregates in index.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jatashankar patel
Starting Member

3 Posts

Posted - 2011-12-22 : 02:24:44

Hi,

Thanks for your quick reply.

But i want know more...

can we create indexes without creating a view on the table in below query..?

*/
select count(opportunity_code) as opc, month(created) as mon from tbl_oppourtunities where abs(DATEDIFF(date(now()),created)) <= 365 and year(created)=year(now())

/*

1. if yes then what will be the index query for above query.

2. if no then why we can not create index without creating any view on the table. please give me at least one reason.

Thanks

Best regard

Jatashankar patel

quote:
Originally posted by visakh16

you need to create a view for this query and make it to indexed known as indexed views if you want to prestore the aggregates in index.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jatashankar patel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 04:03:47

you can create index on columns of table. but if query has to use the index then it should be be possible to get information from index table. Once you apply functions over indexed columna it will become non sargable and hence index wont be used even if its present in the column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jatashankar patel
Starting Member

3 Posts

Posted - 2011-12-22 : 04:59:31

Hi, thanks for quick reply

*/
select count(opportunity_code) as opc, month(created) as mon from tbl_oppourtunities where abs(DATEDIFF(date(now()),created)) <= 365 and year(created)=year(now())

/*

the above query comes from a table and that table only one primary key index on column 'opportunity_code' and no other indexes on the table.

So please reply me with t-sql format asap.

Thanks

Best regard

Jatashankar patel

quote:
Originally posted by visakh16

you can create index on columns of table. but if query has to use the index then it should be be possible to get information from index table. Once you apply functions over indexed columna it will become non sargable and hence index wont be used even if its present in the column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jatashankar patel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 06:21:39

i think you missed my point.even if you add an index on column the way your current query is written it wont use the index as you've used functions over column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-22 : 07:30:43

If the where clause you posted is really what you want have (and is not just a made up example), it looks like you are trying to filter on all the rows where "created" is in the current year. If that is so, you can avoid the functions on the column in the where clause by doing the following:


where 
create>=dateadd(year,datediff(year,0,getdate()),0) and
create<dateadd(year,datediff(year,0,getdate())+1,0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources