1. Yes it can be. Ascending is the default and is usually what's used. If your query requires the data in descending order, descending order of the index might be a little faster. I'd probably still use ascending though. 2. You can drop/create it.
I have table Preety big close to 25m Records. I don't want to do table partion however i would like to do Index partition. I have data in this table from last 8 years. User ver rear chance go and check the data last 8 years so i want to filter index.
I have 1 clustered and 6 non cluster index on this table and i want to filter my index by WHERE Year(Servicedate) = '2005'
Should i use filtered on all existing Indexes or one is enough?
I agree its not a big table. For feature reference could you please answer my question on last post?
One other Guidence. I Create a file on SQL Profiler that i am going to use on "Database Engine Tuning Advisor" after some analyze they create a report with some recommendation and as you know most of recommendations are create Statistics or Create Non Clustered Index. My question is for you what do you think its a good idea if i follow all recommendation...
I went to front end and do some random test. In the test they ran lot of store procedure and function and other thing.Behind the seen i am saving all steps in SQL Server Profiler, once i done after 5m i stop profile and save file. Then i use this "Tuning" file in Database Engine Tuning Advisor. Thats all information i have. If you wanna know each step and store procedure. I hope you understand its impossible to provide you all that information.
If someone go through this process he should know what i am taking about...
Sodeep if i understand right, In simple wording "IF Database Engine Tuning Adviser" are Recommending Missing Indexes and statistics that will improve 76% Performance don't close your eyes and create those Missing Indexes and statistics" am i right?