September 1, 2009 at 8:17 am
I have same index on multiple tables in a database, is that OK? how does it choose index when seraching.
CREATE CLUSTERED INDEX [index_Empno] ON [dbo].[Employee]
(
[Empno_rev] ASC,
[Effectivedate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
September 1, 2009 at 8:57 am
If you have the same index structure, but on seperate tables, that is fine....
As for choosing an index, that's where your distribution statistics come into play. The query optimiser will use the statistics to work out which indexes will be usefull in the query plan.
September 3, 2009 at 7:50 am
Each table referenced by the query will be addressed by the optimizer and the best index that the optimzer can find will be used. As noted above, this depends on the distribution statistics of the indexes available on the table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2009 at 8:44 am
Also, indexes are on tables, not generic in the database. If I have an index on the CustomerID in the Orders table and an index on the CustomerID in the Customers table, they are separate indexes. I have duplicated the actual data of the index (minor differences between the meta data), but there's no way around that.
The optimizer chooses the index for a table, if the table is in the query and it is deemed to be a good idea for performance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply