Solved

Using indexes

Posted on 2000-03-05
6
207 Views
Last Modified: 2008-03-03
I have a table with an index whose name is _WA_Sys_MVndNbr_12A9974E. When I try to drop the index, I get the message that the index does not exist in system catalog. However, the index does exist in sysindexes.

Failing to drop the first index, I created a second index on the same column called Test_MVndNbr.

My questions are:
1)How do I drop the index _WA_Sys_MVndNbr_12A9974E?

2)How do I use my second index Test_MVndNbr? If I write a query which references the column on which Test_MVndNbr was created, is Test_MVndNbr used automatically by SQL?

Thanks.

Musleh
0
Comment
Question by:mfarid1
  • 3
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
Gustavo Perez Buenrostro earned 50 total points
ID: 2586446
mfarid1,
SQL Server automatically creates indexes to optimise queries. These indexes can be identified checking the internal system-status information contained in status column of sysindexes table (8388608 decimal value).
If you want these indexes not to be created set 'auto created statistics' database option to false.
Remember database options can be set by using the sp_dboption system stored procedure.

To retrieve all auto create indexes:

select cast(object_name(id) as varchar) as 'table'
      ,status
      ,name
 from sysindexes
where status&8388608<>0


This sample shows the process:
(If 'auto created statistics' database option has been set to true)

1.- Create a sample table and populate it with data.

create table YourTable (Column1 int,Column2 int)
insert into YourTable values (1,4)
insert into YourTable values (1,4)
insert into YourTable values (2,5)
insert into YourTable values (2,5)
insert into YourTable values (2,5)
insert into YourTable values (2,5)

2. - Check if there are auto-create indexes.

select name
  from sysindexes
 where id=object_id('YourTable')
   and status&8388608<>0

Here is the result set (no indexes):

name
------------------------


3. - Run a query.

  select *
    from YourTable
group by Column1,Column2
order by Column1,Column2


4. - Check if there are auto-create indexes.

select name
  from sysindexes
 where id=object_id('YourTable')
   and status&8388608<>0


Here is the result set (indexes created):

name
------------------------
_WA_Sys_Column1_1F63A897
_WA_Sys_Column2_1F63A897
0
 

Author Comment

by:mfarid1
ID: 2587486
gpbuenrostro, thanks for the answer to the first part of my question. How about an answer to the second part?

2)How do I use my second index Test_MVndNbr? If I write a query which references the column on which Test_MVndNbr was created, is Test_MVndNbr used automatically by SQL?

What I mean to say is, do I have to say use this new index Test_MVndNbr before I execute a query? I don't see any performance gain after creating the new index.
0
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2588841
By default, SQL Server chooses which index to use.

Query optimizer (the component responsible for generating the optimum execution plan for a query) can use, if your query references an indexed column, the index related to it. (Remember Query optimizer chooses the most effective index in the majority of cases).

Note that if 'INDEX' table hint is specified, it forces SQL Server to use the index specified.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:mfarid1
ID: 2628294
gpbuenrostro, thanks for your answer. Can you explain what you meant by,

"Note that if 'INDEX' table hint is specified, it forces SQL Server to use the index specified."
0
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2630271
You can instruct SQL Server to use a specific index. This can be done using‘table hints’ facility. For more information, see “Hints” topic in BOL.
0
 
LVL 2

Expert Comment

by:cojdev
ID: 8120614
to remove the index use
DROP STATISTICS [TABLE NAME].[_WA_Sys_MVndNbr_12A9974E]
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now