Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)
You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>
|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column. SQL Server can
traverse an index in both directions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>
|||Bryan and Tibor,
Thanks for the idea. Bryan is right that I can't have same name for two
different index.
Well I am not understanding when, if there is a need, to create multiple
types of Index for one or muiltiple columns.
Another word, why would SQLsrvr2k allow muliple index in one column?
Ideas? Thanks again.
|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>
|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:

>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks, Hugo. Your answer make sense.
light_wt.