Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using indexes

Posted on 2000-03-05
6
Medium Priority
?
230 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 200 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

704 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