Solved

Using indexes

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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…
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 different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

832 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