Which indexing style give more performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Which indexing style give more performance


Hi, I designed a table contains the fields like – Name, FlatNo, City, District, State, Country,…etc. I created the seperate Indexes on the fields District, State, Country. and one compound Index I created is District+State+Country. I want to filter the rcords like 1) The records in a given Disctrict
2) The records in a given State
3) The records in a given Country
1) The records in a given District+State
2) The records in a given State+Country
3) The records in a given District+Country 1) The records in a given District+State+Country
Is the right way I created the index ?, or need any unique-identity column for Indexing ? Please give your suggestions.
If I were you I’ll try any combination with Execution Plan to see what cost is in any case.
Also with Index Tuning Wizard. On the fly, may be you need other non-clustering on District+Country.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
As suggested by Luis check the individual plans on QA.
I suppose the index on District+State+Country is ideal. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
You may want to attend this Free MS MSDN webcast: MSDN Webcast: SQL Server 2000 Performance Tuning – Finding the Right Balance of Indexes – Level 200 http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032252961&Culture=en-US
Sir, Suppose Take the table I previously said as a Master table and I am going to create a new table to keep the details of persons as master table’s sub. I mean that The Master table Primary(Unique) Index will be District+State+Country The sub table will be
Name of person, Flat No, District, State, County. Here, the link I can establish is on District+State+Country. take it as a first case,
In second case, In the Master table I am adding a new field "RecordIDNo" and Make it as Primary Index, and the District+State+Country will be an another unique index. The sub table will be Name of person, Flat No, RecordIDNo. Here, the link I can establish is on RecordIDNo. In these cases, which will give me more performance and reliability for crores of records
]]>