Solved

Using indexes

Posted on 2000-03-05
6
210 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
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.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Slabs 9 38
recover sqlserver db 8 56
ms sql last 8 weeks as columns 5 28
Update SQL to SP1 on SCCM server 7 16
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

895 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

12 Experts available now in Live!

Get 1:1 Help Now