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.
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_RetailDataON 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.ThanksTransact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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) |
|
|
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_RetailDataON 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.CheersMIK |
|
|
|
|
|
|
|