SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Filtered Index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

suriyarupa
Starting Member

19 Posts

Posted - 05/13/2013 :  09:11:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/13/2013 :  09:57:55  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

India
52325 Posts

Posted - 05/14/2013 :  00:36:10  Show Profile  Reply with Quote
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 - 05/14/2013 :  08:42:31  Show Profile  Reply with Quote
My Query is

Select * from tblretaildata where timeperiod = 'Apr'

It has 280000 records.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 05/14/2013 :  11:45:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/14/2013 :  13:08:23  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 05/14/2013 13:09:29
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000