Solved

Using indexes

Posted on 2000-03-05
6
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

751 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