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
 General SQL Server Forums
 New to SQL Server Programming
 Filtered Index

Author  Topic 

suriyarupa
Starting Member

19 Posts

Posted - 2013-05-13 : 09:11:37
I created Filtered index. But there is no difference between before creating and after. Why? Here is the code.

CREATE INDEX FIX_RetailData
ON tblretaildata(Region,Trademark,Package,
[FC IC Type],BEV_PROD_NM,datayear)
WHERE timeperiod = 'Apr'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-13 : 09:57:55
what's your query that uses the index....

Making an index will do nothing unless queries are able to use them.

Can you post some example queries running against this table.

Thanks

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 00:36:10
First see if your query is accesing any data which satisfies specified filter condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

suriyarupa
Starting Member

19 Posts

Posted - 2013-05-14 : 08:42:31
My Query is

Select * from tblretaildata where timeperiod = 'Apr'

It has 280000 records.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-14 : 11:45:39
Have you looked at the execution plan to see which index is being used? Also, while the filter is indeed being specified in your where clause, the actual index columns are not. You are choosing "select *" instead of an explicit column list. Unless your index is a covering index, it is unlikely that the optimal plan would use your index since it would then have to do a bookmark lookup to get the other columns.

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-14 : 13:08:23
quote:
Originally posted by suriyarupa

I created Filtered index. But there is no difference between before creating and after. Why? Here is the code.

CREATE INDEX FIX_RetailData
ON tblretaildata(Region,Trademark,Package,
[FC IC Type],BEV_PROD_NM,datayear)
WHERE timeperiod = 'Apr'





Difference in terms of what? If its in terms of performance for the mentioned query, then I think you should have an index on "timeperiod" field - you're querying data based on timeperiod field. Filter index is a type in which an index is build over a specific set of data instead of all data in the table. English translation of the one you created would be as "create/maintain indexing on these six fields, but, for the records where timeperiod='apr'". SQL server uses the fields/columns on which the index is build, in order to determine whether to use it or not.

Cheers
MIK
Go to Top of Page
   

- Advertisement -