February 12, 2010 at 10:30 am
Wht will effect if column order change while creating indexes?
Consider below 2 statements where Date and Name changed in creating indexes.
1. Is there any effect if order is changed?
2. on which factors order of column decided?
CREATE NONCLUSTERED INDEX [TestIndex] ON Table1
(
[Date] ASC,
[Name] ASC,
[Material] ASC
) ON [PRIMARY]
GO
or
CREATE NONCLUSTERED INDEX [TestIndex] ON Table1
(
[Name] ASC,
[Date] ASC,
[Material] ASC
) ON [PRIMARY]
GO
February 12, 2010 at 10:40 am
The columns you use for indexes usually depend on your queries. The search arguments in the WHERE clause are were indexes come into play.
IF you create the first index and query by name, the index can't be used. So for this query:
select name
from Table1
where name = 'Sue'
won't use the index. It would have to use a scan. This query, would work:
select name
from Table1
where name = 'Sue'
and date > '1/1/2010'
If you often query by name and date separately, you would need both indexes.
Creating indexes is a balancing act. You don't want to create an index on every column since you have ot update all those indexes every time there's an insert. And they use space. You pick the most often used 5-10 columns and index them. If you often query in compound ways (such as NAME AND DATE), then you use indexes like you've listed.
February 12, 2010 at 12:23 pm
1) Yes
2) The indexes that run against the table
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply